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.

@DaleK, As I said in note, there is large query written on the top of table variable, can't add it here, so just added simplified version of that query. — MAK, Jun 07 '23 at 04:19
@DaleK, The function is used in other queries with JOINs. And also don't know what all other dependencies it has. — MAK, Jun 07 '23 at 04:38
Must be mussing something here but the outer joins will always join, you may as well just return a static list? — Stu, Jun 07 '23 at 07:28

score 1 · Answer 1 · answered Jun 07 '23 at 13:10

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;

score 0 · Answer 2 · answered Jun 07 '23 at 05:07

0

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).

answered Jun 07 '23 at 05:07

MAK

6,824
25
74
131

You do know a semi-colon is a statement *terminator*? Not a *begininator*. – Dale K Jun 07 '23 at 07:01
2

The previous statement must be terminated before a WITH/CTE. To avoid errors, most folk use ;WITH because we don't know what is before the CTE – Nitika Jun 07 '23 at 07:53
1

@Nitika - some folks do... But that's not it's correct useage, all statements should now be terminated with a srmi-colon. Many folks do this. And in this case, given its a self answer, OP definitely knows what code comes before. – Dale K Jun 07 '23 at 07:57
But In sql this is not mandatory to terminate the statement :) – Nitika Jun 07 '23 at 07:58
1

@Nitika just because it's not mandatory doesn't mean one should ignore best practice. – Dale K Jun 07 '23 at 08:33
You can update the `CTE` directly, as long as it only updates the columns of one table reference from within it. `UPDATE CTE SET ...` – Charlieface Jun 07 '23 at 13:07

score 0 · Answer 3 · answered Jun 16 '23 at 20:47

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.

Update with from clause within user defined function

3 Answers3