CODEWITHSUNDEEP
mysqlsql
StackAddict
StackAddict

Reputation: 423

Update for duplicate entry in the same table

I have a table as follows...

Initial Table

I want a selective update for this table.

If the ID has a "duplicate entry" AND the INFO of ID is NULL, then the SERVICE of the NULL tuple should be updated in the FOR_ID field of the NON-NULL tuple..

For eg:- 660060110 is a duplicate entry and the one with SERVICE -> NM has INFO as NULL. So, the FOR_ID of the tuple with SERVICE -> IS should become NM. While 660074540 has duplicate entry but no NULL INFO. So no updations will occur for that.

The final table should be something like as follows :

Final Table

Please help me out in this. Thanks.

Upvotes: 1

Views: 160

Answers (1)

StuartLC
StuartLC

Reputation: 107407

This should do it - by joining the table of prospective table of rows to be updated (i.e. INFO is NOT NULL) to a derived table of rows which are to be referenced (i.e. INFO is NULL) on the common key, ID:

UPDATE MyTable mt
   INNER JOIN
   (
     SELECT ID, `SERVICE`
     FROM MyTable
     WHERE INFO IS NULL
   ) x
   ON x.ID = mt.ID 
     AND INFO IS NOT NULL
   SET mt.FOR_ID = x.`SERVICE`

SqlFiddle here

Upvotes: 2

Related Questions