|
|
|
Forum Newbie
Group: Forum Members
Last Login: 2/17/2007 8:14:00 AM
Posts: 8,
Visits: 1
|
|
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!
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 2/17/2007 8:14:00 AM
Posts: 8,
Visits: 1
|
|
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 
|
|
|
|
|
Junior Member
Group: Forum Members
Last Login: 11/19/2008 2:41:24 PM
Posts: 15,
Visits: 2
|
|
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
;
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 3/21/2006 3:27:00 PM
Posts: 1,
Visits: 1
|
|
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?
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 8/26/2009 6:42:55 PM
Posts: 116,
Visits: 27
|
|
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.
Feroz Shaik
|
|
|
|