1. Forum
  2. Usenet
  3. COMP.DATABASES.MS-SQLSERV

  • UPDATE FROM

    From Anton Shepelev@21:1/5 to All on Mon Jul 19 12:14:48 2021
    Hello, all. The MSSQL documentation for `UPDATE FROM' is
    not very clear on how the update source should be specified
    and how its rows are matched against those of the table
    being updated. I think the following methods are equivalent
    and correct:

    -- 1.
    UPDATE my_alias
    SET col = data_tab.col
    FROM upd_tab my_alias
    JOIN data_tab ON data_tab.code = my_alias.code

    -- 2.
    UPDATE upd_tab
    SET col = data_tab.col
    FROM upd_tab my_alias
    JOIN data_tab ON data_tab.code = my_alias.code

    But in some old code that seems to have been working for
    about ten years I have found an `UPDATE' with the following
    structure:

    -- 3.
    UPDATE upd_tab
    SET col = data_tab.col
    FROM data_tab
    WHERE data_tab.code = my_alias.code

    which, unlike the previous two commands, works non-
    deterministically, although there are no more than one row
    in data_tab from each row in upd_tab. Is it because the FROM
    clause does not mention upd_tab, whereas it must? If so,
    how is the WHERE predicate above interpreted and how does it
    affect the result?

    --
    () ascii ribbon campaign - against html e-mail
    /\ http://preview.tinyurl.com/qcy6mjc [archived]

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Anton Shepelev@21:1/5 to All on Mon Jul 19 12:23:00 2021
    I wrote:

    -- 3.
    UPDATE upd_tab
    SET col = data_tab.col
    FROM data_tab
    WHERE data_tab.code = my_alias.code

    I made a typo. Replace `my_alias' with `upd_tab'.

    --
    () ascii ribbon campaign - against html e-mail
    /\ http://preview.tinyurl.com/qcy6mjc [archived]

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Anton Shepelev@21:1/5 to All on Mon Jul 19 17:21:38 2021
    Question withdrawn. It was an error in my logic, whereas
    the syntax is quite clear. The simplest UPDATED from another
    table does *not* requre that it the table begin updated be
    mentioned in the FROM clause:

    UPDATE upd_tab
    SET col = data_tab.col
    FROM data_tab
    WHERE data_tab.code = upd_tab.code

    --
    () ascii ribbon campaign - against html e-mail
    /\ http://preview.tinyurl.com/qcy6mjc [archived]

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Erland Sommarskog@21:1/5 to Anton Shepelev on Mon Jul 19 20:52:52 2021
    Anton Shepelev (anton.txt@g{oogle}mail.com) writes:
    Question withdrawn. It was an error in my logic, whereas
    the syntax is quite clear. The simplest UPDATED from another
    table does *not* requre that it the table begin updated be
    mentioned in the FROM clause:

    UPDATE upd_tab
    SET col = data_tab.col
    FROM data_tab
    WHERE data_tab.code = upd_tab.code


    Whereas this is legal and produces something, I definitely recommend
    against it. I will have to admit that I don't understand what this is
    doing - and I certainly play an SQL expert on TV.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Anton Shepelev@21:1/5 to All on Tue Jul 20 17:12:43 2021
    Erland Sommarskog to Anton Shepelev:

    The simplest UPDATE from another table does *not* requre
    that it the table begin updated be mentioned in the FROM
    clause:

    UPDATE upd_tab
    SET col = data_tab.col
    FROM data_tab
    WHERE data_tab.code = upd_tab.code

    Whereas this is legal and produces something, I definitely
    recommend against it. I will have to admit that I don't
    understand what this is doing - and I certainly play an
    SQL expert on TV.

    I had been of simlar opinion until I tested that code. Then
    I pondered it some more and concluded that it is clear,
    logical, and correct. See for yourself:

    CREATE TABLE #upd_tab (code INT, col INT)
    CREATE TABLE #data_tab(code INT, col INT)

    INSERT INTO #upd_tab VALUES
    (8, 0),(1, 0),(7, 0),(2, 0),
    (6, 0),(3, 0),(5, 0),(4, 0)

    INSERT INTO #data_tab VALUES
    (1, 1),(2, 2),(3, 3),(4, 4),
    (5, 5),(6, 6),(7, 7),(8, 8)

    SELECT * FROM #upd_tab

    UPDATE #upd_tab
    SET col = #data_tab.col
    FROM #data_tab
    WHERE #data_tab.code = #upd_tab.code

    SELECT * FROM #upd_tab

    DROP TABLE #upd_tab
    DROP TABLE #data_tab

    --
    () ascii ribbon campaign - against html e-mail
    /\ http://preview.tinyurl.com/qcy6mjc [archived]

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)