CODEWITHSUNDEEP
sql-servert-sql
Josetta Caudill
Josetta Caudill

Reputation: 56

Set SQL Variable with a series of IF statements

I have a table which compares certain columns to columns in another table. If a column has changed, I want to ultimately change a "Notes" field to alert what has changed. More than one column could've changed.

In a nutshell, I declare the variable and I want to update that variable with whatever has changed and then Update a column with the variable.

In Access, I can do this quite easily, but I've spent two days trying to accomplish this in SQL to no avail.

Declare @NewUpdate nvarchar(max)

If B.RCustNmbr <> A.RSQ_Custnmbr Set @NewUpdate = 'CustNmbr changed from ' & A.RSQ_Custnmbr & ' to ' & B.RCustNmbr & ', '

If B.RCustName <> A.RSQ_CUSTNAME Set @NewUpdate = **@NewUpdate** & 'CustName changed from ' & A.RSQ_CUSTNAME & ' to ' & B.RCustName & ', ' 

Update A
Set A.[Notes] = @NewUpdate & " on today's date " & A.[Notes] *(to include notes that might've been there before)*
From [Table1] A Inner Join  [Table2] B on A.ID = B.ID

Then I would want to Reset the @NewUpdate variable for the next ID (so Set @NewUpdate = Null)

Can you point me in the right direction to get this accomplished? I'm really unsure how I should reference my A & B tables in the If statements. I've tried a number of things. This is the closest:

Declare @NewUpdate nvarchar(max)

Set @NewUpdate = (Select 'CustName changed from ' + A.RSQ_CustName + ' to ' + B.RCustName + ', '
From [Customer Master List] A INNER JOIN [Customer Master List Changes TEMP] B ON A.ID = B.ID
Where **A.ID = 566** and B.RCustName <> A.RSQ_CustName)

(the above query formatted):

Declare @NewUpdate nvarchar(max)

Set @NewUpdate = 
   (Select 'CustName changed from ' + A.RSQ_CustName + ' to ' + B.RCustName + ', '
      From [Customer Master List] A
INNER JOIN [Customer Master List Changes TEMP] B ON A.ID = B.ID
Where A.ID = 566 and B.RCustName <> A.RSQ_CustName)

But I have to limit it to 1 ID or I get the error Subquery returned more than 1 value. This is not permitted when the subquery follows =, etc.... and I need to do this for thousands of records.

Upvotes: 0

Views: 111

Answers (2)

vr8ce
vr8ce

Reputation: 544

Assuming the two tables have a common key column, you can use a query like this to identify the changes. You'll have to use CASTs if the columns aren't character-based, but this gives you the basic idea.

SELECT d.*
  FROM (SELECT a.key,
               CASE WHEN a.col1<>b.col1
                 THEN 'col1 changed from ' + a.col1 + ' to ' + b.col1 + ','
                 ELSE ''
               END 
               + CASE WHEN a.col2<>b.col2
                   THEN 'col2 changed from ' + a.col2 + ' to ' + b.col2 + ','
                   ELSE ''
                 END AS diff
          FROM a INNER JOIN b ON a.key = b.key) d
 WHERE d.diff <> ''

You can then use the above as its own subquery to join to the table with the note column in it; it sounded like the note column was in one of the original tables.

UPDATE a
   SET a.note = n.diff
  FROM a INNER JOIN (<above select>) n ON a.key = n.key

Upvotes: 1

neer
neer

Reputation: 4082

Use UPDATE FROM

UPDATE Table
SET Notes = R.Notes
   From
(
    Select 
         A.TableId
        'CustName changed from ' + A.RSQ_CustName + ' to ' + B.RCustName + ', ' AS Notes
    From 
        [Customer Master List] A INNER JOIN 
        [Customer Master List Changes TEMP] B ON A.ID = B.ID
    Where 
        A.ID = 566 and 
        B.RCustName <> A.RSQ_CustName
) R
WHERE
    Table.Id = R.TableId

Upvotes: 0

Related Questions