Jump to content

Recommended Posts

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?

Link to comment
https://forums.phpfreaks.com/topic/83939-how-to-use-select-in-update/
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.