[Last Call] Learn how to a build a cloud-first strategyRegister Now


UPDATE one table using JOIN in MySQL 3.23

Posted on 2005-04-05
Medium Priority
Last Modified: 2012-08-13

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'.
Question by:ftsao
  • 2
LVL 10

Expert Comment

ID: 13712120
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.
LVL 33

Accepted Solution

snoyes_jw earned 500 total points
ID: 13712148
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.
LVL 33

Expert Comment

ID: 13712154
Sorry, that first line should have read:
Multi-table update was not added until MySQL 4.0.4

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month17 days, 23 hours left to enroll

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question