Updating with CASE & sub query

Viewed 30

I'm working on the validation Script right now, is there a way to update it like what I code below?

UPDATE Table1
SET ErrorMessage += 
CASE WHEN ( 
        SELECT LOWER(B.CountryCode ) Table1 
        LEFT JOIN (SELECT Table2.CountryCode Value FROM Table2) B ON Table1.CountryCode = B.CountryCode
        WHERE UserId = 'id'
        ) <> 'USA' THEN 'Country Code is not registred' END
WHERE UserId = 'id' 
AND Type IN ('Data')

right now it's returning

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

because I want to make it like

UPDATE Table1
SET ErrorMessage += 
CASE WHEN ( 
          SELECT LOWER(B.CountryCode ) Table1 
          LEFT JOIN (SELECT Table2.CountryCode Value FROM Table2) B ON Table1.CountryCode = B.CountryCode
          WHERE UserId = 'id'
        ) <> 'USA' THEN 'Country Code is not registred' END
+
CASE WHEN 
       (  
          SELECT LEN(Code)
       ) <> 8 THEN ' Code must be 8 digits' ELSE '' END
WHERE UserId = 'id' 
AND Type IN ('Data')

But the len is working but is working

example result enter image description here

0 Answers
Related