Welcome to WebmasterWorld Guest from 52.91.39.106

Forum Moderators: open

Message Too Old, No Replies

UPDATE multiple tables with same value

     
1:22 pm on Oct 23, 2009 (gmt 0)

New User

5+ Year Member

joined:Oct 23, 2009
posts: 1
votes: 0


I've tried to solve it myself, but can't find the solution.
This is my problem:
I have some tables and all of them contain one column with te same name. I want to empty rows in these columns when some condition is met.

So I have:
table1: columnA, columnB
table2: columnA, columnC
table3: columnA, columnD

I can do:
UPDATE table1 SET columnA='' WHERE columnA LIKE 'A%'
UPDATE table2 SET columnA='' WHERE columnA LIKE 'A%'
etc

Is there a way to combine the query's?

I've tried
UPDATE table1 t1,table2 t2 SET t1.columnA='', t2.columnA='' WHERE t1.columnA LIKE 'A%' OR t2.columnA LIKE 'A%'

but it doesn't work.
Anybody any suggestions? Thanx.

1:09 pm on Nov 14, 2009 (gmt 0)

Junior Member

10+ Year Member

joined:Feb 4, 2007
posts:73
votes: 0


I have always done it similar to your first method... except I use (in PHP) a While - else statement to verify the first UPDATE (or INSERT) actually happened before I do the next UPDATE.

I think this is safer to insure you don't corrupt any of your data.

4:26 pm on Nov 17, 2009 (gmt 0)

Senior Member from US 

WebmasterWorld Senior Member 10+ Year Member

joined:Nov 11, 2007
posts:774
votes: 3


If you have to update 3 tables and make sure all are updated OR none to avoid corrupt data, doing this in PHP is not the best approach IMO. I would look into using TRANSACTIONS, COMMITs, and ROLLBACKs.

Almost all SQL DBs support transactions... I'm not sure of the MySQL specific syntax (I'm more an Oracle/Microsoft SQL*Server guy)... but you typically create a stored proc that does all of the work. The stored proc uses a transaction to ensure that ALL updates or done. The proc might contain logic something like:

ON ERROR GOTO MyErrorHandler

BEGIN TRANSACTION
UPDATE table1
UPDATE table2
UPDATE table3
COMMIT
RETURN (some success status)

MyErrorHandler:
ROLLBACK
RETURN (return some error status)

With the above logic encapsulated in a stored proc, your PHP code can call the stored proc (and even pass in parameters if need be) and simply check its return value to see if it worked or not. The proc will try to update all 3 tables. If all updates succeed then it will return a value to indicate it succeeded. If any of the updates fail then ALL changes previously made by the proc are 'undone' or rolled back and an error status is returned to the calling PHP.

So if the updates to tables1 & table2 are successful but the update to table3 fails, the proc will jump to the MyErrorHandler label, undo all of the changes it made to table1 and table2 (setting everything back to the way it was before the stored proc call) and return an error status so the calling PHP program knows there was some type of error and the updates failed.

5:50 am on Dec 7, 2009 (gmt 0)

Full Member

10+ Year Member

joined:Apr 21, 2004
posts:306
votes: 0


ZydoSEO has good advice. I believe MySQL only supports transactions if your tables are of type InnoDB.
 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members