On Wed, Dec 02, 2020 at 02:08:41PM -0800, Adrian Klaver <adrian.klaver@xxxxxxxxxxx> wrote: > On 12/2/20 2:02 PM, Thomas Kellerer wrote: > > guyren@xxxxxxxxxx schrieb am 02.12.2020 um 21:27: > > > The Halloween problem is that it is a challenge for the database if > > > you’re updating a field that is also in the WHERE clause of the same > > > query. > > > > > > I just saw a presentation from someone about how in SQL Server he > > > recommended writing changes to a temp table and then writing them to > > > the table as being much more efficient. > > > > It sounds strange to me, that this _is_ actually a problem. > > > > Why exactly is that a problem in SQL Server? > > Yeah that was a new one to me. A quick search found: > > https://www.sqlshack.com/the-halloween-problem-in-sql-server-and-suggested-solutions/ > > > And what are the consequences if you do it nevertheless. It looks like the anser is no (unless I've misunderstood the problem): create table a (id serial not null primary key, a integer not null, b integer not null); create index a_a on a(a); insert into a (a, b) values (1, 2); insert into a (a, b) values (2, 3); insert into a (a, b) values (3, 4); insert into a (a, b) values (4, 5); insert into a (a, b) values (5, 6); insert into a (a, b) values (6, 7); update a set a = a + 1 where a < 4; select * from a order by id; drop table a cascade; results in: id | a | b ----+---+--- 1 | 2 | 2 2 | 3 | 3 3 | 4 | 4 4 | 4 | 5 5 | 5 | 6 6 | 6 | 7 It's the same with or without the index on a(a). cheers, raf
