获取错误关键字“as”附近的语法不正确。将mysql查询转换为sql时

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

我正在将mysql转换为sql查询，我做了很多更改，以便在sql server中运行查询，但我仍然收到错误关键字'as'附近的语法错误。，有谁可以帮助我为什么我收到此错误？这是我的查询

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';

1 个解决方案

#1

The correct syntax in SQL Server is:

SQL Server中的正确语法是：

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.
• 如果要使用数据库，请了解正确的SQL Server语法。
• Use CASE; it is proper SQL and works in all proper databases. This is especially true if you have multiple conditions.
• 使用CASE;它是适当的SQL并适用于所有适当的数据库。如果您有多个条件，则尤其如此。
• 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.
• 如果您要更新e，那么您需要INNER JOIN或在LEFT JOIN中创建第一个表。更新不匹配的行毫无意义。
• billed_flag is, presumably, a number so don't use quotes around the comparison value.
• billed_flag可能是一个数字，所以不要在比较值周围使用引号。