CODEWITHSUNDEEP
sql
maltman
maltman

Reputation: 454

Update multiple records with multiple conditions

I am trying to update several records in my SQL table. My data looks like this

Name
Record1_Name
Record1_Name_something
Record2_Data_Name
Record2_Name_something

The results need to be

Name
Record1
Record2
Record2_Data

Here is the code that I have so far

UPDATE Table1
SET Column1 = 
CASE 1
WHEN '%Record1%' THEN 'Record1'
WHEN '%Record2%' AND '%Data%' THEN 'Record2_Data'
WHEN '%Record2%' AND NOT '%Data%' Then 'Record2'

The first when works with no issue. The second I add the AND, though, it fails. I know the syntax is off on the last one but that is something I will need to try as well.

Upvotes: 0

Views: 51

Answers (2)

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

I think you are looking for something this:

UPDATE Table1
SET Column1 = CASE 
                 WHEN Column1 LIKE '%Record1%' THEN 'Record1'
                 WHEN Column1 LIKE '%Record2%' AND Column1 LIKE '%Data%' THEN 'Record2_Data'
                 WHEN Column1 LIKE '%Record2%' AND Column1 NOT LIKE '%Data%' Then 'Record2'
                 ELSE 'Record3'
              END

Note: You need to specify a value that is returned in case none of the case boolean expressions is satisfied. Otherwise, CASE returns NULL.

Upvotes: 3

Aron
Aron

Reputation: 775

I think your case syntax is off -- https://dba.stackexchange.com/questions/154721/difference-in-results-between-case-syntax

this should work 

 `UPDATE Table1

SET Column1 = CASE WHEN NAME LIKE '%Record1%' THEN 'Record1' WHEN NAME LIKE '%Record2%'
            AND NAME LIKE '%Data%' THEN 'Record2_Data' WHEN NAME LIKE '%Record2%'
            AND NAME NOT LIKE '%Data%' THEN 'Record2' end`

Upvotes: 1

Related Questions