3

I have a problem with an "upsert" trigger, being triggered by a SSIS package with fastload. (FIRE_TRIGGERS is enabled)

CREATE TRIGGER [dbo].[MP_VOL_UPSERT] 
   ON  [dbo].[vwMP_VOL] 
   INSTEAD OF INSERT
AS 
BEGIN
    SET NOCOUNT ON;

UPDATE [dbo].[vwMP_VOL]
   SET
    [vwMP_VOL].[id_batch] = inserted.id_batch
      ,[vwMP_VOL].[id_invoice] = inserted.id_invoice
      ,[vwMP_VOL].[text_vchr] = inserted.text_vchr
      ,[vwMP_VOL].[amt_frt_tcur] = inserted.amt_frt_tcur
      ,[vwMP_VOL].[amt_line_hcur] = inserted.amt_line_hcur
      ,[vwMP_VOL].[line_total_hcur] = inserted.line_total_hcur
      ,[vwMP_VOL].[id_tax_1] = inserted.id_tax_1
      ,[vwMP_VOL].[id_tax_2] = inserted.id_tax_2
      ,[vwMP_VOL].[id_tax_3] = inserted.id_tax_3
      ,[vwMP_VOL].[id_tax_4] = inserted.id_tax_4
      ,[vwMP_VOL].[line_total_rem_hcu] = inserted.line_total_rem_hcu
      ,[vwMP_VOL].[id_po] = inserted.id_po
      ,[vwMP_VOL].[cnt_rlse] = inserted.cnt_rlse
      ,[vwMP_VOL].[cnt_line_nbr] = inserted.cnt_line_nbr
FROM inserted 
INNER JOIN (SELECT [Org]
      ,[id_vend]
      ,[id_org_orig]
      ,[id_vchr_nbr]
      ,[id_vchr_line]
  FROM [vwMP_VOL]) AS old
ON old.[Org] = inserted.Org
      AND old.[id_vend] = inserted.id_vend
      AND old.[id_org_orig] = inserted.id_org_orig
      AND old.[id_vchr_nbr] = inserted.id_vchr_nbr
      AND old.[id_vchr_line] = inserted.id_vchr_line;

INSERT INTO [dbo].[vwMP_VOL] 
SELECT inserted.* FROM inserted 
LEFT JOIN (SELECT [Org]
      ,[id_vend]
      ,[id_org_orig]
      ,[id_vchr_nbr]
      ,[id_vchr_line]
  FROM [vwMP_VOL]) AS old
ON old.[Org] = inserted.Org
      AND old.[id_vend] = inserted.id_vend
      AND old.[id_org_orig] = inserted.id_org_orig
      AND old.[id_vchr_nbr] = inserted.id_vchr_nbr
      AND old.[id_vchr_line] = inserted.id_vchr_line
WHERE old.Org IS NULL;

END

Inserting new rows works fine, but all existing rows get updated with one set of values.

SELECT     
text_vchr, count(*) as dups
FROM MP_VOL
group by text_vchr
order by count(*) desc

Results:
text_vchr                   dups
-------------------------   -----
MEDIA MONITORING SERVICES   20897
                                1

Do I have to disable fastload in SSIS, or is there some other way to fix it?

