i've wrote the below query to update all rows as duplicate apart from the first one. I am getting an error with the aliases. Can somebody help?

    `table`.`statuscode` = 'DUP'
    `table` AS `a`,
    `table` AS `b`

    -- IMPORTANT: Ensures one version remains
    -- Change "ID" to your unique column's name
    `a`.`id` > `b`.`id`

    -- Any duplicates you want to check for
    AND `a`.`firstname` <=> `b`.`firstname`
    AND `a`.`surname` <=> `b`.`surname`
    AND `a`.`email` <=> `b`.`email`;
  • Do you think posting the actual error message might be useful? – mustaccio Jul 19 '19 at 14:00
  • Have a look at UPDATE - JOIN syntax here – McNets Jul 19 '19 at 14:23
  • Please provide SHOW CREATE TABLE. – Rick James Jul 19 '19 at 18:12

This worked for me in MS SQL, hope it is similar in MySQL

SET  A.statuscode = 'DUP'
FROM [table] a
INNER JOIN [table] b ON a.ID <> b.ID 
AND a.ID > b.ID
AND a.firstname = b.firstname
AND a.surname = b.surname
AND a.email = b.email; 
  • Remove a.ID <> b.ID. – Rick James Jul 19 '19 at 18:14
  • Ah yes that is a bit redundant, isn't it... – outjet Jul 24 '19 at 14:42

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.