how to use SELECT in UPDATE

[phparion]

Hi

 

I have a table which has three columns,

 

bwCountry,bwState and bwCity.

 

The values of bwCountry, somehow, on some occasions are stored in bwState and bwCity columns.

 

I can display all the values of bwCountry with this SELECT.

 

SELECT DISTINCT
CASE bwCountry
WHEN ''
THEN CASE bwState
WHEN ''
THEN bwCity
ELSE bwState
END ELSE bwCountry
END
FROM brewers

 

 

this command works fine.

 

But instead of doing this every time I have to display the countries I want to fix the table values. I wanted to run an UPDATE command like this,

 

UPDATE brewers3 
SET bwCountry=
(SELECT DISTINCT 
CASE bwCountry 
WHEN '' 
THEN CASE bwState 
WHEN '' THEN bwCity 
ELSE bwState 
END 
ELSE bwCountry 
END 
FROM brewers3);

 

 

from this query I get the following error,

 

 

 

ERROR 1093 (HY000): You can't specify target table 'brewers3' for update in FROM clause

 

 

I have a copy of this table so I tried this following,

 

UPDATE brewers3 
SET bwCountry=
(SELECT DISTINCT 
CASE bwCountry 
WHEN '' 
THEN CASE bwState 
WHEN '' THEN bwCity 
ELSE bwState 
END 
ELSE bwCountry 
END 
FROM brewers2);

 

 

 

and now I get this error

 

 

ERROR 1242 (21000): Subquery returns more than 1 row

 

 

 

I don't want to write a php script that reads all the table and then update the row-by-row records. So is there any work-around for this?

[Barand]

UPDATE brewers3
SET bwCountry=
CASE bwState
WHEN '' THEN bwCity
ELSE bwState
END
WHERE bwCountry = ''