Update Statement

• jsshivalik

  SSChasing Mays

  Points: 612

  More actions

  October 27, 2018 at 10:48 am

  #367476

  Hi

   Can the below 2 Update statements be executed with 1 Update statement

  Update [Test] set DTemp = A.Category from [Test1] as A
  where A.Location = [Test].Place 

  Update [Test] set NMSTEMP = 'Y' where Date > DATEADD(m,(Select A.Months from [Test1] as A
  where A.Location = [Test].Place) ,[RDate])

  Thanks

• Eirikur Eiriksson

  SSC Guru

  Points: 182881

  More actions

  October 28, 2018 at 2:31 am

  #2011460

  jsshivalik - Saturday, October 27, 2018 10:48 AM

  Hi

   Can the below 2 Update statements be executed with 1 Update statement

  Update [Test] set DTemp = A.Category from [Test1] as A
  where A.Location = [Test].Place 

  Update [Test] set NMSTEMP = 'Y' where Date > DATEADD(m,(Select A.Months from [Test1] as A
  where A.Location = [Test].Place) ,[RDate])

  Thanks

  Suggest that you post the DDL (create table) scripts, sample data as an insert statement and the desired results, makes it much easier for those answering the questions!
  😎

  To answer your question, most likely this can be done in a single statement, here is an example.
  
UPDATE T
  SET T.DTemp     = T1.Category
     ,T.NMSTEMP   = CASE
                       WHEN T.[Date] > DATEADD(m,T1.Months,T.[RDate]) THEN 'Y'
                       ELSE T.NMSTEMP
                    END
FROM        [Test]    T
INNER JOIN [Test1]   T1
ON    T.[Place]   =  T1.[Location];