Обновление с помощью предложения from в определяемой пользователем функции

Question

Написание функции, которая также должна быть совместима с Babelfish и SQL Server.

Следующая функция имеет оператор обновления с предложением from и joins.

      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

Отлично работает на SQL Server, но в Babelfish возникает ошибка:

«ОБНОВЛЕНИЕ» нельзя использовать внутри функции.

Когда я попробовал использовать следующий синтаксис, он работал в Babelfish, но не для SQL Server.

--Работает в 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;

--Ошибка в SQL-сервере:

Сообщение 8154, уровень 16, состояние 1, строка 52. Таблица «@ListOWeekDays» неоднозначна.

Примечание . Логика оператора обновления в исходном коде отличается, я только что добавил пример кода для понимания проблемы.

Answer 1

Вы не можете вносить изменения внутри функции, только в процедуру.

Вы можете просто выполнить совместное обновление и использоватьOUTPUTпредложение, чтобы получить измененные результаты.

      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 2

Как насчет использования 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;

В конце концов работает нормально в обоих случаях (SQL Server и Babelfish).

Answer 3

Это текущая ошибка в Babelfish: хотя вы не можете изменить реальную таблицу в функции SQL, вы можете делать что угодно с табличной переменной в функции SQL. К сожалению, синтаксисUPDATE t...FROM @tabvarпоскольку в настоящее время Babelfish интерпретирует t как обновление обычной таблицы. Ожидается, что эта ошибка будет исправлена ​​в следующем выпуске v.3.2.0. Обходной путь — сделать прямойUPDATE @tabvar, поэтому не используйте корреляционное имя для UPDATE.