(It's SQL Server 2005, so MERGE is not available)

0

2 Answers 2

4

I suspect the issue appears because you are referencing the view twice in the UPDATE statement, once in UPDATE [dbo].[vwMP_VOL] clause and then in FROM clause and the second time you are giving it an alias. But you are not using the alias in the SET clause, only the first reference.

The result is that the the two references are unrelated and the rows are updated with unrelated data (and possibly many times as well!)

See the the official MSDN documentation about UPDATE and the caution:

Best Practices

...

Use caution when specifying the FROM clause to provide the criteria for the update operation. The results of an UPDATE statement are undefined if the statement includes a FROM clause that is not specified in such a way that only one value is available for each column occurrence that is updated, that is if the UPDATE statement is not deterministic.

and a more detailed analysis of this "feature" and other issues it can cause, in Hugo Kornelis's blog post: Let's deprecate UPDATE FROM! and especially this paragraph:

Correctness? Bah, who cares?

Well, most do. That’s why we test.

If I mess up the join criteria in a SELECT query so that too many rows from the second table match, I’ll see it as soon as I test, because I get more rows back then expected. If I mess up the subquery criteria in an ANSI standard UPDATE query in a similar way, I see it even sooner, because SQL Server will return an error if the subquery returns more than a single value. But with the proprietary UPDATE FROM syntax, I can mess up the join and never notice – SQL Server will happily update the same row over and over again if it matches more than one row in the joined table, with only the result of the last of those updates sticking. And there is no way of knowing which row that will be, since that depends in the query execution plan that happens to be chosen. A worst case scenario would be one where the execution plan just happens to result in the expected outcome during all tests on the single-processor development server – and then, after deployment to the four-way dual-core production server, our precious data suddenly hits the fan…


I think the best way to solve this is to use the alias in the UPDATE clause and change accordingly the SET clause:

UPDATE old 
   SET
       [id_batch] = inserted.id_batch
      ,[id_invoice] = inserted.id_invoice
      ,[text_vchr] = inserted.text_vchr
      ,[amt_frt_tcur] = inserted.amt_frt_tcur
      ,[amt_line_hcur] = inserted.amt_line_hcur
      ,[line_total_hcur] = inserted.line_total_hcur
      ,[id_tax_1] = inserted.id_tax_1
      ,[id_tax_2] = inserted.id_tax_2
      ,[id_tax_3] = inserted.id_tax_3
      ,[id_tax_4] = inserted.id_tax_4
      ,[line_total_rem_hcu] = inserted.line_total_rem_hcu
      ,[id_po] = inserted.id_po
      ,[cnt_rlse] = inserted.cnt_rlse
      ,[cnt_line_nbr] = inserted.cnt_line_nbr
FROM inserted 
          --   INNER JOIN (SELECT * FROM [dbo].[vwMP_VOL]) AS old
-- or the simpler
INNER JOIN [dbo].[vwMP_VOL] AS old
ON old.[Org] = inserted.Org
      AND old.[id_vend] = inserted.id_vend
      AND old.[id_org_orig] = inserted.id_org_orig
      AND old.[id_vchr_nbr] = inserted.id_vchr_nbr
      AND old.[id_vchr_line] = inserted.id_vchr_line;

This would work, too, but I don't like it as much as the previous because we can't alias the table:

UPDATE [dbo].[vwMP_VOL] 
   SET
       [id_batch] = inserted.id_batch
      ---
      ,[cnt_line_nbr] = inserted.cnt_line_nbr
FROM inserted 
WHERE [dbo].[vwMP_VOL].[Org] = inserted.Org
      AND [dbo].[vwMP_VOL].[id_vend] = inserted.id_vend
      AND [dbo].[vwMP_VOL].[id_org_orig] = inserted.id_org_orig
      AND [dbo].[vwMP_VOL].[id_vchr_nbr] = inserted.id_vchr_nbr
      AND [dbo].[vwMP_VOL].[id_vchr_line] = inserted.id_vchr_line;
0
1

Change your update to

UPDATE [dbo].[vwMP_VOL]
   SET
    [vwMP_VOL].[id_batch] = inserted.id_batch
      ,[vwMP_VOL].[id_invoice] = inserted.id_invoice
      ,[vwMP_VOL].[text_vchr] = inserted.text_vchr
      ,[vwMP_VOL].[amt_frt_tcur] = inserted.amt_frt_tcur
      ,[vwMP_VOL].[amt_line_hcur] = inserted.amt_line_hcur
      ,[vwMP_VOL].[line_total_hcur] = inserted.line_total_hcur
      ,[vwMP_VOL].[id_tax_1] = inserted.id_tax_1
      ,[vwMP_VOL].[id_tax_2] = inserted.id_tax_2
      ,[vwMP_VOL].[id_tax_3] = inserted.id_tax_3
      ,[vwMP_VOL].[id_tax_4] = inserted.id_tax_4
      ,[vwMP_VOL].[line_total_rem_hcu] = inserted.line_total_rem_hcu
      ,[vwMP_VOL].[id_po] = inserted.id_po
      ,[vwMP_VOL].[cnt_rlse] = inserted.cnt_rlse
      ,[vwMP_VOL].[cnt_line_nbr] = inserted.cnt_line_nbr
FROM inserted 
INNER JOIN vwMP_VOL AS old
ON old.[Org] = inserted.Org
      AND old.[id_vend] = inserted.id_vend
      AND old.[id_org_orig] = inserted.id_org_orig
      AND old.[id_vchr_nbr] = inserted.id_vchr_nbr
      AND old.[id_vchr_line] = inserted.id_vchr_line;

Currently optimizer can not link your subquery with updated table and updates all rows.

EDIT
Test case

-- test data
SELECT * INTO #authors
FROM (SELECT
   '1'  au_id, 'White'  au_lname
    UNION ALL SELECT '2' , 'Green'
    UNION ALL SELECT '3' , 'Red'
    UNION ALL SELECT '4' , 'Blue'
) x;

select * from #authors;

-- OK

begin tran tr;
WITH inserted AS (
    SELECT  '1' au_id, '999'  au_lname
    UNION ALL SELECT '2' , '888'
)
UPDATE #authors
SET au_lname= inserted.au_lname
FROM inserted 
JOIN #authors old
ON old.au_id = inserted.au_id;
SELECT 'OK' AS runname, * FROM #authors;
rollback tran tr;

-- all rows updated

begin tran tr;
WITH inserted AS (
    SELECT  '1' au_id, '999'  au_lname
    UNION ALL SELECT '2' , '888'
)
UPDATE #authors
SET au_lname = inserted.au_lname
FROM inserted 
JOIN ( 
    SELECT * 
    FROM #authors ) old
ON old.au_id = inserted.au_id;
SELECT 'ALL ROWS' AS RUNNAME, * FROM #authors;
rollback tran tr;
4
  • @ypercubeᵀᴹ See test case.
    – Serg
    Jun 8, 2016 at 9:21
  • @ypercubeᵀᴹ: Now that the view is used directly rather than as a derived table, putting either the real name or the alias after UPDATE would equally reference the same instance – the one introduced by the join. When a derived table is used, the real name after UPDATE cannot be matched with it and thus represents a new instance (unrelated to either table in the FROM clause).
    – Andriy M
    Jun 8, 2016 at 9:22
  • 1
    @AndriyM yes, it seems you are right. Still I don't like this use and depending on where the parser/optimizer may fail or not. Jun 8, 2016 at 9:37
  • 2
    @ypercubeᵀᴹ: Neither do I. If I assign an alias to the target table in the FROM clause, I always update it by the alias rather than by the [original] name.
    – Andriy M
    Jun 8, 2016 at 9:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.