Update records with JOIN command

Question

I have the following query that works great and shows me where the tbl_staff.staff_id and tbl_lead.rlog_create_user_id values do not match.

    SELECT
tbl_staff.staff_id,
tbl_staff.username,
tbl_lead.rlog_create_user_name,
tbl_lead.rlog_create_user_id
    FROM
    tbl_staff
    JOIN tbl_lead ON tbl_staff.username = tbl_lead.rlog_create_user_name
    AND tbl_staff.staff_id <> tbl_lead.rlog_create_user_id;

The query returns values like this where you can see the 1014 does not match the 1004.

1014    bubba   bubba   1004

I want to update the value in the tbl_lead.rlog_create_user_id to the same value as is found in tbl_staff.staff_id.

I tried to insert a SET command but it's giving me a generic syntax error:

        SELECT
    tbl_staff.staff_id,
    tbl_staff.username,
    tbl_lead.rlog_create_user_name,
    tbl_lead.rlog_create_user_id
        FROM
        tbl_staff
        JOIN tbl_lead ON tbl_staff.username = tbl_lead.rlog_create_user_name
        AND tbl_staff.staff_id <> tbl_lead.rlog_create_user_id
SET tbl_lead.rlog_create_user_id=tbl_staff.staff_id ;

The actual error is:

[Err] 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 'SET tbl_lead.rlog_create_user_id=tbl_staff.staff_id' at line 10

I tried to change the SELECT to and UPDATE command using this question but still could not get it working: How can I do an UPDATE statement with JOIN in SQL?

The syntax is incorrect. Take a look at the syntax for the UPDATE command. That is what you want to use, not a SELECT command. — Sloan Thrasher, Aug 07 '18 at 15:29
I tried using update with this question and could not get it working: https://stackoverflow.com/questions/1293330/how-can-i-do-an-update-statement-with-join-in-sql — Rocco The Taco, Aug 07 '18 at 15:35

score 2 · Accepted Answer · answered Aug 07 '18 at 15:51

2

Try this

UPDATE tbl_lead
JOIN tbl_staff ON tbl_staff.username = tbl_lead.rlog_create_user_name
SET tbl_lead.rlog_create_user_id = tbl_staff.staff_id 
WHERE tbl_staff.staff_id <> tbl_lead.rlog_create_user_id;

answered Aug 07 '18 at 15:51

Eric

3,165
1
19
25

score 0 · Answer 2 · answered Aug 07 '18 at 15:47

0

Have you tried like this ?

UPDATE tbl_staff, tbl_lead 
  SET tbl_lead.rlog_create_user_id = tbl_staff.staff_id
  WHERE tbl_staff.username = tbl_lead.rlog_create_user_name

answered Aug 07 '18 at 15:47

IVO GELOV

13,496
1
17
26

`tbl_lead` is updated with the corresponding value from `tbl_staff` – IVO GELOV Aug 07 '18 at 15:58

Update records with JOIN command

2 Answers2