[RESOLVED] UPDATE LEFT JOIN not working - what's wrong with my syntax?

lazzerous

What am I doing wrong here - all of the examples I've seen follow this same format..

Thank you in advance for any assistance you may provide - this is baffling me

UPDATE part_numbers_master
LEFT JOIN part_numbers
ON part_numbers_master.pn_full=part_numbers.pn_full
SET part_numbers_master.table_id=part_numbers.table_ids
WHERE part_numbers_master.table_ids='0';

ragefu

As far as I know, you can't use Joins for Inserts or Updates. Someone correct me if I'm wrong.

lazzerous

Yes you can

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. The table_references part lists the tables involved in the join. Its syntax is described in Section 13.2.7.1, “JOIN Syntax”."

Roger_Ramjet

Well, you don't want a left join to start with - can't set a value from part_nos in master if it does not have a matching row in part_nos. Change that to an inner join, or comma.

Would help if you told us what was happening a bit more explicitly. Are only some of the rows being updated? Are none of them? What happens if you just run a select query with the same conditions - does it return any rows?

Try this


$sql = "UPDATE part_numbers_master, part_numbers
           SET part_numbers_master.table_id=part_numbers.table_ids
           WHERE part_numbers_master.pn_full=part_numbers.pn_full
           AND part_numbers_master.table_ids='0'";

lazzerous

I apologize for the lack of info. I had hoped my statement would have a blaring mistake that I just didn't see..

I am getting a syntax error - I tried running with a comma as you siggested before I posted and received the following error:
mysql> UPDATE part_numbers_master, part_numbers
-> SET part_numbers_master.table_id=part_numbers.table_ids
-> WHERE part_numbers_master.pn_full=part_numbers.pn_full
-> AND part_numbers_master.table_ids='0';
ERROR 1064: You have an error in your SQL syntax near ' part_numbers
SET part_numbers_master.table_id=part_numbers.table_ids' at line 1

I don't see the syntax error there - do you?
Could it be that we're saying update two tables, when only one table's values are being modified?

What else could cause a syntax error?

Drakla

I've just tested that query and it works fine - you sure you've got a version of mysql capable of running it? Run

SELECT version();

lazzerous

3.23.58

Drakla

lazzerous wrote:
3.23.58

Ouch - I hate it when you're trying to debug something that isn't bugged.

You're either going to have to upgrade your version of mysql or split the query.

lazzerous

I'm upgrading to mysql 4.1 & php 4.3 now

lazzerous

The upgrade did it - thank you guys!

Roger_Ramjet

Having posted the quote, one assumed that you had read it and knew that you were running an appropriate version.

lazzerous

so far as I knew I was running an appropriate version. I had no idea that the join issue existed with that particular version of MySQL, hence my coming in for help.