SQL - If Else Update Script Statement

Question

I am trying to create an If Else Update Script. This is what I have put together so far:

IF (SELECT [FileType1] 
    FROM Document 
    WHERE ([FileType1] LIKE 'E-mail' OR [FileType1] LIKE 'outlook'))

    UPDATE Document
    SET [FilePath] = [Full Path]

ELSE ((SELECT [FileType1] 
       FROM Document 
       WHERE ([FileType1] NOT LIKE 'E-mail' OR [FileType1] NOT LIKE 'outlook'))

    UPDATE Document
    SET [Path] = [Full Path]

What I am I doing wrong? Basically, if E-mail or outlook is found in one statement, I want the filepath column to equal the full path column.

If non-E-mails are found, I want the path column equal to the full path column.

Possible duplicate of using a conditional update statement in sql — devlin carnate
– devlin carnate, Commented Jun 13, 2016 at 20:10
not sure why you need IF ELSE here; what is preventing you to write conditions on UPDATE — techspider
– techspider, Commented Jun 13, 2016 at 20:11

Siyual · Accepted Answer · 2016-06-13 20:13:29Z

2

You can do this with a CASE statement while defaulting the ELSE condition to itself:

Update  Document
Set     FilePath =  Case When FileType1 In ('E-mail', 'Outlook')
                        Then [Full Path]
                        Else FilePath
                    End,
        [Path]   =  Case When FileType1 Not In ('E-mail', 'Outlook') 
                        Then [Full Path]
                        Else [Path]
                    End

I've also removed the LIKE statements (since you're only using them for equality), and removed the OR statements in preference of an IN and NOT IN.

answered Jun 13, 2016 at 20:13

Siyual

17k8 gold badges47 silver badges60 bronze badges

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

Comments

square_particle · Accepted Answer · 2016-06-14 02:23:25Z

2

Alternatively, you can simplify and shorten Siyual's suggestion to just using [iif], which is relatively new, i.e. introduced with SQL Server 2012.

update d
set FilePath = iif(FileType1 in ('E-Mail', 'Outlook'), FullPath, FilePath),
    Path = iif(FileType1 not in ('E-Mail', 'Outlook'), FullPath, Path)
from Document d;

Hopes this help.

answered Jun 14, 2016 at 2:23

square_particle

5242 silver badges7 bronze badges

1 Comment

Hila DG Over a year ago

IIF in SQL? I was a bit surprised, but then I read in here: msdn.microsoft.com/en-AU/library/…

Collectives™ on Stack Overflow

SQL - If Else Update Script Statement

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related