Hi, everyone.
I have a database with two tables and I need to update a column in the
first column by cross-referencing it with a column in the second table.
Is there a way I can do this in MySQL? I read that some systems have a
FROM clause in the UPDATE statement, but as far as I can tell, MySQL
does not have this feature.
You can only refer to 1 table in a MySQL UPDATE statement. There are a couple of workarounds, but the most general purpose one is to use a SELECT to load the updated row into a TEMPORARY table, DELETE to old row from the original table, and INSERT INTO from the temp table to the original table. That requires 3 SQL statements. Another possibility is to create the updated row in the original table w/ a new primary key, delete the old row, and change the primary key for the new row to the old row's primary key.
Bob Hall
Know thyself? Absurd direction!
Bubbles bear no introspection. -Khushhal Khan Khatak