Using MySQL: Update field with values using Inner Join

Question

I have a main table and an index table. Both tables share a common primary field called "L_Status". I want to update the data in the main table from "L_Status" values (integers) into "L_StatusLV" (readable text values) based on the reference in the index table called "status". Here is the code I've entered into PHPmyAdmin to accomplish this:

UPDATE markers.L_Status 
FROM markers
INNER JOIN STATUS ON markers.L_Status = status.L_Status
WHERE  markers.L_Status = status.L_StatusLV

PHPmyAdmin returns the following 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 markers INNER JOIN STATUS ON markers.L_Status = status.L_Status WHERE ma' at line 2

Any advice on the sytax error here?

Answer 1

MySQL's UPDATE JOIN syntax differs from SQL Server's (which looks like what you have):

UPDATE
  /* First join the tables */
  markers 
  INNER JOIN status ON markers.L_Status = status.L_Status
/* Then specify the new value in the SET clause */
SET markers.L_Status = status.L_StatusLV

However, as you noted above the current values are integers. If the column markers.L_Status is an INT column rather than a CHAR/VARCHAR as I assume the human-readable column is, this won't work.

Visit MySQL's UPDATE syntax reference for the full syntactic details. In particular, the table_references.