[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