Combining updating two tables into one query?

[joe92]

Is it possible (and faster) to combine updating two tables in the same database into one query where the WHERE clause is identical? I cannot combine the tables together unfortunately and I can't seem to get these queries combined. Just to note, it definitely works prior to trying to combine the two.

 

The queries trying to combine:

<?php
//first query
$query1 = mysql_query("UPDATE
			table1
		SET
			item1 = '$newItem1',
			date = CURDATE()
		WHERE
			ID = '$id'
	");
//second query
$query2 = mysql_query("UPDATE
			table2
		SET
			item2 = '$newItem2'
		WHERE
			ID = '$id'
	");

 

I have tried the following code but it does not work?

<?php
//combine the two queries?
$query = mysql_query("UPDATE
			table1 AS a,
			table2 AS b
		SET
			a.item1 = '$newItem1',
			a.date = CURDATE(),
			b.item2 = '$newItem2'
		WHERE
			ID = '$id'
	");

 

Thanks for any help,

Joe

[PFMaBiSmAd]

If you execute that query directly against your database or use error checking logic in your php code, you will get an error - Column 'ID' in where clause is ambiguous

 

You need to tell it which id column to use and since this is actually a join, you probably need to include in the where clause a statement to only join together rows with the same id in both tables.

 

Untested, but should work -

 

WHERE a.ID = b.ID AND a.ID = 1

[joe92]

If you execute that query directly against your database or use error checking logic in your php code, you will get an error - Column 'ID' in where clause is ambiguous

 

You need to tell it which id column to use and since this is actually a join, you probably need to include in the where clause a statement to only join together rows with the same id in both tables.

 

Untested, but should work -

 

WHERE a.ID = b.ID AND a.ID = 1

 

Now I feel so foolish for not printing the mysql_error()

 

That has worked perfectly, thank you!