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>
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

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]