SQL : updating a column based on other columns

SQL Server: I have a following table.

ID Type1 Type2 error
_____________________
1   P107  0057   NULL
2   P101  1142   NULL
3   P107  1142   NULL

now here I have to check type1 column in table 1 if data exists, type2 in table2. Lets assume that P101 exists in table1 and 0057 exist in table2. Table1 have many columns but we are checking for col = 'type1' same is true for table2 just checking col = 'type2'. So, error generated should be like:

ID Type1 Type2 error
_____________________
1   P107  0057   <type1 invalid>
2   P101  1142   <type2 invalid>
3   P107  1142   <type1 invalid> + <type2 invalid>

PS: There might be other messages that are already existing in error column, we dont want to delete those messages but to add the new messages if any. For example I might have the table like :

ID Type1 Type2 error
_____________________
1   P107  0057   NULL
2   P101  1142   <duplicate>
3   P107  1142   NULL

now, I want to add the new error in id = 2 like,

ID Type1 Type2 error
_____________________
1   P107  0057   <type1 invalid>
2   P101  1142   <du开发者_运维问答plicate> + <type2 invalid>
3   P107  1142   <type1 invalid> + <type2 invalid>

Any help will be appreciated !! Thanks in advance .

---

If the following produces the results you want:

SELECT t.ID, t.Type1, t.Type2, 
  error = COALESCE(t.error + ' + ', '') + CASE
  WHEN t1.type1 IS NULL AND t2.type2 IS NULL 
    THEN '<type1 invalid> + <type2 invalid>'
  WHEN t1.type1 IS NULL THEN '<type1 invalid>'
  WHEN t2.type2 IS NULL THEN '<type2 invalid>'
  ELSE t.error END
FROM dbo.table AS t
LEFT OUTER JOIN dbo.Table1 AS t1
ON t.Type1 = t1.Type1
LEFT OUTER JOIN dbo.Table2 AS t2
ON t.Type2 = t2.Type2
WHERE t1.Type1 IS NULL OR t2.Type2 IS NULL;

Then you can run this update statement:

UPDATE t
SET error = COALESCE(t.error + ' + ', '') + CASE
  WHEN t1.type1 IS NULL AND t2.type2 IS NULL 
    THEN '<type1 invalid> + <type2 invalid>'
  WHEN t1.type1 IS NULL THEN '<type1 invalid>'
  WHEN t2.type2 IS NULL THEN '<type2 invalid>'
  ELSE t.error END
FROM dbo.table AS t
LEFT OUTER JOIN dbo.Table1 AS t1
ON t.Type1 = t1.Type1
LEFT OUTER JOIN dbo.Table2 AS t2
ON t.Type2 = t2.Type2
WHERE t1.Type1 IS NULL OR t2.Type2 IS NULL;

Added where clause to account for new information added to the question as an afterthought. Assuming that the column is NULL when there isn't yet an error message.

EDIT updated for yet MORE new requirements.

EDIT and again. Is there an echo?

Here's a repro you can run in tempdb to see that, no, you don't need to use variables and no, you don't need to write 10! (yes, I know what factorial means) case expressions to get this done.

Core table:

USE tempdb;
GO

CREATE TABLE dbo.[table]
(
    ID INT,
    Type1 VARCHAR(4),
    Type2 VARCHAR(4),
    Type3 VARCHAR(4),
    Type4 VARCHAR(4),
    Type5 VARCHAR(4),
    Type6 VARCHAR(4),
    Type7 VARCHAR(4),
    Type8 VARCHAR(4),
    Type9 VARCHAR(4),
    Type10 VARCHAR(4),
    error VARCHAR(MAX)
);
GO

Some rows:

INSERT dbo.[table] SELECT -- this will yield type 1 invalid:
 1,'P107','0057','x',   'x','x','x','x',   'x','x','x',NULL
UNION ALL SELECT          -- this will yield type 2 invalid:
 2,'P101','1142','x',   'x','x','x','x',   'x','x','x','<duplicate>'
UNION ALL SELECT          -- this will yield type 1 + type 2 invalid:
 3,'P107','1142','x',   'x','x','x','x',   'x','x','x',NULL
UNION ALL SELECT          -- no problems here:
 4,'x',   'x',   'x205','x','x','x','y676','x','x','x',NULL
UNION ALL SELECT          -- this will yield type 3 invalid:
 5,'x',   'x',   'x206','x','x','x','y676','x','x','x','<other>';

Related tables and rows:

CREATE TABLE dbo.Table1 (Type1  VARCHAR(4));
CREATE TABLE dbo.Table2 (Type2  VARCHAR(4));
CREATE TABLE dbo.Table3 (Type3  VARCHAR(4));
CREATE TABLE dbo.Table4 (Type4  VARCHAR(4));
CREATE TABLE dbo.Table5 (Type5  VARCHAR(4));
CREATE TABLE dbo.Table6 (Type6  VARCHAR(4));
CREATE TABLE dbo.Table7 (Type7  VARCHAR(4));
CREATE TABLE dbo.Table8 (Type8  VARCHAR(4));
CREATE TABLE dbo.Table9 (Type9  VARCHAR(4));
CREATE TABLE dbo.Table10(Type10 VARCHAR(4));

