CODEWITHSUNDEEP
mysqldatabasesql-update
Nate
Nate

Reputation: 28354

How to set a column value equal to the value in another table?

I am trying to figure out how to update a row in one table, setting a column value equal to a value in a different table. Here's an example:

movies:

movie_id | movie_price

movies_attended:

attended_id | attended_movie_id | attended_movie_price

Now, this is kind of a stupid example, but supposed that for some reason there is a row in movies_attended that does not have the correct attended_movies_price in it and so it needs to be updated.

How should a query be written to update the movies_attended table, setting movies_attended.attended_movie_price = movies.movie_price?

I tried something similar to the following, but it did not work:

update movies_attended, movies 
set movies_attended.attended_movie_price = movies.movie_price 
where movies_attended.attended_movie_id = movies.movie_id 
AND attended_id = [the id of the row we want to update]

Upvotes: 1

Views: 7761

Answers (2)

spencer7593
spencer7593

Reputation: 108370

When you say "it did not work", do you mean that it reported 0 rows updated, or did the statement cause the database raise an exception?

Your example statement appears to be of the form:

UPDATE movies_attended a
  JOIN movies m
    ON a.attended_movie_id = m.movie_id
   SET a.attended_movie_price = m.movie_price
 WHERE a.attended_id = ?

(We typically prefer the JOIN ... ON ... style syntax to the comma join operator and the join predicates in the WHERE clause.)

I have no explanation as to why this statement would "not work".

It's possible this would report 0 rows affected, if no rows satisfy the predicates. It would also report 0 rows affected if the rows that would be changed do not require any changes... that is, the existing value in attended_movie_price already matches the value being assigned to it.

Normally, before running an update statement like that, I write it as a SELECT first, and see what values are returned...

By replacing the UPDATE keyword with SELECT ... FROM, and removing the SET clause: 

SELECT m.movie_price          AS new_val
     , a.attended_movie_price AS old_val
     , a.attended_id
  FROM UPDATE movies_attended a
  JOIN movies m
    ON a.attended_movie_id = m.movie_id
 WHERE a.attended_id = ?

Upvotes: 3

Zvonko Biskup
Zvonko Biskup

Reputation: 393

This is actually a bad database design. You don't need movie price in two tables.

But, if you just need this, it goes something along this:

UPDATE movies_attended
INNER JOIN
movies
ON movies_attended.attended_movie_id = movies.movie_id
SET movies_attended.attended_movie_price = movie.movie_price

Upvotes: 0

Related Questions