On 09/12/13 23:43, Lee Hachadoorian wrote:

I need to update multiple columns (~250) from one table to another based on a two-column key. In Postgres I would write this using the FROM clause, but since MonetDB doesn't support the FROM clause, I've successfully updated one column with:

UPDATE census.acs2010_5yr SET b07401001 = ( SELECT CAST(b07401001 AS int) FROM acs2010_5yr.tmp_seq0001 WHERE acs2010_5yr.stusab = tmp_seq0001.stusab AND acs2010_5yr.logrecno = tmp_seq0001.logrecno );

My question is whether there is any benefit to writing a multiple column update into a single UPDATE statement:

UPDATE target_table SET column1 = (SELECT column1 FROM source_table WHERE target_table.key = source_table.key), column2 = (SELECT column2 FROM source_table WHERE target_table.key = source_table.key), ..., columnN = (SELECT columnN FROM source_table WHERE target_table.key = source_table.key);

The common term optimizer will ensure that the WHERE part is only executed once. See the result of EXPLAIN for this query.

On Postgres I believe the multi-column update using a table join would only have to do the sequential scan of each table once, but with the necessity of sub-selects in MonetDB, what is the backend doing? Is it already doing an index scan once for each row in the single column case, then doing it once for each row * column in the multi-column case?

Best, --Lee

-- Lee Hachadoorian Asst Professor of Geography, Dartmouth College http://freecity.commons.gc.cuny.edu/

_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list