Updating Multiple Values in the Same Column

Question

I have a data table called Movie_Stars. I want to update multiple values, but they are all in the same column. Here's what I have:

update movie_stars
set movie_category = 'Family'
where movie_category = 'Drama'
and set movie_category = 'Children'
where movie_category = 'Cartoon'
and set movie_category = 'Teen'
where movie_category = 'Action';

But this generates the error "invalid user.table.column, table.column, or column specification". So what is the right column specification?

Tim Biegeleisen · Accepted Answer · 2016-11-28 02:46:01Z

2

Use a CASE expression:

update movie_stars
set movie_category = case when movie_category = 'Drama'
                          then 'Family'
                          when movie_category = 'Cartoon'
                          then 'Children'
                          when movie_category = 'Action'
                          then 'Teen'
                     end
where movie_category in ('Drama', 'Cartoon', 'Action')

edited Nov 28, 2016 at 2:46

answered Nov 28, 2016 at 0:45

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

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

2 Comments

Tim Over a year ago

sorry, but when I run you code, it says "invalid relational operator".

Tim Biegeleisen Over a year ago

@Tim Sorry, I had a typo in there, please try again. Great first name, by the way!

Collectives™ on Stack Overflow

Updating Multiple Values in the Same Column

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related