CODEWITHSUNDEEP
sql-servert-sqlms-accessiif
SikRikDaRula
SikRikDaRula

Reputation: 133

Convert IIF statement from access to T-SQL SQL Server(Pass-Through)

I have an update query that I created it in Access 2013. I have a table with over 1.5 million records, I am trying to run an update query but it takes forever sometimes especially if I am updating thousands of rows. So I figured out that I would use pass through option in access but when I try to do that I get a syntax error in my query as the syntax is different in access than it is in SQL Server. How could I convert my query to sql server so my queries won't take forever to run.

Here is my original access query that I want to convert it to T-SQL.

UPDATE CLAIM 
   INNER JOIN 06 ON [06].ID = Claim.ID  
   SET reason_code_01= IIF(reason_code_01 is null,”06”,reason_code_01),        reason_code_02= IIF(reason_code_01 <> null,”06”,reason_code_02), reason_code_03=        IIF(reason_code_01 <> null,”06”,reason_code_03), reason_code_04=        IIF(reason_code_01 <> null,”06”,reason_code_04),overpaid_deduc=        IIF(overpaid_deduc is null,[06].[DED AMT],overpaid_deduc), overpaid_deduc2=        IIF(overpaid_deduc <>null,[06].[DED AMT],overpaid_deduc2),overpaid_deduc3=        IIF(overpaid_deduc <> null,[06].[DED AMT],overpaid_deduc3),overpaid_pay1=        IIF(overpaid_pay1 is null,[06].[PAY 1],overpaid_pay1),overpaid_pay2=        IIF(overpaid_pay1 <> null,[06].[PAY 1],overpaid_pay2),overpaid_pay3=        IIF(overpaid_pay1 <> null,[06].[PAY 1],overpaid_pay3)
WHERE ((([06].sort_order)=1));

Upvotes: 0

Views: 204

Answers (1)

sgeddes
sgeddes

Reputation: 62851

Your syntax is off a bit for updates with joins. And you need to use a case statement instead of iif. Here is a condensed version which should help:

UPDATE c
SET reason_code_01= 
    case when reason_code_01 is null 
         then '06' 
         else reason_code_01
    end, 
    ...
    overpaid_pay3=
    case when overpaid_pay1 is not null 
         then [06].[PAY 1] 
         else overpaid_pay3
    end
FROM CLAIM c 
    INNER JOIN 06 ON [06].ID = c.ID  
WHERE [06].sort_order=1

Upvotes: 3

Related Questions