Re: Use ctid in where clause in update from statement

This is just a very simplified sample and doesn’t makes sense, but it shows the execution plan. The original query is more complex. It selects more values and updates more columns.

Dirk

--
Dirk Mika
Software Developer

[cid:image001_d0a5cf51-5dd3-4ff5-b428-1016a6d95a0f.png]

mika:timing GmbH
Strundepark - Kürtener Str. 11b
51465 Bergisch Gladbach
Germany

fon +49 2202 2401-1197
dirk(dot)mika(at)mikatiming(dot)de
www.mikatiming.de

AG Köln HRB 47509 * WEEE-Reg.-Nr. DE 90029884
Geschäftsführer: Harald Mika, Jörg Mika

[cid:CCh2U_f27a5ce6-8556-4db1-8aa4-fd610d6e9efe.jpg]<https://youtu.be/qfOFXrpSKLQ>

Von: Achilleas Mantzios <achill(at)matrix(dot)gatewaynet(dot)com>
Datum: Montag, 1. Juli 2019 um 11:50
An: "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org>
Betreff: Re: Use ctid in where clause in update from statement

On 1/7/19 12:13 μ.μ., Dirk Mika wrote:

UPDATE test_large d
SET grp = s.grp
FROM (SELECT ctid, test_large.*
FROM test_large
WHERE grp = 1) s
WHERE d.ctid = s.ctid;

Besides, what the above does is select for rows that have grp=1 and then set grp to the same value.

BR
Dirk

--
Dirk Mika
Software Developer

[cid:part1(dot)F9658A75(dot)C84DBA7E(at)matrix(dot)gatewaynet(dot)com]

mika:timing GmbH
Strundepark - Kürtener Str. 11b
51465 Bergisch Gladbach
Germany

fon +49 2202 2401-1197
dirk(dot)mika(at)mikatiming(dot)de<mailto:dirk(dot)mika(at)mikatiming(dot)de>
www.mikatiming.de<http://www.mikatiming.de>

AG Köln HRB 47509 * WEEE-Reg.-Nr. DE 90029884
Geschäftsführer: Harald Mika, Jörg Mika

[cid:part2(dot)3A5504AA(dot)38B3DF7A(at)matrix(dot)gatewaynet(dot)com]<https://youtu.be/qfOFXrpSKLQ>

--

Achilleas Mantzios

IT DEV Lead

IT DEPT

Dynacom Tankers Mgmt