Run an Update with a subselect

Database
Enthusiast

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!
4 REPLIES
Enthusiast

Re: Run an Update with a subselect

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 = D1

Got it :-)
Enthusiast

Re: Run an Update with a subselect

a bit cleaner :

update a
from
table1 a
,
(
select
column1
,min(column2) as column2
from
table2
group by 1
) b
set
column2 = b.column2
where
a.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 a
from
table1 a
,
(
select
b1.column1
,min(b1.column2) as column2
from
table1 a1
inner join
table2 b1
on
a1.column1 = b1.column1
group by 1
) b
set
column2 = b.column2
where
a.column1 = b.column1
;

Re: Run an Update with a subselect

ok, and in a similar vein:

update table1 a
from
(
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?
Enthusiast

Re: Run an Update with a subselect

The query will fail with "illegal usage of alias name "
because we cannot alias a table with the update clause
the aliasing should be done at the from clause.

so update table1 a
will fail
this can be written as

update a
from table1 as a, table2 .......

and at the set clause we do not qualify the field with the table name

i.e.
update a
from table1 a, (select field1, min(field2)
from table2
group by 1) b(col1, col2)
set field2 = b.col2
where a.field1 = b.col1

if the set statement is given as
set a.field2 = b.col2

this fails with
3706: 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.