SQL update/set more than one column

Posted on 2007-07-30
Last Modified: 2010-03-19
this question is related to the question (SQL update logic) that i posted before.

is it possible to setMORE than one column with different values if condition is true?

set table1.ColumnA = 0  and table1.ColumnB = abc1 and table 1.ColumnC  = xyz1
     if table1.columnB=table2.columnX and table1.columnC = table2.columnY
set table1.ColumnA = 1  and table1.ColumnB = abc2 and table 1.ColumnC  = xyz2
Question by:jgordin
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    i guess you are looking for a syntax like this:

    set table1.ColumnA = 0
       , table1.ColumnB = abc1  
       , table 1.ColumnC  = xyz1
       , table1.ColumnA = case when table1.columnB = table2.columnX and table1.columnC = table2.columnY then 1 else  table1.ColumnA end
       , table1.ColumnB = case when table1.columnB = table2.columnX and table1.columnC = table2.columnY then abc2  else   table1.ColumnB  end
       , table1.ColumnC  = case when table1.columnB = table2.columnX and table1.columnC = table2.columnY then xyz2 else   table1.ColumnC  end


    Author Comment

    i am looking for the syntax that will allow me to update multiple columns of the table based on the condition:

    if  t1.columnB=t2.columnX and t1.columnC = t2.columnY = True
      set t1.columnA = 1, t1.columnB = t2.columnZ1
    if  t1.columnB=t3.columnX and t1.columnC = t3.columnY = True
     set t1.columnA = 2, t1.columnB = t2.columnZ2

    LVL 142

    Accepted Solution

    without being more "clear" on the circumstances (ie how are the tables joined etc), it will be impossible to get clearer on the "response" without guessing...

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    This video discusses moving either the default database or any database to a new volume.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now