Jump to content

Combining updating two tables into one query?


joe92

Recommended Posts

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.