CODEWITHSUNDEEP
phpmysqlpdo
Damaged Organic
Damaged Organic

Reputation: 8467

UPDATE on different tables and columns by similar condition

I have to run a procedure which will update an old value with the new value across several tables. All those tables are different and have different column name, and also they could not even have matching $old_value in them (in this case columns should not be updated), so only thing I could think of is to run a transaction, like this:

$new_value = 'something';

$DBH->prepare("SELECT old_value FROM table_0 WHERE id = :id");
$DBH->execute(':id' => $some_value);
$result = $DBH->fetch(PDO::FETCH_ASSOC);

if( $new_value != $result[0]['old_value'] )
{
    $DBH->beginTransaction();

    $DBH->exec("UPDATE table_1 SET column_1 = $new_value WHERE column_1 = $old_value");
    $DBH->exec("UPDATE table_2 SET column_2 = $new_value WHERE column_2 = $old_value");
    $DBH->exec("UPDATE table_2 SET column_3 = $new_value WHERE column_3 = $old_value");

    $DBH->rollBack();
}

Is there any better solution? Maybe way to run UPDATE on SELECT without affecting whole result set?

Upvotes: 0

Views: 50

Answers (2)

Meier
Meier

Reputation: 3890

At the end you need to do

$DBH->commit();

You do $DBH->rollback() only if you got an error. Rollback undos everything you have done since the start of the transaction.

Upvotes: 0

tadman
tadman

Reputation: 211670

You have three different update conditions there, so you basically need three different updates. Enclosing them inside a transaction as you've done is the best way to do that reliably.

Upvotes: 1

Related Questions