By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,569 Members | 1,386 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,569 IT Pros & Developers. It's quick & easy.

Question about UPDATE and LEFT JOIN

P: n/a
Hi, I hope this is the right place to post, but, I am having a problem with
an UPDATE command and a LEFT JOIN, I am using something like:

UPDATE table_a LEFT JOIN table_b ON table_a.field1=table_b.field1 SET
table_b.field6='1' WHERE table_a.field2='1';

Something along those lines. Anyway, from what I can see on various web
sites, documentation, etc, I think it should work, however, I keep getting
this error...

#1064 - You have an error in your SQL syntax near 'LEFT JOIN table_b ON
table_a.field1 = table_b.field1 SET table_b.field6 = '1' ' at line 1

Any idea why this doesn't work? I tried rewriting it not to use the LEFT
JOIN, but, still received SQL syntax issues. I need to change a field in
table_b, but the only way I know which records to change is a field in
table_a.

Thanks.
Jul 23 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
The above syntax worked when I tried it, what version of mysql are you
using?
mysql>select version();

http://dev.mysql.com/doc/mysql/en/update.html

Starting with MySQL 4.0.4, you can also perform UPDATE operations that
cover multiple tables:

UPDATE items,month SET items.price=month.price
WHERE items.id=month.id;

The example shows an inner join using the comma operator, but
multiple-table UPDATE statements can use any type of join allowed in
SELECT statements, such as LEFT JOIN.

Jul 23 '05 #2

P: n/a
Great, I am pretty sure that the version is not 4.0 or better, it is back in
the 3's... I was afraid that is what the problem would be...

"Bill Turczyn" <bt******@gmail.com> wrote in message
news:11*********************@z14g2000cwz.googlegro ups.com...
The above syntax worked when I tried it, what version of mysql are you
using?
mysql>select version();

http://dev.mysql.com/doc/mysql/en/update.html

Starting with MySQL 4.0.4, you can also perform UPDATE operations that
cover multiple tables:

UPDATE items,month SET items.price=month.price
WHERE items.id=month.id;

The example shows an inner join using the comma operator, but
multiple-table UPDATE statements can use any type of join allowed in
SELECT statements, such as LEFT JOIN.

Jul 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.