maltman maltman - 28 days ago 10
SQL Question

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.

Answer

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.