Forum Moderators: open
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.
I think this is safer to insure you don't corrupt any of your data.
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.
