SQL multiple table update issue

Question

I'm trying to update two tables at the same time. The action completes successfully.However, it updates all records in the the table instead of the ones specified. I have tried the suggestions here, but to no avail. Any ideas what the issue might be? My code looks like below.

$sql = "UPDATE $receiverTable, $currUserTable 
SET $currUserTable.originator = '$curr_username', 
$receiverTable.originator = '$curr_username', 
$currUserTable.status = '$currValue', 
$receiverTable.status = '$currValue' 
WHERE ($receiverTable.username = '$curr_username') 
OR ($currUserTable.username = '$curr_username')";

Thats a weird requirement, why not wrap two updates inside a single transaction ? — Gus
– Gus, Commented Aug 12, 2019 at 10:01

Nick · Accepted Answer · 2019-08-12 10:10:26Z

2

You have no condition on your JOIN (you really shouldn't be writing JOINs with commas any more) and so every row gets JOINed to every other row, which means that they all have a JOIN to a row in which one of the WHERE conditions is true, hence they all get updated. Rewrite your JOIN with the appropriate condition and the problem will go away. Something like:

UPDATE $receiverTable
JOIN $currUserTable ON $currUserTable.somecolumn = $receiverTable.somecolumn
SET $currUserTable.originator = '$curr_username', 
$receiverTable.originator = '$curr_username', 
$currUserTable.status = '$currValue', 
$receiverTable.status = '$currValue' 
WHERE ($receiverTable.username = '$curr_username') 
OR ($currUserTable.username = '$curr_username')

If there is no way that the tables can be JOINed, you will need to write the UPDATE as two separate queries.

edited Aug 12, 2019 at 10:10

answered Aug 12, 2019 at 9:59

Nick

147k23 gold badges67 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sleek Geek Over a year ago

Thank you very much, that did the trick. I was trying to do it without the JOIN!

Gus · Accepted Answer · 2019-08-12 10:25:26Z

2

Instead of doing this with a single update use a transaction to wrap two update commands, something like this.

BEGIN TRANSACTIONS;

UPDATE TABLE1
SET Col1 = 'SomeValue'
WHERE Cond1 = 'SomeCond';

UPDATE TABLE2
SET Col2 = 'SomeValue'
WHERE Cond2 = 'SomeCond';

COMMIT;

UPDATE

Following this I believe with phpi it would look like:

mysqli_autocommit($dbConnection, false);

$query1 = " UPDATE $receiverTable set originator = '$curr_username', 
status = '$currValue' WHERE username = '$curr_username' "
$query2 = " UPDATE $currUserTable set originator = '$curr_username', 
status = '$currValue' WHERE username = '$curr_username' "

mysqli_query($dbConnection, $query1); 
mysqli_query($dbConnection, $query2);

mysqli_commit($dbConnection);

edited Aug 12, 2019 at 10:25

answered Aug 12, 2019 at 10:03

Gus

9549 silver badges33 bronze badges

Collectives™ on Stack Overflow

SQL multiple table update issue

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related