MySQL Update & Join-CodePudding

I have a MySQL table containing contact details, and I need to flag duplicates of the column "PHONE1" into another flag column called "DUPLICATE" (ignoring the first, original, row).

I found an SQL tutorial with an example SQL query doing almost exactly what I want here: https://www.sqlservertutorial.net/sql-server-basics/sql-server-update-join/.

I modified the tutorial example, and I can "SELECT" the duplicate rows correctly with the following query:

SELECT t1.ID, t1.`Contact Name`, t1.PHONE1
FROM new_leads_test2 AS t1
INNER JOIN new_leads_test2 AS t2
ON (t1.PHONE1 = t2.PHONE1
AND t1.ID > t2.ID)

this "SELECT" query works 100%, but when I try to alter this query to actually UPDATE the rows (per the tutorial), I get an SQL syntax error. Here's my query that generates the error:

UPDATE t1
SET t1.duplicate = t2.ID
FROM new_leads_test2 AS t1
INNER JOIN new_leads_test2 AS t2
ON (t1.PHONE1 = t2.PHONE1
AND t1.ID > t2.ID)

This results in an SQL error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM new_leads_test2 AS t1
INNER JOIN new_leads_test2 AS t2
ON t1.PHONE1 = t2.' at line 3

Can anyone please help me with this?

CodePudding user response：

We might put SET after INNER JOIN because UPDATE Statement, table_reference use before SET

BTW maybe you might not need FROM

UPDATE [LOW_PRIORITY] [IGNORE] table_reference
SET assignment_list
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]

so we might use like below.

UPDATE new_leads_test2 AS t1
INNER JOIN new_leads_test2 AS t2
ON (t1.PHONE1 = t2.PHONE1
AND t1.ID > t2.ID)
SET t1.duplicate = t2.ID

CodePudding user response：

Your example is a SQL Server syntax, not MySQL.

Try with:

UPDATE new_leads_test2 t1
 INNER JOIN new_leads_test2 t2
    ON (t1.PHONE1 = t2.PHONE1 AND t1.ID > t2.ID)
   SET t1.duplicate = t2.ID