CODEWITHSUNDEEP
mysqlsqlsql-updateforeign-key-relationship
shane
shane

Reputation: 1863

mysql update related row

I have a mysql database with two tables, users and items. I want to update a variable on a row from items (which I can do) with UPDATE and then update a variable on the related row in users.

I am confused on updating using the foreign key, is this possible in one query? How do I go about this?

EDIT

Currently I am doing this in php:

//execute the SQL query and return records
$result = mysqli_query($dbhandle, "UPDATE items SET likes = likes + 1 WHERE id='$_POST[id]'")
  or die("Unable to query");

// update user score
$score = mysqli_query($dbhandle, "UPDATE users u JOIN items i ON u.id = i.user_id SET u.score = u.score + i.likes")
    or die("Unable to update user score");

Every row in items has a foreign key 'user_id', I just want to update the score of the related user based on how many likes the newly updated item now has

Upvotes: 1

Views: 101

Answers (1)

Rahul
Rahul

Reputation: 77876

You can do Join on both the table and perform the UPDATE like (a sample below)

UPDATE users u 
    JOIN items i ON u.some_col = i.some_col
SET u.column1 = i.column1

Also, if you have a foreign key relationship defined between the tables then you can use ON UPDATE CASCADE (You will have to define while creation of the table or perform ALTER and change the schema) which will cascade the update between Main and Derived table.

Upvotes: 1

Related Questions