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
4
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 anINNER JOIN
or to makee
the first table in theLEFT 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可能是一个数字,所以不要在比较值周围使用引号。
#1
4
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 anINNER JOIN
or to makee
the first table in theLEFT 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可能是一个数字,所以不要在比较值周围使用引号。