MYSQL; Update Table A base on join from Table A and Table B

Question

Right of the bat, I'm going to say that I believe that this is what I want:

Update multiple rows using select statement. The answer seems to be for what I want to do.

My tables ares as follows. tPatientsIDs contains only two columns: keyid and uid.

tEyeResults contains a column named patientid and a column named puid. The values of patientid match the column uid from tPatientsIDs and puid is empty, having been created by altering the table recently.

All I want to do is set the value puid to the keyid in tPatientIDs that corresponds to the value of the column patientid.

Here is the query I'm using based on the answer on SO:

UPDATE tEyeResults SET puid = tPatientIDs.keyid FROM tPatientIDs WHERE 
tPatientIDs.uid = tEyeResults.patientid;

Even though the structure seems Identical to the answer I get the error:

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 
tPatientIDs 
WHERE tPatientIDs.uid = tEyeResults.patientid' at line 1

your link is for sqlserver but you have tagged mysql - multi table update is one of the differences between sqlserver and mysql.please read https://dev.mysql.com/doc/refman/8.0/en/update.html — P.Salmon, Dec 15 '18 at 11:00

score 1 · Accepted Answer · edited Dec 15 '18 at 11:52

1

Use JOIN with your update

UPDATE tEyeResults e 
JOIN tPatientIDs p ON p.keyId = e.patientId
SET e.puid = tPatientIDs.keyid

Maybe you need a where clause to only update one patient but I didn’t see one in your question

edited Dec 15 '18 at 11:52

Gordon Linoff

1,242,037
58
646
786

answered Dec 15 '18 at 11:00

Joakim Danielson

43,251
5
22
52

Thank you! This did exactly what I wanted! – aarelovich Dec 15 '18 at 11:38

MYSQL; Update Table A base on join from Table A and Table B

1 Answers1