CODEWITHSUNDEEP
sql-server
cfly24
cfly24

Reputation: 1952

How to include IF ELSE in SQL Query

I am attempting to put an IF ELSE statement into the follow SQL Query, but it keeps breaking when I try to execute. I have never used IF ELSE in SQL so I am assuming I am not including it correctly

Query without IF ELSE

UPDATE RA SET 
CreditInvoiceAmount = CSV.CreditInvoiceAmount, 
CreditInvoiceDate = CSV.CreditInvoiceDate, 
CreditInvoiceNumber = CSV.CreditInvoiceNumber, 
CreditDeniedDate = CSV.CreditDeniedDate, 
CreditDeniedReasonId = CSV.CreditDeniedReasonId, 
CreditDeniedNotes = CSV.CreditDeniedNotes 
FROM ReturnAuthorization RA 
JOIN TemporaryCsvUpload CSV 
on RA.Id = CSV.Id

IF ELSE

IF CSV.CreditInvoiceDate = null AND CSV.CreditDeniedDate != null THEN
StatusId = 7;
ELSE
StatusId = 8;

Insert Attempt

UPDATE RA SET 
CreditInvoiceAmount = CSV.CreditInvoiceAmount, 
CreditInvoiceDate = CSV.CreditInvoiceDate, 
CreditInvoiceNumber = CSV.CreditInvoiceNumber, 
CreditDeniedDate = CSV.CreditDeniedDate, 
CreditDeniedReasonId = CSV.CreditDeniedReasonId, 
CreditDeniedNotes = CSV.CreditDeniedNotes,
IF CSV.CreditInvoiceDate = null AND CSV.CreditDeniedDate != null THEN
StatusId = 7;
ELSE
StatusId = 8; 
FROM ReturnAuthorization RA 
JOIN TemporaryCsvUpload CSV 
on RA.Id = CSV.Id

Any guidance on how to correctly put this if statement into the SQL block would be appreciated. Thanks!

Upvotes: 1

Views: 280

Answers (2)

Bipool
Bipool

Reputation: 727

// You can try this

StatusId = IF ((CSV.CreditInvoiceDate IS NULL AND CSV.CreditDeniedDate IS NOT NULL), 7, 8)

Upvotes: 0

Radu Gheorghiu
Radu Gheorghiu

Reputation: 20489

Use a CASE expression to implement a IF logic based on which you determine what value to assign into a column for a specific row.

UPDATE RA
SET CreditInvoiceAmount = CSV.CreditInvoiceAmount
    ,CreditInvoiceDate = CSV.CreditInvoiceDate
    ,CreditInvoiceNumber = CSV.CreditInvoiceNumber
    ,CreditDeniedDate = CSV.CreditDeniedDate
    ,CreditDeniedReasonId = CSV.CreditDeniedReasonId
    ,CreditDeniedNotes = CSV.CreditDeniedNotes
    ,StatusId = CASE 
                    WHEN CSV.CreditInvoiceDate IS NULL
                        AND CSV.CreditDeniedDate IS NOT NULL
                            THEN 7
                    ELSE 8
                END
FROM ReturnAuthorization RA
INNER JOIN TemporaryCsvUpload CSV ON RA.Id = CSV.Id

CASE is valid for both SQL Server and MySQL, so it should work with either systems. Also, please consider editing your question and leaving only the relevant tag (what RDBMS you're actually using).

Upvotes: 1

Related Questions