Save Sorting from ORDER BY with One UPDATE Statement

order-byupdate

I have this table:

SELECT * FROM items

id | item      | position
---|-----------|----------
 1 | USB cable |        0
 2 | SD card   |        4
 3 | Mouse     |        2
 4 | Keyboard  |        0
 5 | Monitor   |        3

Sorting this table gives this result:

SELECT * FROM items ORDER BY position

id | item      | position
---|-----------|----------
 4 | Keyboard  |        0
 1 | USB cable |        0
 3 | Mouse     |        2
 5 | Monitor   |        3
 2 | SD card   |        4

Now I want to update the table and save the order in the position column:

SELECT * FROM items

id | item      | position
---|-----------|----------
 4 | Keyboard  |        1
 1 | USB cable |        2
 3 | Mouse     |        3
 5 | Monitor   |        4
 2 | SD card   |        5

Can this be done with a single query or do I have to manually loop over all rows and do a manual update?

In case the order is not fully defined (e.g. for USB cable and Keyboard above) I just arbitrarily decided the order.

Best Answer

UPDATE
   T1
SET
   position = T2.rn
FROM
   myTable T1
   JOIN
   (
   SELECT
      id,
      ROW_NUMBER() OVER (ORDER BY position) AS rn
   FROM
      myTable
   ) T2 ON T1.id = T2.id

Note: the order of "Keyboard" and "USB cable" is arbitrary. They both have position = 0

To tie-break positions based on item, add a secondary sort

      ROW_NUMBER() OVER (ORDER BY position, item) AS rn

If you have duplicate position,item pairs this will be arbitrary too...

OPTION #1 : INSERT INTO ... ON DUPLICATE KEY UPDATE

INSERT INTO newtable
SELECT * FROM oldtable
ORDER BY tmstamp_value
ON DUPLICATE KEY UPDATE email=VALUES(email), address=VALUES(address) ... ;

OPTION #2 : REPLACE INTO

REPLACE INTO newtable
SELECT * FROM oldtable
ORDER BY tmstamp_value;

It mechanically performs a DELETE and an INSERT.

CAVEAT

Notice I applied the ORDER BY against the old table. This should help get the latest data for a give LastName, FirstName.

You should also add a UNIQUE INDEX on the new table as follows:

ALTER TABLE newtable ADD UNIQUE INDEX name_ndx (lastname,firstname);

UPDATE 2013-04-08 17:13 EDT

If you are doing updates, there are two ways to do this:

1. UPDATE JOIN

UPDATE
(SELECT lastname,firstname,phone,email
FROM oldtable ORDER BY lastname,firstname) A
LEFT JOIN newtable B USING (lastname,firstname)
SET B.phone=A.phone,B.email=A.email;

2. UPDATE JOIN with GROUP BY

UPDATE
(
    SELECT BB.id,BB.phone,BB.email FROM 
    (SELECT lastname,firstname,MAX(id) id FROM oldtable GROUP BY lastname,firstname) AA
    INNER JOIN oldtable BB USING (lastname,firstname)
) A INNER JOIN newtable B USING (id)
SET B.phone=A.phone,B.email=A.email;

Give it a Try !!!

Both options will take full advantage of the UNIQUE index on name.

MySQL update one table from another joined many-to-many

This works - I had the JOIN wrong

UPDATE `events` 
  INNER JOIN `plays_in_events` ON (`plays_in_events`.`EventID` = `events`.`EventID`)
  INNER JOIN `plays` ON (`plays_in_events`.`PlayID` = `plays`.`PlayID`)
  SET events.Name = plays.Play
WHERE 
  events.Name IS NULL and plays_in_events.PlayID = 1

and the earlier comments were quite right, but it took me a while to 'get it' - thanks for your help. I had just copied the JOIN lines from the working SELECT query, but when I looked again, I could finally see what the commentators meant about events appearing twice, and plays_in_events not appearing properly in the JOINs.