[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