Conditional update of SQL Server table column

Question

I have to update a table (say table name is Venues in SQL Server conditionally i.e. if a column (say column name is Venue_Name) in Venues CONTAINS sub-string 'C_' then SET a column (say column name is Venue_Type) in Venues = Conference.

I have tried this query as:

IF Venue_Name CONTAINS 'C_'
    UPDATE Venues SET Venue_Type = 'Conference'
ELSE IF Venue_Name CONTAINS 'J_'
    UPDATE Venues SET Venue_Type = 'Journal'
ELSE
    UPDATE Venues SET Venue_Type = 'Other'

But found error at using the keyword CONTAINS.

Felix Pamittan · Accepted Answer · 2016-05-10 06:24:28Z

2

What you need is a CASE expression:

UPDATE Venues
    SET Venue_Type = CASE 
                        WHEN Venue_Name LIKE '%C[_]%' THEN 'Conference'
                        WHEN Venue_Name LIKE '%J[_]%' THEN 'Journal'
                        ELSE 'Other'
                     END

edited May 10, 2016 at 6:24

answered May 10, 2016 at 5:55

Felix Pamittan

31.9k7 gold badges43 silver badges71 bronze badges

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

1 Comment

user4622594 Over a year ago

I think you forgot to use a ESCAPE Character, the underscore will work as wildcard for any character in LIKE. It should look like this for example: Venue_Name LIKE '%C#_%' ESCAPE '#'

Collectives™ on Stack Overflow

Conditional update of SQL Server table column

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related