在sql server中获取关键字附近的错误语法错误

I am converting mysql query to sql query I have the below query, when i run this query in sql server it says

我正在将mysql查询转换为sql查询我有以下查询，当我在sql server中运行此查询时说

Error 156: Incorrect syntax near the keyword 'as'.

Can anyone please help why i am getting this error ?

任何人都可以帮助我为什么会收到此错误？

Mysql Query(This is working fine in mysql):

Mysql Query（这在mysql中运行正常）：

UPDATE  tb_Episode as e left JOIN tb_Payer as p ON (e.CustID = p.company_id) AND (e.PayorType = p.payor_type)
                        left JOIN tb_HHPPS as h ON e.HHPPS = h.HHPPS
                        SET
                                e.PayerType = If(p.payer_type=1,"Ep","NonEp"),

                                e.LUPAAlert = If((p.payer_type)<>"1",0,If(EpEnd<=Now(),0,If(TotVisits<5,1,0))),
                                e.LUPADays = If((If((p.payer_type)<>"1",0,If(EpEnd<=Now(),0,If(TotVisits<5,1,0))))=0,0,EpEnd-Now()),
                                e.FinalAlert = If((p.payer_type)<>"1",0,If(abs(DATEDIFF(Now(),EpEnd))>1,0,1)),
                                e.FinalDays = If((If((p.payer_type)<>"1",0,If(abs(DATEDIFF(Now(),EpEnd))>1,0,1)))=1,abs(DATEDIFF(Now(),EpEnd)),0),
                                e.RAPAlert = If(p.payer_type="1",If(abs(DATEDIFF(Now(),EpStart))>0,1,0),0),
                                e.RAPDays = If((If(p.payer_type="1",If(abs(DATEDIFF(Now(),EpStart))>0,1,0),0))=1,abs(DATEDIFF(Now(),EpStart)),0)

                        where  e.billed_flag = "0"

SQLQuery (Getting error):

SQLQuery（获取错误）：

UPDATE  tb_Episode as e left JOIN tb_Payer as p ON (e.CustID = p.company_id) AND (e.PayorType = p.payor_type)
                        left JOIN tb_HHPPS as h ON e.HHPPS = h.HHPPS
                        SET
                                e.PayerType = IIF(p.payer_type=1,'Ep','NonEp'),

                                e.LUPAAlert = IIF((p.payer_type)<>"1",0,IIF(EpEnd<=getdate(),0,IIF(TotVisits<5,1,0))),
                                e.LUPADays = IIF((IIF((p.payer_type)<>"1",0,IIF(EpEnd<=getdate(),0,IIF(TotVisits<5,1,0))))=0,0,EpEnd-getdate()),
                                e.FinalAlert = IIF((p.payer_type)<>"1",0,IIF(abs(DATEDIFF(millisecond,getdate(),EpEnd))>1,0,1)),
                                e.FinalDays = IIF((IIF((p.payer_type)<>"1",0,IIF(abs(DATEDIFF(millisecond,getdate(),EpEnd))>1,0,1)))=1,abs(DATEDIFF(millisecond,getdate(),EpEnd)),0),
                                e.RAPAlert = IIF(p.payer_type="1",IIF(abs(DATEDIFF(millisecond,getdate(),EpStart))>0,1,0),0),
                                e.RAPDays = IIF((IIF(p.payer_type="1",IIF(abs(DATEDIFF(millisecond,getdate(),EpStart))>0,1,0),0))=1,abs(DATEDIFF(millisecond,getdate(),EpStart)),0)

                        where  e.billed_flag = '0'

2 个解决方案

#1

This should work. SQL Server doesn't use the syntax of UPDATE [TABLE] AS [Alias] it requires the FROM clause still.

这应该工作。 SQL Server不使用UPDATE [TABLE] AS [Alias]的语法，它仍然需要FROM子句。

