Update multiple columns using case

• ramamurthy.raju

  SSC Enthusiast

  Points: 188

  More actions

  October 12, 2017 at 12:28 am

  #324075

  I have a scenario where i want to update multiple columns of first table from multiple rows of second table.
  But the case only updates from first row from second table.

  any help to achive this with out using cursor. below is sample code

  create table Table1 (ColA varchar(20),ColB varchar(20),ColC varchar(20))
  insert into Table1 values(1,null,null)

  create table Table2 (ColA varchar(20),ColB varchar(20),ColValue varchar(20))

  insert into Table2 values(1,2,'X')
  insert into Table2 values(1,3,'Y')

  update Table1
  set ColB=case when T1.ColB=2 then T1.ColValue else T0.ColB end,
  ColC=case when T1.ColB=3 then T1.ColValue else T0.ColC end
  from Table1 T0 INNER JOIN Table2 T1 On T0.ColA=T1.ColA

  select * from Table1

  in the result it has updated only ColB

• Thom A

  SSC Guru

  Points: 99126

  More actions

  October 12, 2017 at 2:08 am

  #1963320

  Because you're trying to pass multiple rows to a single row UPDATE. This is much like doing:
  CREATE TABLE #Sample (string char(1));
INSERT INTO #Sample
VALUES (NULL);
GO
CREATE TABLE #Values (Char1 char(1));
INSERT INTO #Values
VALUES ('A'),('B');
GO
UPDATE #Sample
SET string = char1
FROM #Values;
GO
SELECT *
FROM #Sample;
GO
DROP TABLE #Sample;
DROP TABLE #Values;

  Notice that only the the value is 'A', as that is the first row resolved. You'll need to have your data set return one row PER value that is to be updated. For example:
  UPDATE T1
SET T1.ColB = T2a.ColValue,
    T1.ColC = T2b.ColValue
FROM Table1 T1
     JOIN Table2 T2a ON T1.ColA = T2a.ColA AND T2a.ColB = 2
     JOIN Table2 T2b ON T1.ColA = T2b.ColA AND T2b.ColB = 3;

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• ramamurthy.raju

  SSC Enthusiast

  Points: 188

  More actions

  October 12, 2017 at 4:16 am

  #1963327

  Thank you for your reply. 
  Yes i changed my code like your example. Problem is in real time my second table itself is another big query with so many conditions
  So i used Common Table Expression and it worked.

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  October 12, 2017 at 4:41 am

  #1963328

  ramamurthy.raju - Thursday, October 12, 2017 4:16 AM

  Thank you for your reply. 
  Yes i changed my code like your example. Problem is in real time my second table itself is another big query with so many conditions
  So i used Common Table Expression and it worked.

  Here's a very standard method:
  DROP TABLE #Table1; CREATE TABLE #Table1 (ID INT, ColB varchar(20), ColC varchar(20))
INSERT INTO #Table1 VALUES (1, null, null)

  DROP TABLE #Table2; CREATE TABLE #Table2 (ID INT, ColX SMALLINT, ColValue varchar(20))
  INSERT INTO #Table2 VALUES (1, 2, 'X')
  INSERT INTO #Table2 VALUES (1, 3, 'Y')

  UPDATE T1 SET
   ColB = x.ColBValue,
   ColC = x.ColCValue
  FROM #Table1 T1
  CROSS APPLY ( -- "flatten" Table2
   SELECT
    ColBValue = MAX(CASE WHEN ColX = 2 THEN ColValue ELSE NULL END),
    ColCValue = MAX(CASE WHEN ColX = 3 THEN ColValue ELSE NULL END)
   FROM #Table2 t2
   WHERE t2.ID = t1.ID
  ) x

  SELECT * FROM #Table1
  

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden