Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #2092 > unrolled thread

UPDATE FROM

Back to article view | Back to comp.databases.ms-sqlserver

Contents

  UPDATE FROM Anton Shepelev <anton.txt@g{oogle}mail.com> - 2021-07-19 12:14 +0300
    Re: UPDATE FROM Anton Shepelev <anton.txt@g{oogle}mail.com> - 2021-07-19 12:23 +0300
    Re: UPDATE FROM Anton Shepelev <anton.txt@g{oogle}mail.com> - 2021-07-19 17:21 +0300
      Re: UPDATE FROM Erland Sommarskog <esquel@sommarskog.se> - 2021-07-19 20:52 +0200
        Re: UPDATE FROM Anton Shepelev <antonius@freeshell.de> - 2021-07-20 17:12 +0300

#2092 — UPDATE FROM

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]

[toc] | [next] | [standalone]

#2093

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]

[toc] | [prev] | [next] | [standalone]

#2094

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]

[toc] | [prev] | [next] | [standalone]

#2095

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.

[toc] | [prev] | [next] | [standalone]

#2096

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]

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-sqlserver

csiph-web