Update multiple tables with single UPDATE query

[elite311]

I have a few tables that I want to update when I change the value you in 1 table and I can't seem to figure out why my query is not working. I read about how to do this at http://dev.mysql.com/doc/refman/5.5/en/update.html and even though it looks correct to me it doesn't update anything.

 

I have 3 tables that I want to update from the 1 form:

 

assets, assethours, pm

 

my query looks like this:

$assetresult = $db->fetch_all_array("SELECT * FROM assets WHERE asset = '".$_GET['id']."'");

if(isset($_POST['updateit']))
{
$db->query("UPDATE assets a, assethours ah, pm p
			SET a.asset = '".$_POST['asset']."', 
			a.category = '".$_POST['category']."', 
			a.descrip = '".$_POST['descrip']."', 
			a.year = '".$_POST['year']."', 
			a.make = '".$_POST['make']."', 
			a.model = '".$_POST['model']."', 
			a.serial = '".$_POST['serial']."' 
			ah.hoursasset = '".$_POST['asset']."' 
			p.pmasset = '".$_POST['asset']."' 
			WHERE id = '".$_POST['asset']."'");

header("Location: assets.php?updated=1");
exit();
}

 

And my form looks like this:

            <form name="updateasset" action="assetedit.php" method="post">
              <?php foreach($assetresult as $assetinfo) { ?>

              	    <input style="width: 100%" name="asset" type="text" id="asset" value="<?php echo $assetinfo['asset'];?>" size="50"/>

     	            <input style="width: 100%" name="category" type="text" id="category" value="<?php echo $assetinfo['category'];?>" size="50"/>

             	    <input style="width: 100%" name="descrip" type="text" id="descrip" value="<?php echo $assetinfo['descrip'];?>" size="50"/>

   		     <input style="width: 100%" name="year" type="text" id="year" value="<?php echo $assetinfo['year'];?>" size="50"/>

                    <input style="width: 100%" name="make" type="text" id="make" value="<?php echo $assetinfo['make'];?>" size="50"/>

                    <input style="width: 100%" name="model" type="text" id="model" value="<?php echo $assetinfo['model'];?>" size="50"/>

                    <input style="width: 100%" name="serial" type="text" id="serial" value="<?php echo $assetinfo['serial'];?>" size="50"/>

           	   <input name="submit" type="submit" class="button" value="Save" />
    	   	   <input name="cancel" type="button" class="button" onClick="window.location='assets.php';" value="Cancel" />
               
               <input type="hidden" name="id" value="<?php echo $assetinfo['asset'];?>" />
               <input type="hidden" name="updateit" value="1" />
             </form> 

<?php } ?>

 

 

 

I hoping someone can tell me what I'm doing wrong here, when I run the update it says it was successful but nothing updates.

[jazzman1]

Do you get any mysql errors?

Is it true,  WHERE id = '".$_POST['asset']."'")

[jazzman1]

I meant, if you don't understand me correctly, which table it belongs this id ?

[elite311]

It's not showing me any errors, just goes back to the main page and says it has updated successfully.

 

All the tables have an id (auto increment). This is probably the wrong way to do this though after a lot of reading.

 

But as for your question is belongs to the assets table, I'm trying to say when it the id matches the asset number. What I'm trying to accomplish is when I change the asset # I want to change the asset #'s in the other tables as well.

 

So I have:

 

assets table

asset (the asset #)

 

assethours table

hoursasset (asset #)

 

pm table

pmasset (asset #)

 

I use these numbers in other query's so if I change it in the main assets table I need it to change in the other tables as well.

[jazzman1]

Put a mysql_error() function inside a die() function in the end of your query string.

Give us a result back, please.

$db->query("UPDATE assets a, assethours ah, pm p
SET a.asset = '".$_POST['asset']."', 
a.category = '".$_POST['category']."', 
a.descrip = '".$_POST['descrip']."', 
a.year = '".$_POST['year']."', 
a.make = '".$_POST['make']."', 
a.model = '".$_POST['model']."', 
a.serial = '".$_POST['serial']."' 
ah.hoursasset = '".$_POST['asset']."' 
p.pmasset = '".$_POST['asset']."' 
WHERE id = '".$_POST['asset']."'") or die(mysql_error());

[elite311]

I added that to the query like you asked and this is the error:

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ah.hoursasset = 'H102' p.pmasset = 'H102' WHERE id = 'H102'' at line 9

 

I think I know the problem from this error as well, just need to figure out how to fix it now. H102 is what I am changing the asset number too from H103 so it can't change the records in ah an p because H102 doesn't exist. Am I reading that correctly?

[elite311]

I managed to get the query to run, looks like I was missing a few commas. When I run this query now though it changes all the records to the new asset number in all the tables not just the record I'm editing, any idea why?

 

	$db->query("UPDATE assets a,assethours ah,pm p
			SET a.asset = '".$_POST['asset']."', 
			a.category = '".$_POST['category']."', 
			a.descrip = '".$_POST['descrip']."', 
			a.year = '".$_POST['year']."', 
			a.make = '".$_POST['make']."', 
			a.model = '".$_POST['model']."', 
			a.serial = '".$_POST['serial']."', 
			ah.hoursasset = '".$_POST['asset']."',
			p.pmasset = '".$_POST['asset']."' 
			WHERE a.asset = ah.hoursasset
			AND a.asset = p.pmasset") or die(mysql_error());

[jazzman1]

Well, b/s you need to filter ah.hoursasset and p.pmasset whit a subquery string to get only 1 unique value.

You can make a simple test, just replace them with some static values and you will see a proper result. 

[xyph]

http://forums.mysql.com/read.php?20,85813,85816#msg-85816

[elite311]

http://forums.mysql.com/read.php?20,85813,85816#msg-85816

 

I manged to get the query to update all of the tables from the one statement, but I'm really having difficulty with the sub query. I have never really used one before and I can't seem to get this to work.

 

I read about how to use these here http://www.roseindia.net/mysql/mysql5/writing-subqueries.shtml and have been trying for a little while now but can seem to figure this out.

 

I changed my query to this:

	$db->query("UPDATE assets a,assethours ah,pm p
			SET a.asset = '".$_POST['asset']."', 
			a.category = '".$_POST['category']."', 
			a.descrip = '".$_POST['descrip']."', 
			a.year = '".$_POST['year']."', 
			a.make = '".$_POST['make']."', 
			a.model = '".$_POST['model']."', 
			a.serial = '".$_POST['serial']."', 
			a.updatedby = '{$_SESSION['username']}',
			a.updateddate = NOW(),
			ah.hoursasset = '".$_POST['asset']."',
			p.pmasset = '".$_POST['asset']."'
			WHERE ah.hoursasset = ANY(SELECT hoursasset FROM assethours)
			AND p.pmasset = ANY(SELECT pmasset FROM pm)")
			or die(mysql_error());

 

And I get this error:

 

You can't specify target table 'a' for update in FROM clause

 

So I kept reading about Subqueries http://dev.mysql.com/doc/refman/5.1/en/subquery-restrictions.html but I'm not understanding how to fix this problem still. I can make it update all the tables however when I do that using my original query it changes all the records in all the tables which I don't want. I just want to be able to edit the asset number in the assets table and it up date the asset numbers in the other 2 tables (asssethours.hoursasset & pm.pmasset) for the asset I'm working with only.

 

If anyone can point me to even a better tutorial it would be much appreciated.

[jazzman1]

My logic is not very clear regarding to your post.

You have  to filter a.asset to be easy.......

Try,

 

WHERE ah.hoursasset = (SELECT asset FROM assets WHERE asset  = $_POST['asset']) AND p.pmasset = (SELECT asset FROM assets WHERE asset  = $_POST['asset'])

 

I don't like this way, b/s a query is too long. Take a time and learn more about sql join statements.....

 

[Barand]

You are joining tables (assets a, assethours ah, pm p) without specifying any join conditions. This joins every row in each table with every row in the other tables (known as a a Cartesian Join). Specify the joins so you only update related records.

 

UPDATE 
    assets a
    INNER JOIN assethours ah ON a.asset = ah.asset
    INNER JOIN pm p ON a.asset = pm.asset
....

[elite311]

Thank you very much!

 

I have been playing with this for while and couldn't get anything to work properly, this worked fantastic! and as a bonus your post really helped me understand joins a little better.

 

Thanks again for the help