List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:March 19 1999 10:47am
Subject:SQL Question??: Using Join in Update
View as plain text  
>>>>> "Brandon" == Brandon Shuey <brandon@stripped> writes:

Brandon> I am running mySQL under NT4.0.
Brandon> I am trying a simple update statement that does a left join and updates on
> null values.  The exact same join works in a select
Brandon> statement but will not work in a update statement.  I get the following
> error:
Brandon> ERROR 1064 at line1: parse error near 'LEFT JOIN table2 ON
> table1.ID=table2.Field'

Brandon> This is the select statement that works correctly:
Brandon> SELECT table1.* FROM table1
Brandon> LEFT JOIN table2 ON(table1.ID = table2.Field)
Brandon> WHERE (((table2.Field) is NULL))

Brandon> Here is the update that doesn't work:
Brandon> UPDATE table1
Brandon> LEFT JOIN table2 ON table1.ID = table2.Field
Brandon> SET table1.ID = "Invalid"
Brandon> WHERE (((table2.Field) Is Null));

Brandon> The documentation does not address joins in Update queries so I do believe
> this is a valid question.

Hi!

The UPDATE clause is not allowed in ANSI SQL (and not in MySQL, even
if we have it on the TODO).

crash-me (http://www.mysql.com/crash-me-choose.htmy) tests the above
clause with 14 common SQL servers, and only Access supports the above.

Regards,
Monty
Thread
SQL Question??: Using Join in Update(Brandon Shuey)18 Mar
  • SQL Question??: Using Join in Updatesinisa19 Mar
  • SQL Question??: Using Join in UpdateMichael Widenius19 Mar