UPDATE e 
SET e.PayerType = IIF(p.payer_type=1,'Ep','NonEp'),
    e.LUPAAlert = IIF((p.payer_type)<>'1',0,IIF(EpEnd<=getdate(),0,IIF(TotVisits<5,1,0))),
    e.LUPADays = IIF((IIF((p.payer_type)<>'1',0,IIF(EpEnd<=getdate(),0,IIF(TotVisits<5,1,0))))=0,0,EpEnd - CAST(getdate() as date)),
    e.FinalAlert = IIF((p.payer_type)<>'1',0,IIF(abs(DATEDIFF(millisecond,getdate(),EpEnd))>1,0,1)),
    e.FinalDays = IIF((IIF((p.payer_type)<>'1',0,IIF(abs(DATEDIFF(millisecond,getdate(),EpEnd))>1,0,1)))=1,abs(DATEDIFF(millisecond,getdate(),EpEnd)),0),
    e.RAPAlert = IIF(p.payer_type='1',IIF(abs(DATEDIFF(millisecond,getdate(),EpStart))>0,1,0),0),
    e.RAPDays = IIF((IIF(p.payer_type='1',IIF(abs(DATEDIFF(millisecond,getdate(),EpStart))>0,1,0),0))=1,abs(DATEDIFF(millisecond,getdate(),EpStart)),0)
FROM tb_Episode as e
LEFT JOIN tb_Payer as p ON (e.CustID = p.company_id) AND (e.PayorType = p.payor_type)
LEFT JOIN tb_HHPPS as h ON e.HHPPS = h.HHPPS
WHERE e.billed_flag = '0'

#2

It'll probably work if you move the JOIN statements like this:

如果您像这样移动JOIN语句，它可能会起作用：

UPDATE [e]
    SET 
        [e].[PayerType] = IIF([p].[payer_type] = 1, 'Ep', 'NonEp')
      , [e].[LUPAAlert] = IIF(([p].[payer_type]) <> "1", 0, IIF([EpEnd] <= GETDATE(), 0, IIF([TotVisits] < 5, 1, 0)))
      , [e].[LUPADays] = IIF((IIF(([p].[payer_type]) <> "1", 0, IIF([EpEnd] <= GETDATE(), 0, IIF([TotVisits] < 5, 1, 0)))) = 0, 0, [EpEnd] - GETDATE())
      , [e].[FinalAlert] = IIF(([p].[payer_type]) <> "1", 0, IIF(ABS(DATEDIFF([millisecond], GETDATE(), [EpEnd])) > 1, 0, 1))
      , [e].[FinalDays] = IIF((IIF(([p].[payer_type]) <> "1", 0, IIF(ABS(DATEDIFF([millisecond], GETDATE(), [EpEnd])) > 1, 0, 1))) = 1, ABS(DATEDIFF([millisecond], GETDATE(), [EpEnd])), 0)
      , [e].[RAPAlert] = IIF([p].[payer_type] = "1", IIF(ABS(DATEDIFF([millisecond], GETDATE(), [EpStart])) > 0, 1, 0), 0)
      , [e].[RAPDays] = IIF((IIF([p].[payer_type] = "1", IIF(ABS(DATEDIFF([millisecond], GETDATE(), [EpStart])) > 0, 1, 0), 0)) = 1, ABS(DATEDIFF([millisecond], GETDATE(), [EpStart])), 0)
FROM [tb_Episode] AS [e]
LEFT JOIN [tb_Payer] AS [p]
     ON([e].[CustID] = [p].[company_id])
       AND ([e].[PayorType] = [p].[payor_type])
LEFT JOIN [tb_HHPPS] AS [h]
     ON [e].[HHPPS] = [h].[HHPPS]
WHERE 
    [e].[billed_flag] = '0';

However, I wasn't able to test this statement due to the lack of table definitions and the statement's complexity.

但是，由于缺少表定义和语句的复杂性，我无法测试此语句。

#1