INSERT dbo.Table1 SELECT 'P101';
INSERT dbo.Table2 SELECT '0057';
INSERT dbo.Table3 SELECT 'x205';
INSERT dbo.Table7 SELECT 'y676';

-- I know you won't have x's but I assume you'll have
-- valid data most of the time.

INSERT dbo.Table1  SELECT 'x';
INSERT dbo.Table2  SELECT 'x';
INSERT dbo.Table3  SELECT 'x';
INSERT dbo.Table4  SELECT 'x';
INSERT dbo.Table5  SELECT 'x';
INSERT dbo.Table6  SELECT 'x';
INSERT dbo.Table7  SELECT 'x';
INSERT dbo.Table8  SELECT 'x';
INSERT dbo.Table9  SELECT 'x';
INSERT dbo.Table10 SELECT 'x';

Let's make sure the table looks right:

SELECT * FROM dbo.[table];

Now, a single statement, only 10 case expressions, not 10!:

UPDATE t SET error = REPLACE(COALESCE(t.error, '') 
    + CASE WHEN t1.type1   IS NULL THEN '<type1 invalid>'  ELSE '' END
    + CASE WHEN t2.type2   IS NULL THEN '<type2 invalid>'  ELSE '' END
    + CASE WHEN t3.type3   IS NULL THEN '<type3 invalid>'  ELSE '' END
    + CASE WHEN t4.type4   IS NULL THEN '<type4 invalid>'  ELSE '' END
    + CASE WHEN t5.type5   IS NULL THEN '<type5 invalid>'  ELSE '' END
    + CASE WHEN t6.type6   IS NULL THEN '<type6 invalid>'  ELSE '' END
    + CASE WHEN t7.type7   IS NULL THEN '<type7 invalid>'  ELSE '' END
    + CASE WHEN t8.type8   IS NULL THEN '<type8 invalid>'  ELSE '' END
    + CASE WHEN t9.type9   IS NULL THEN '<type9 invalid>'  ELSE '' END
    + CASE WHEN t10.type10 IS NULL THEN '<type10 invalid>' ELSE '' END, '><', '> + <')
FROM dbo.[table] AS t
LEFT OUTER JOIN dbo.Table1  AS t1  ON t.Type1  = t1.Type1
LEFT OUTER JOIN dbo.Table2  AS t2  ON t.Type2  = t2.Type2
LEFT OUTER JOIN dbo.Table3  AS t3  ON t.Type3  = t3.Type3
LEFT OUTER JOIN dbo.Table4  AS t4  ON t.Type4  = t4.Type4
LEFT OUTER JOIN dbo.Table5  AS t5  ON t.Type5  = t5.Type5
LEFT OUTER JOIN dbo.Table6  AS t6  ON t.Type6  = t6.Type6
LEFT OUTER JOIN dbo.Table7  AS t7  ON t.Type7  = t7.Type7
LEFT OUTER JOIN dbo.Table8  AS t8  ON t.Type8  = t8.Type8
LEFT OUTER JOIN dbo.Table9  AS t9  ON t.Type9  = t9.Type9
LEFT OUTER JOIN dbo.Table10 AS t10 ON t.Type10 = t10.Type10
WHERE t1.Type1   IS NULL
   OR t2.Type2   IS NULL
   OR t3.Type3   IS NULL
   OR t4.Type4   IS NULL
   OR t5.Type5   IS NULL
   OR t6.Type6   IS NULL
   OR t7.Type7   IS NULL
   OR t8.Type8   IS NULL
   OR t9.Type9   IS NULL
   OR t10.Type10 IS NULL;
GO

Once the update has run, let's check and make sure the right rows have been updated:

SELECT * FROM dbo.[table]; 
GO

And cleanup:

DROP TABLE dbo.[table],
    dbo.Table1, dbo.Table2, dbo.Table3, dbo.Table4, dbo.Table5, 
    dbo.Table6, dbo.Table7, dbo.Table8, dbo.Table9, dbo.Table10;

---

UPDATE yt
    SET error = CASE WHEN t1.Type1 IS NULL AND t2.Type2 IS NULL THEN '<type1 invalid> + <type2 invalid>'
                     WHEN t1.Type1 IS NULL THEN '<type1 invalid>'
                     WHEN t2.Type2 IS NULL THEN '<type2 invalid>'
                END
    FROM YourTable yt
        LEFT JOIN table1 t1
            ON yt.Type1 = t1.Type1
        LEFT JOIN table2 t2
            ON yt.Type2 = t2.Type2
    WHERE t1.Type1 IS NULL 
        OR t2.Type2 IS NULL;