StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>While there may be ways to update a table with another table through fancy subselects, I believe the best approach is to write a program to do this using the SQL API (whether it is DBI with the DBD::DB2 driver for perl, JDBC for Java, or the C libraries, etc) to perform the SELECT, FETCH each result row from the RESULTSET with a cursor, and do an update.</p> <p>PSEUDOCODE (I don't know what language you are familiar with):</p> <pre><code>dbHandle = sqllib->open_connection(database, user, password) select_statement = dbHandle->prepare('SELECT id FROM idstoupdate') update_statement = dbHandle->prepare('UPDATE clients SET col1=?, col2=? WHERE ID=?') resultset = statement->execute(select_statement) foreach (row in resultset) { id = row.getColumn('id') update_statement->execute('1','2',id) } dbHandle->disconnect(); </code></pre> <p>You would want to add error checking. If you want either all the updates to apply, or none, then you have to look into beginning a transaction and commiting the entire transaction if you have no errors. There is a wealth of material on how to do all the above in the <a href="http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=%2Fcom.ibm.db2.udb.apdv.gs.doc%2Fdoc%2Fc0023431.htm" rel="nofollow">DB2 Infocenter</a>.</p> <p><strong>Note:</strong> If your source data for the <code>idstoupdate</code> is a file, then you could skip the select statement and the work you do to load the <code>idstoupdate</code> table, and just read from the file and update the database. This would be the most efficient way to handle updates to a table.</p> <p>If you simply must update a table from another table with pure SQL, then the most common examples are in this format:</p> <pre><code> UPDATE table1 t1 SET (t1.field1, t1.field2) = ( SELECT t2.field1, t2.field2 FROM table2 t2 WHERE t1.joinfield = t2.joinfield AND t2.criteriafield = 'qualifier' ) WHERE EXISTS ( SELECT 1 FROM table2 WHERE t1.joinfield = table2.joinfield AND t2.criteriafield = 'qualifier' ) </code></pre> <p>which eliminates the <code>IN</code> predicate, but is probably not much more memory or logspace efficient, and because it is less straightforward than a select, loop, fetch, update you need to be sure you have all the criteria correct. Your case is a bit simpler-- I think this would work, but I'd need a db2 instance to try it against: </p> <pre><code> UPDATE clients t1 SET t1.col1 = '1', col2 = 'y' WHERE EXISTS ( SELECT 1 FROM idstoupdate t2 WHERE t1.id = t2.id ) </code></pre> <p><strong>Edit:</strong> I am actually surprised that the query you gave did not work from SQL squirrel as it is a legitimate query. It may even perform similar to the example I gave, as DB2 is very good at optimizing SQL/determining the best access path. </p> <p>In my answer, I was trying to show the most memory efficient way to update a table, as well as the general pattern for updating rows in one table from another table using pure SQL (which would cover cases where one table contains more than just the rows you want to update). </p> <p>Additionally, I am suspicious of IN predicates that contain more than 20 or so values, even if modern database engines handle them with ease. </p> <p>However, the best way to examine if the database engine is handling your query efficiently and/or compare two SQL queries is to use the <a href="http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fcom.ibm.db2.doc.sqlref%2Frexpl.htm" rel="nofollow">SQL explain</a> commands.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload