[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
Ie

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