Getting error Incorrect syntax near the keyword 'as'. when convert mysql query to sql

Question

I am converting mysql to sql query, i did many changes for that to run query in sql server, but still i am getting error Incorrect syntax near the keyword 'as'., can anyone please help me why i am getting this error ? Here is my query for that

UPDATE tb_EpVisitRange as v 
left JOIN tb_Episode as e 
  ON v.BranchID   = e.BranchID 
 AND v.company_id = e.CustID 
 AND v.CMW        = e.CMW
SET
e.EpVisitCount = IIF(PayerType = 'NonEp', 0, IIF(LUPA = 1, 0, v.High)),
e.VisitAlert = IIF(Status = 'Closed', 0, IIF((IIF(PayerType = 'NonEp', 0, IIF(LUPA = 1, 0, v.High))) > 0, 1, 0))
where  billed_flag = '0';

You're trying to update the table on the left side of the join here. What are you expecting to do if a record isn't found..? — Thom A
– Thom A ♦, Commented Feb 7, 2018 at 13:35
This actually works on MySql? If so, this is one more reason to not work with MySql. The more I learn about it, the more I don't like it. — Zohar Peled
– Zohar Peled, Commented Feb 7, 2018 at 14:40
The update is on v but the set is on e. It has other problems also. Update a left join make zero sense here. If the record does not exists there is nothing to update. — paparazzo
– paparazzo, Commented Feb 7, 2018 at 14:56
Possible duplicate of getting error incorrect syntax near the keyword as in sql server — SMor
– SMor, Commented Feb 7, 2018 at 15:02

Gordon Linoff · Accepted Answer · 2018-02-07 13:37:11Z

The correct syntax in SQL Server is:

UPDATE e
    SET EpVisitCount = (CASE WHEN PayerType = 'NonEp' THEN 0
                             WHEN LUPA = 1 THEN 0
                             ELSE v.High
                        END),
        VisitAlert = (CASE WHEN Status = 'Closed' THEN 0
                           WHEN PayerType = 'NonEp' THEN 0
                           WHEN LUPA = 1 THEN 0
                           WHEN v.High > 0 THEN 1
                           ELSE 0
                      END)
    FROM tb_EpVisitRange v JOIN
         tb_Episode e
         ON v.BranchID = e.BranchID AND v.company_id = e.CustID AND v.CMW = e.CMW
    WHERE billed_flag = 0;

Notes:

Learn proper SQL Server syntax if you are going to use the database.
Use CASE; it is proper SQL and works in all proper databases. This is especially true if you have multiple conditions.
If you are updating e, then you want an INNER JOIN or to make e the first table in the LEFT JOIN. Updating an unmatched row makes no sense.
billed_flag is, presumably, a number so don't use quotes around the comparison value.

Collectives™ on Stack Overflow

Getting error Incorrect syntax near the keyword 'as'. when convert mysql query to sql

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related