----- Original Message -----
From: "Mike Blezien" <[EMAIL PROTECTED]>
To: "MySQL List" <mysql@lists.mysql.com>
Sent: Monday, March 27, 2006 3:39 PM
Subject: Update multiple tables
Hello,
I'm alittle unclear on how too update multiple tables. We have two tables
with the same column name:
account.state
account_service.state
when we update the account table, we also need to update the
account_service table with the same value for the 'state' column. Can this
be done with MySQL 4.1.12. and what is the correct syntax?
I think you're asking if you can update both tables within the same SQL
statement.
Unless MySQL works much differently than DB2, the main relational database I
use, you have to write a separate UPDATE statement for each table.
Relational databases (or at least DB2!) normally require that an UPDATE
statement can only affect a single table; you can't put multiple table names
in the FROM clause of an UPDATE statement.
Assuming you are using an engine that supports transactions, I would
definitely make a point of enclosing both updates within a single
transaction. That way, if one of the updates fails, they will both be rolled
back to maintain consistency. Otherwise, if the first update succeeds and
then you hit a problem, like a power failure, the column that is common to
both tables will have one value in one table and a different value in the
other table.
--
Rhino
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.3.2/293 - Release Date: 26/03/2006
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
