UPDATE one table using JOIN in MySQL 3.23

Posted on 2005-04-05
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
    LVL 10

    Expert Comment

    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

    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

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
    Both Easy and Powerful How easy is PHP? (  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    737 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

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now