Search code examples
mysqlsql-updatesql-insert

Update existing rows with values of new row if given field is identical

My MySQL knowledge is a bit shaky. I have a table with (among others) the following columns/values:

ID | importID | distID | email     | street       | city
-----------------------------------------------------------
25 | 5        | 2      | [email protected] | Main Road    | London
-----------------------------------------------------------
26 | 5        | 2      | [email protected] | Oak Alley    | York
-----------------------------------------------------------
27 | 5        | 2      | [email protected] | Tar Pits     | London
-----------------------------------------------------------
28 | 5        | 2      | [email protected] | Fleet Street | London
-----------------------------------------------------------
...
-----------------------------------------------------------
99 | -1       | 2      | [email protected] | New Street   | Exeter

I do some checks when new rows are inserted: validate email addresses, find doublets with different dist(ributor)ID etc.

One of the tasks is "update existing rows with data of the freshly imported row when column "email" is identical" (yes, there can be multiple rows with identical email addresses).

At the time this task is performed, the importID of the currently inserted rows is always -1. I tried aliasing with all kinds of variations of

UPDATE table orig table dup
SET orig.street = dup.street, orig.city = dup.city
WHERE orig.email = dup.email

or joining with numerous variations of

UPDATE table orig
JOIN 
  (SELECT email FROM table
  WHERE importID != -1) dup
ON orig.email = dup.email
SET orig.street = dup.street, orig.city = dup.city

What is my mistake?

Solution

  • Don't do it that way.

    Store the duplicate email information in a separate table. That table is to contain records that are awaiting analysis and confirmation; do not try to include them in the main table.

    This extra table can have multiple rows with the same email, but the main table must not.

    The person processing the pending changes would use a SELECT into both tables (either two Selects or a Union), make a decision, and poke a button. One button would say to toss the new info; one would say to replace; etc.

    And, as Tim suggests, have a TIMESTAMP in each table. This will assist the user in making changes, especially if there are multiple pending changes.