[thelist] mysql UPDATE SELECT query

Chris Anderson Chris at activeide.com
Tue Apr 1 12:37:38 CDT 2008

> I have a SQL problem , I am trying to update some field from one
> table and assign the value to that filed with SELECT statement
> here is example
> update ads set img_1_small= (select  img_1_small from ads where
> id=37) where img_1_small is NULL;
> but mysql return
> Error Code : 1093
> You can't specify target table 'ads' for update in FROM clause
> (0 ms taken)
> is there a way to do this with one SQL query ?

I haven't got a copy of MySQL to hand so cannot test, but I would try to
alias the inner select

update ads 
set img_1_small= 
	(select i.img_1_small from ads i where i.id=37) 
where img_1_small is NULL;

More information about the thelist mailing list