Bugs item #2254506, was opened at 2008-11-10 10:30 Message generated for change (Comment added) made by sjoerd You can respond by visiting: https://sourceforge.net/tracker/?func=detail&atid=482468&aid=2254506&group_id=56967
Please note that this message will contain a full copy of the comment thread, including the initial issue submission, for this request, not just the latest update. Category: SQL/Core Group: MonetDB5 "stable" Status: Open Resolution: None Priority: 5 Private: No Submitted By: olivia (opurvis) Assigned to: Niels Nes (nielsnes) Summary: Multi-table update produces incorrect results Initial Comment: When updating a table with values from another table, the update doesn't work properly. How to reproduce: create table table1 (col1 int,col2 int); create table table2 (col1 int,col2 int); insert into table1 (col1,col2) values (1,10),(2,20),(3,30); insert into table2 (col1,col2) values (1,100),(2,200); sql>select * from table1; +------+-----+ | col1 |col2 | +======+=====+ | 1 | 10 | | 2 | 20 | | 3 | 30 | +------+-----+ sql>select * from table2; +------+-----+ | col1 |col2 | +======+=====+ | 1 | 100 | | 2 | 200 | +------+-----+ update table1 set col2=(select table2.col2 from table2 where table2.col1=table1.col1) where exists (select * from table2 where table1.col1=table2.col1); sql>select * from table1; +------+-----+ | col1 |col2 | +======+=====+ | 1 | 100 | | 2 | 200 | | 3 |null | +------+-----+ The desired result should should be: sql>select * from table1; +------+-----+ | col1 |col2 | +======+=====+ | 1 | 100 | | 2 | 200 | | 3 | 30 | +------+-----+ Only two rows should be affected as demonstrated by a similar select: sql>select * from table1 where exists (select * from table2 where table1.col1=table2.col1); +------+-----+ | col1 |col2 | +======+=====+ | 1 | 10 | | 2 | 20 | +------+-----+ ---------------------------------------------------------------------- Comment By: Sjoerd Mullender (sjoerd) Date: 2008-11-10 11:37 Message: Test added: src/test/BugTracker-2008 update_error.SF-2254506. Could this be related to [ 2080207 ] The sub SELECT can't identify the outer table? ---------------------------------------------------------------------- You can respond by visiting: https://sourceforge.net/tracker/?func=detail&atid=482468&aid=2254506&group_id=56967 ------------------------------------------------------------------------- This SF.Net email is sponsored by the Moblin Your Move Developer's challenge Build the coolest Linux based applications with Moblin SDK & win great prizes Grand prize is a trip for two to an Open Source event anywhere in the world http://moblin-contest.org/redirect.php?banner_id=100&url=/ _______________________________________________ Monetdb-bugs mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/monetdb-bugs
