UPDATE FROM

2 views
Skip to first unread message

Anton Shepelev

unread,
Jul 19, 2021, 5:14:52 AMJul 19
to
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]

Anton Shepelev

unread,
Jul 19, 2021, 5:23:03 AMJul 19
to
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'.

Anton Shepelev

unread,
Jul 19, 2021, 10:21:42 AMJul 19
to
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

Erland Sommarskog

unread,
Jul 19, 2021, 2:52:56 PMJul 19
to
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.

Anton Shepelev

unread,
Jul 20, 2021, 10:12:47 AMJul 20
to
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
Reply all
Reply to author
Forward
0 new messages