SQL update two tables transaction syntax

Question

I have problems with my syntax for updating two tables in one go. My code currently looks like this:

if ($stmt = $mysqli->prepare('

            BEGIN TRANSACTION

            UPDATE items_woods
            SET items_woods.oak = ´1´
            FROM items_woods T1, skills_woodcutting T2
            WHERE T1.id = T2.id 
            and T1.id = ´?´

            UPDATE skills_woodcutting
            SET skills_woodcutting.exp = ´1´
            FROM items_woods T1, skills_woodcutting T2
            WHERE T1.id = T2.id
            and T1.id = ´?´

            COMMIT


            ')) {
                /* Bind parametres */
                $stmt->bind_param('i', $id);

                /* Insert the parameter values */
                $id = 1;

                /* Execute the query */
                $stmt->execute();

                /* Close statement */
                $stmt->close();

            } else {
                /* Something went wrong */
                echo 'Something went terrible wrong' . $mysqli->error;
            }

I am running a MySQL server and I can't see where the problem is, if anyone can hint me to a correct syntax, it will be appreciated. Thanks.

Care to include your error message? *(But try using something like `UPDATE items_woods SET oak = '1' FROM skills_woodcutting WHERE items_woods.id = skills_woodcutting.id AND items_woods.id = '?'`)* — MatBailie, Feb 16 '14 at 22:51
The only thing that happens when it's executed is the error at the bottom "Something went terrible wrong". Uhm as I see it, my syntax should be correct, however there must be something that isn't — user3287771, Feb 16 '14 at 22:59
I can also see two parameters in the query, and only one parameter bound. — MatBailie, Feb 16 '14 at 23:00
I have tried following this post: http://stackoverflow.com/questions/2044467/how-to-update-two-tables-in-one-statement-in-sql-server-2005 — user3287771, Feb 16 '14 at 23:03

score 2 · Accepted Answer · edited May 23 '17 at 10:31

I have tried following this post:How to update two tables in one statement in SQL Server 2005?

The above-mentioned post is a bad source of information in your case because it is for SQL Server instead of MySQL.

SQL Server and MySQL have different syntax for UPDATE:

SQL Server doesn't allow simultaneous updates to multiple tables while MySQL does. Therefore you don't need two updates.
In MySQL SET clause goes after all table references while in SQL Server while in SQL Server before FROM clause
In MySQL there is no FROM clause in UPDATE

That being said in MySQL you can do it in one UPDATE statement by using a proper multi-table syntax

UPDATE items_woods t1 JOIN skills_woodcutting t2
    ON t1.id = t2.id 
   SET t1.oak = 1,
       t2.exp = 1
 WHERE t1.id = ?;

Here is SQLFiddle demo

Your php-code boils down to

$id = 1;
$sql = 'UPDATE items_woods t1 JOIN skills_woodcutting t2
            ON t1.id = t2.id 
           SET t1.oak = 1,
               t2.exp = 1
         WHERE t1.id = ?';

if ($stmt = $db->prepare($sql)) {
    $stmt->bind_param('i', $id);
    $stmt->execute();
    $stmt->close();
} else {
    die('Error: ' . $db->error); //TODO:better error handling
}

Now apart from the differences in UPDATE syntax between SQL Server and MySQL and if, for some reason, you were in need of issuing more than one update statement then there would still be other issues with your code:

You can't prepare and execute at once multiple SQL statements with prepare() and execute(); You either prepare and execute them one by one or use mysqli_multi_query()
If you were using mysqli_multi_query() you'll have to terminate each SQL statement by semicolon ;.

this was a really nice answer, thanks a lot, I will try this in an hour. Cheers — user3287771, Feb 17 '14 at 17:20

SQL update two tables transaction syntax

1 Answers1