All Forums Database
barni07 8 posts Joined 02/06
20 Mar 2006
Run an Update with a subselect

Is it possible to build an update statement with a subselect? I mean, I have a table T1 with two fields: Field1 (PRIMARY KEY), Field2. I have another table T2 with two fields: T2_1, T2_2. I need something like this:UPDATE T1 SET Field2 = (SELECT MIN(T2.T2_2) FROM T2 WHERE T2.T2_1 = T1.Field1)I can't solve the problem this way:UPDATE T1 FROM T2 SET Field2 = T2_2 WHERE Field1 = T2_1 because there are multiple rows in T2 for each value of Field1.I hope I have explained my problem properly.Thanks for your help!

barni07 8 posts Joined 02/06
20 Mar 2006

I reply to myself:UPDATE T1 FROM (SELECT t2_1, MIN(t2_2) from T2 group by 1) as D (D1, D2) SET Field2 = D2 WHERE Field1 = D1Got it :-)

williamdieter 15 posts Joined 03/06
21 Mar 2006

a bit cleaner :update afromtable1 a,(selectcolumn1,min(column2) as column2fromtable2group by 1) bsetcolumn2 = b.column2wherea.column1 = b.column1;If table2 has more rows than table1 you might want to include a join in the derived table to improve performance.update afromtable1 a,(selectb1.column1,min(b1.column2) as column2fromtable1 a1inner jointable2 b1ona1.column1 = b1.column1group by 1) bsetcolumn2 = b.column2wherea.column1 = b.column1;

DragonIV 1 post Joined 03/06
21 Mar 2006

ok, and in a similar vein:update table1 afrom( select col1 ,col2 ,col3 from table2 b where b.keycol = a.keycol)doesn't seem to work, as I get an illegal usage of alias name error (3993).if I replace a.keycol with table1.keycol, it tells me it doesn't know what table1 is.Any ideas?

21 Mar 2006

The query will fail with "illegal usage of alias name "because we cannot alias a table with the update clausethe aliasing should be done at the from clause.so update table1 awill failthis can be written asupdate afrom table1 as a, table2 .......and at the set clause we do not qualify the field with the table namei.e.update afrom table1 a, (select field1, min(field2) from table2 group by 1) b(col1, col2)set field2 = b.col2where a.field1 = b.col1if the set statement is given as set a.field2 = b.col2this fails with3706: Syntax error: expected something between the word 'a' and '.'. "if I replace a.keycol with table1.keycol, it tells me it doesn't know what table1 is."This does not work because when you are creating a derived table it does not have access to the earlier table that you used in the query.

You must sign in to leave a comment.