CODEWITHSUNDEEP
mysql
Juned Ansari
Juned Ansari

Reputation: 5275

update query "You can't specify target table 'a' for update in FROM clause"

I am trying to add new column and wants update its value based on some condition but it does not allow me to do that it says "You can't specify target table 'a' for update in FROM clause"

Any Idea, how to do that?

mysql

ALTER TABLE test ADD COLUMN step1_pincode VARCHAR(20);

UPDATE test a SET a.step1_pincode = 
(
SELECT CASE WHEN b.RetailerPincode IS NOT NULL 
       THEN RetailerPincode
       ELSE b.StorePartyPincode
END AS step1_pincode1
FROM test b
);

Upvotes: 0

Views: 98

Answers (1)

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28844

In MySQL, you cannot specify the same table in SET clause during UPDATE. Moreover, you don't really need a subquery in your case; you can simply use the conditional CASE .. WHEN expression directly. Try the following:

UPDATE test a 
SET a.step1_pincode = CASE WHEN a.RetailerPincode IS NOT NULL 
                           THEN a.RetailerPincode
                           ELSE a.StorePartyPincode
                      END

As @TimBiegeleisen rightly suggested in comments, you can actually write this query using COALESCE() function, in a concise manner:

UPDATE test 
SET step1_pincode = COALESCE(RetailerPincode, StorePartyPincode)

Upvotes: 2

Related Questions