Groups > comp.databases.ms-sqlserver > #2096

Re: UPDATE FROM

Path	csiph.com!newsfeed.xs4all.nl!newsfeed9.news.xs4all.nl!feeder1.feed.usenet.farm!feed.usenet.farm!aioe.org!Aby9p5R+Yqq7QceeVjZLIA.user.46.165.242.75.POSTED!not-for-mail
From	Anton Shepelev <antonius@freeshell.de>
Newsgroups	comp.databases.ms-sqlserver
Subject	Re: UPDATE FROM
Date	Tue, 20 Jul 2021 17:12:43 +0300
Organization	Aioe.org NNTP Server
Message-ID	<20210720171243.08683144ac630eea1cc14111@freeshell.de> (permalink)
References	<20210719121448.6ece4302949ccaaee268158c@g{oogle}mail.com> <20210719172138.5d4e4628fc7454f1c19fc659@g{oogle}mail.com> <XnsAD6CD46A1C54EYazorman@127.0.0.1>
Mime-Version	1.0
Content-Type	text/plain; charset=US-ASCII
Content-Transfer-Encoding	7bit
Injection-Info	gioia.aioe.org; logging-data="4476"; posting-host="Aby9p5R+Yqq7QceeVjZLIA.user.gioia.aioe.org"; mail-complaints-to="abuse@aioe.org";
Keywords	851906414
X-Notice	Filtered by postfilter v. 0.9.2
X-Newsreader	Sylpheed 3.5.0 (GTK+ 2.24.23; i686-pc-mingw32)
Xref	csiph.com comp.databases.ms-sqlserver:2096

Show key headers only | View raw

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]

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Find similar

Thread

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

csiph-web