Update with a join statement error 1

[cyoung]

I am trying to update a table when there are matching items in another table

Table A - inventory
itemnum
balance

Table B - inventory_transactions
itemnum
qty
invoicenum

What I am trying to do, is to add the inventory qty back into the inventory table when an invoice gets cancelled or changed

Here is my SQL statement

UPDATE inventory SET
inventory.balance = inventory.balance + inventory_transactions.qty
INNER JOIN inventory_transactions
ON inventory.itemnum = inventory_transactions.itemnum
WHERE inventory.itemnum = inventory_transactions.itemnum
AND inventory_transactions.invoicenum = '1'

I am getting an error in the syntax near the INNER JOIN

 

[sleipnir214]

What version of MySQL are you running?

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[cyoung]

I am running 3.23.52

 

[sleipnir214]

You can't use joins in update statemens on that version of MySQL. I believe it was introduced in version 4.0.4.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[cyoung]

thanks for the info.... guess I really do need to update to 4 now

 

[cyoung]

Thanks again for pointing me in the right direction. Upgraded and all is well!