0

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
);
Juned Ansari
  • 5,035
  • 7
  • 56
  • 89
  • [You can't specify target table for update in FROM clause](https://stackoverflow.com/questions/4429319/you-cant-specify-target-table-for-update-in-from-clause/) – Lukasz Szozda Sep 29 '19 at 08:58

1 Answers1

2

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)
Madhur Bhaiya
  • 28,155
  • 10
  • 49
  • 57
  • it says "Error Code: 1406 Data too long for column 'step1_pincode' at row 395576" – Juned Ansari Sep 29 '19 at 09:02
  • @JunedAnsari well this error has nothing to do with the query but your schema definition. Ensure that `step1_pincode` , `RetailerPincode` and `StorePartyPincode` (all these three columns have similar datatype and size defined). Do `SHOW CREATE TABLE test` and add the details of that to the question. – Madhur Bhaiya Sep 29 '19 at 09:03
  • 1
    @MadhurBhaiya A more concise version: `SET a.step1_pincode = COALESCE(a.RetailerPincode, a.StorePartyPincode)` – Tim Biegeleisen Sep 29 '19 at 09:08