SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Enthusiast
    Join Date
    Apr 2007
    Posts
    43
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Update Duplicate Rows

    There are zillions of messages on how to delete duplicate rows, and insert on duplicate key etc., but I am unable to find how to update duplicate rows.

    Table:
    id | name | status
    1 | John | 1
    2 | Sara | 1
    3 | Doe | 1
    4 | Julie | 1
    5 | John | 1
    6 | Smith| 1

    I am trying to update status of all John's (duplicate) to 2. How to update status of all duplicate names to 2?

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Code:
    CREATE TABLE tmp (id int);
    INSERT INTO tmp SELECT MIN(id) FROM table GROUP BY name;
    UPDATE table SET status = 2 WHERE id NOT IN (SELECT id FROM tmp);
    The temporary table is because you can't have the table you're updating in the FROM clause of a SELECT in the same UPDATE query.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •