more mysql help?

I dont know how good your mysql is but, In the docs it says:

------
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.

Note: You cannot use ORDER BY or LIMIT with multiple-table UPDATE.

Before MySQL 4.0.18, you needed the UPDATE privilege for all tables used in a multiple-table UPDATE, even if they were not updated. As of MySQL 4.0.18, you need only the SELECT privilege for any columns that are read but not modified.
----------

I need to do exactly that multiple table update operation, but i think im using version 3.23 of mysql. So it messes up saying I have a syntax error...
Do you know how I would do it ?

Could someone give me an example sql code on how:

many multiple thanks in advance ?

aku beg
Friday, May 21, 2004

Options..

o Update to MySQL 4.0.4

o Use multiple Update statments

o Use myODBC connector and ADO. Then you can get a recordset with the joins in place and just add a row to it.

...off the top of my head

Eric Debois
Friday, May 21, 2004

I concur.

Clay Whipkey
Friday, May 21, 2004

Start a transaction and update all your tables, one at a time - if any barf then unroll your transaction, else commit it.

That is, if your Mysql has transactions, which was an add on a year ago when I last used it.

5v3n
Friday, May 21, 2004

Assuming you're using InnoDB tables (not an addon, just another table type) use transactions.

Otherwise,  upgrade.  Just be aware that the licenses for the connectors changed with 4.x and they're now GPL licensed.

Koz
Saturday, May 22, 2004

  Recent Topics

  Fog Creek Home