phparion Posted January 1, 2008 Share Posted January 1, 2008 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? Quote Link to comment https://forums.phpfreaks.com/topic/83939-how-to-use-select-in-update/ Share on other sites More sharing options...
Barand Posted January 1, 2008 Share Posted January 1, 2008 UPDATE brewers3 SET bwCountry= CASE bwState WHEN '' THEN bwCity ELSE bwState END WHERE bwCountry = '' Quote Link to comment https://forums.phpfreaks.com/topic/83939-how-to-use-select-in-update/#findComment-427223 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.