Update a table column from a join with another table

• fafful

  SSC Eights!

  Points: 810

  More actions

  January 19, 2019 at 7:34 am

  #382599

  I would like a query to update Table:DutyA Action column with word "create"
  if when joined to Table:DutyB on columns EmpID, DutyLev1 and DutyLevel2 and record 
  are not found in Table:DutyB but when exists update column with word "update"
  Below are 2 tables.
  =======
  Table:DutyA
  EmpID    DutyLev1    DutyLevel2    Action
  1        Task1        Task2    
  2        Task11        Task22
  3        Task111        Task222
  4        Task1111       Task2222
  5        Task11111     Task22222    
  6        Task111111    Task222222
  7        Task111555    Task222555
  8        Task111166    Task222266

  Table:DutyB
  EmpID    DutyLev1    DutyLevel2
  1        Task1            Task2    
  3        Task111        Task222
  4        Task1111       Task2222

• Jonathan AC Roberts

  SSCoach

  Points: 18263

  More actions

  January 20, 2019 at 6:30 pm

  #2019488

  UPDATE A
   SET A.Action = IIF(B.EmpId IS NULL,'create', 'update')
  FROM dbo.DutyA A
  LEFT JOIN dbo.DutyB
         ON B.EmpId = A.EmpId
        AND B.DutyLev1 = A. DutyLev1
        AND B.DutyLev2 = A. DutyLev2
  Or it might be better to write it as two separate statements:
  UPDATE A
   SET A.Action = 'update'
  FROM dbo.DutyA A
 WHERE EXISTS(SELECT *
                FROM dbo.DutyB B
               WHERE B.EmpId = A.EmpId
                 AND B.DutyLev1 = A.DutyLev1
                 AND B.DutyLev2 = A.DutyLev2);

  UPDATE A
   SET A.Action = 'create'
  FROM dbo.DutyA A
 WHERE NOT EXISTS(SELECT *
                    FROM dbo.DutyB B
                   WHERE B.EmpId = A.EmpId
                     AND B.DutyLev1 = A.DutyLev1
                     AND B.DutyLev2 = A.DutyLev2);