UPDATE one table using JOIN in MySQL 3.23


I'm trying to update one table where a field in another table is a certain value, but I'm not sure if it's possible in v3.23 and if so, what the syntax would look like.

With an example table structure like:

t1 (a)
t2 (b, a)
t3 (c, b, d)

I want to run something like UPDATE t1 SET f1 = 'foo' but only update that field WHERE t1.a = t2.a AND t2.b = t3.b AND t3.d = 'bar'.
Who is Participating?
Multi-table update was not added in MySQL 4.0.4.  You can:
- upgrade to a later version of MySQL
- use some client language to select the appropriate records from the tables, and then create updates statements
- use a combination of INSERT...SELECT and temporary tables to accomplish the same thing as the second option above, but using just SQL.
Try this:

UPDATE t1 x, t2 y, t3 z
set x.f1='foo'
where x.a=y.a
and y.b = z.b
and z.d='bar'

I presume that f1 is another column in table t1.
Sorry, that first line should have read:
Multi-table update was not added until MySQL 4.0.4
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.