Update with from clause within user defined function

Question

Writting function which should compatibile with Babelfish and SQL Server as well.

The following function is having update statement with from and joins clause.

CREATE FUNCTION fn_update_from_test()
RETURNS @ListOWeekDays TABLE
(
    DyNumber INT,
    DayAbb VARCHAR(40), 
    WeekName VARCHAR(40)
) 
AS BEGIN 

    INSERT INTO @ListOWeekDays
    VALUES 
    (1,'Mon','Monday')  ,
    (2,'Tue','Tuesday') ,
    (3,'Wed','Wednesday') ,
    (4,'Thu','Thursday'),
    (5,'Fri','Friday'),
    (6,'Sat','Saturday'),
    (7,'Sun','Sunday')  

    UPDATE  lwd
    SET DayAbb = COALESCE( lwd1.DayAbb, lwd2.DayAbb ) + '--',
        WeekName = COALESCE( lwd3.WeekName, lwd2.WeekName ) + '-^-'
    FROM @ListOWeekDays lwd
    LEFT JOIN @ListOWeekDays lwd1 ON lwd1.DyNumber = lwd.DyNumber
    LEFT JOIN @ListOWeekDays lwd2 ON lwd2.DyNumber = lwd.DyNumber
    LEFT JOIN @ListOWeekDays lwd3 ON lwd3.DyNumber = lwd.DyNumber;

RETURN;

END
GO

Works fine in SQL Server but getting an error in Babelfish:

'UPDATE' cannot be used within a function

When I tried it with following syntax it works in Babelfish but not for SQL Server.

--Works in Babelfish

UPDATE  ListOWeekDays
SET DayAbb = COALESCE( lwd1.DayAbb, lwd2.DayAbb ) + '--',
    WeekName = COALESCE( lwd3.WeekName, lwd2.WeekName ) + '-^-'
FROM @ListOWeekDays lwd
LEFT JOIN @ListOWeekDays lwd1 ON lwd1.DyNumber = lwd.DyNumber
LEFT JOIN @ListOWeekDays lwd2 ON lwd2.DyNumber = lwd.DyNumber
LEFT JOIN @ListOWeekDays lwd3 ON lwd3.DyNumber = lwd.DyNumber;

--Error in SQL Server:

Msg 8154, Level 16, State 1, Line 52 The table '@ListOWeekDays' is ambiguous.

Note: The logic of update statement is different in original code, I have just added the sample code for understanding of issue.

Answer 1

This is a current bug in Babelfish: while you cannot modify an actual table in a SQL function, you can do anything to a table variable in a SQL function. Unfortunately, the syntax UPDATE t...FROM @tabvar as t is currently interpreted by Babelfish as that a normal table is updated. This bug is expected to be fixed in the next release, which is v.3.2.0. The workaround is to do a direct UPDATE @tabvar, so not using the correlation name for the UPDATE.

Answer 2

You can't make modifications inside a function, only in a procedure.

You can just do a joined update, and use an OUTPUT clause to get the changed results.

CREATE PROCEDURE update_from_test
-- parameters here
AS

SET NOCOUNT, XACT_ABORT ON;

UPDATE  lwd
SET
  DayAbb = COALESCE( lwd1.DayAbb, lwd2.DayAbb ) + '--',
  WeekName = COALESCE( lwd3.WeekName, lwd2.WeekName ) + '-^-'
OUTPUT
  inserted.DyNumber,
  inserted.DayAbb,
  inserted.WeekName
FROM ListOWeekDays lwd
LEFT JOIN ListOWeekDays lwd1 ON lwd1.DyNumber = lwd.DyNumber
LEFT JOIN ListOWeekDays lwd2 ON lwd2.DyNumber = lwd.DyNumber
LEFT JOIN ListOWeekDays lwd3 ON lwd3.DyNumber = lwd.DyNumber;

Answer 3

How about using CTE.

;WITH CTE AS
(
    SELECT lwd.DyNumber dn,lwd1.DayAbb as da1, lwd2.DayAbb da2, lwd3.WeekName wn1, lwd2.WeekName wn2
    FROM @ListOWeekDays lwd
    LEFT JOIN @ListOWeekDays lwd1 ON lwd1.DyNumber = lwd.DyNumber
    LEFT JOIN @ListOWeekDays lwd2 ON lwd2.DyNumber = lwd.DyNumber
    LEFT JOIN @ListOWeekDays lwd3 ON lwd3.DyNumber = lwd.DyNumber
)
UPDATE @ListOWeekDays
SET     DayAbb          = COALESCE( cte.da1, CTE.da2 ) + '--'
    ,   WeekName    = COALESCE( CTE.wn1, CTE.wn2 ) + '-^-'
FROM CTE 
WHERE DyNumber = cte.dn;

Eventually works fine in both (SQL Server and Babelfish).