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?

It should get the text value pair of the primary key (L_Status) in the 'status' table. — AME
– AME, Commented Dec 22, 2012 at 21:56

Michael Berkowski · Accepted Answer · 2012-12-22 21:56:33Z

8

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.

answered Dec 22, 2012 at 21:56

Michael Berkowski

271k47 gold badges452 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using MySQL: Update field with values using Inner Join

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related