SQL UPDATE using an IF statement

Question

I have a table that has a unique identifier of "name" and two fields that are both Boolean statements. The two fields are titled "sentbirth" and "senthire".

What I am trying to do is to see if the value is already true for senthire or sentbirth. If so I want to leave it as true. But if it is false then I want it to update with the @SentBirth or @SentHire value that is being produced from my web application.

What I have so far is:

SELECT [name],[sentbirth],[senthire]
FROM [CCRAuction].[dbo].[cardsUser]
WHERE name = @Name

IF [sentbirth] = '1'
    BEGIN
    UPDATE [CCRAuction].[dbo].[cardsUser]
        SET [sentbirth] = '1'
    END 
 ELSE
    BEGIN
     UPDATE [CCRAuction].[dbo].[cardsUser]
        SET [sentbirth] = @SentBirth
    END

IF [senthire] = '1'
    BEGIN
    UPDATE [CCRAuction].[dbo].[cardsUser]
        SET [senthire] = '1'
    END
 ELSE
    BEGIN
     UPDATE [CCRAuction].[dbo].[cardsUser]
        SET [senthire] = @SentHire
    END

With this code as is I am receiving the error message that 'sentbirth' and 'senthire' are invalid column names.

How do I write this code properly in Microsoft SQL Server Management Studio?

Answer to this question is:

UPDATE cu SET
  [sentbirth] = CASE WHEN cu.[sentbirth] = '1' THEN cu.[sentbirth] ELSE @SentBirth END,
  [senthire] = CASE WHEN cu.[senthire] = '1' THEN cu.[senthire] ELSE @SentHire END
FROM [CCRAuction].[dbo].[cardsUser] cu
WHERE cu.name = @name

Possible duplicate of using a conditional update statement in sql — Tab Alleman
– Tab Alleman, Commented Feb 29, 2016 at 18:18

IVNSTN · Accepted Answer · 2016-02-29 18:16:05Z

3

check and keep old or update with new, same for other fields:

UPDATE cu SET
  [sentbirth] = CASE WHEN cu.[sentbirth] = '1' THEN cu.[sentbirth] ELSE @sentbirth END,
  ...
FROM [CCRAuction].[dbo].[cardsUser] cu
WHERE cu.name = @name

answered Feb 29, 2016 at 18:16

IVNSTN

9,3896 gold badges24 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dominick Over a year ago

I tried your code as:

UPDATE cu SET   [sentbirth] = CASE WHEN cu.[sentbirth] = '1' THEN cu.[sentbirth] ELSE @SentBirth END,   [senthire] = CASE WHEN cu.[senthire] = '1' THEN cu.[senthire] ELSE @SentHire END, FROM [CCRAuction].[dbo].[cardsUser] cu WHERE cu.name = @name

But I received the error "Incorrect syntax near the keyword 'FROM'

IVNSTN Over a year ago

@Dominick, remove last comma before FROM

Collectives™ on Stack Overflow

SQL UPDATE using an IF statement

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related