[thelist] mysql UPDATE SELECT query

[Ken Snyder]

Bojan Tesanovic wrote:
> ...
> update ads set img_1_small= (select  img_1_small from ads where  
> id=37) where img_1_small is NULL;
> ...
>
> is there a way to do this with one SQL query ?
>
>
> Bojan Tesanovic
> http://www.carster.us/
>
>   

Does it allow you to use insert and delete?

INSERT INTO ads
(id, field1, field2)
SELECT NULL, b.field1, b.field1 FROM ads b WHERE id = 37;
DELETE FROM ads WHERE img_1_small IS NULL;

The other option is to use a temporary table with the same structure as 
ads then do it all in one transaction:

BEGIN
CREATE TABLE #tmp_ads (...);
INSERT INTO #tmp_ads
SELECT * FROM ads WHERE id = 37;
INSERT INTO ads SELECT * FROM #tmp_ads;
DELETE FROM ads WHERE img_1_small IS NULL;
END

 From your example, ads seems to not have a primary key, but I'm not sure.

- Ken Snyder