[thelist] MySQL subquery
Paul Cowan
evolt at funkwit.com
Fri Oct 28 01:53:50 CDT 2005
Hi Mark,
Assuming there's a 1-1 relationship between t1 and the relevant rows of
t2 (e.g. attributeid, userid is a unique pair) then this will do what
you want in MSSQL. It is (I believe) standard SQL-92, and fairly basic
at that, so I'd be very disappointed if it didn't work in MySQL!
instead of:
> update t1 set repname = (select t2.value
> from t1
> where t2.attributeid = '4' and t2.userid = t1.id)
try:
update t1 set repname = t2.value
from t2
where t2.attributeid = '4' and t2.userid = t1.id
UPDATE ... FROM is a poorly understood syntax sometimes, and
occasionally a confusing one, but it should do what you want here. It's
actually probably a bit simpler for the query processor to work out too.
Cheers,
Paul
More information about the thelist
mailing list