6

This is the problem I'm trying to figure out in MySQL. We have an old table contains some forms submitted by our users. Somehow, the previous decision was each time a user comes to this survey, a new form will be submitted. So in the old table we easily have several rows with the same Firstname, Lastname, but different values in the other columns, and there's a timestamp column Date_Submission as well.

Now we are trying to move everything to a new table, but this time, for each person we only keep one row. And we want to keep some of the the latest old data of that user (like email, phone number, etc)

I could do the following:

update newtable, oldtable set newtable.email = oldtable.email where newtable.firstname = oldtable.firstname and newtable.lastname = oldtable.lastname;

Apparently this won't give me the "latest" old date for each person.

So I tried this one:

update newtable, oldtable set newtable.email = oldtable.email where newtable.firstname = oldtable.firstname and newtable.lastname = oldtable.lastname order by oldtable.Date_Submission;

But they MySQL will complain with:

"ERROR 1221 (HY000): Incorrect usage of UPDATE and ORDER BY".

So I'm wondering, what's the correct way to achieve this?

1 Answer 1

4

There are two options:

  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) 
    ... ;
    
  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 given 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);

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;
    

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

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.