6
This isn't Halloween protection, it's normal UPDATE semantics.
Check out this warning from the docs on UPDATE:
The results of an UPDATE statement are undefined if the statement includes a FROM clause that is not specified in such a way that only one value is available for each column occurrence that is updated, that is if the UPDATE statement is not ...
5
Here is an example of using OUTPUT...INTO
--demo setup
drop table if exists InsertTest;
Create Table InsertTest (id int);
drop table if exists UpdateTest;
Create Table UpdateTest (id int);
insert into UpdateTest(id) values(1),(2);
--solution
UPDATE UpdateTest
SET id = id + 1
OUTPUT INSERTED.id
INTO InsertTest;
--verify results of insert
select * from ...
2
Will it always update all rows for the ID to DM = 1 or sometime just
one?
If you don't have NULL values in the ID column then either all DM's for one type of ID != 1 and as a result all ID's that have the same value will be updated or none.
But as said previously, depending on nullability of the ID column you can have suprising results and performance ...
2
An index only scan can only be used if all columns that the index scan returns are stored in the index.
Yours index lookup_ix contains only the columns natural_key and surrogate_key, but from the execution plan you can see:
-> Index Scan using lookup_ix on public.lookup
Output: lookup.surrogate_key, lookup.ctid, lookup.natural_key
So the index ...
2
Josh's answer (thanks Josh!) got me thinking about determinism.
I can see for the fundamental usage update table set column = value, when there are duplicate source rows, the question must be answered which matching value from the source will be written to the destination. The simplest implementation would be to let the iterators run to completion. ...
1
Adding INTO solved part of the problem.
The trick is to move the INSERT operation "into" the OUTPUT INTO clause. The INSERT is "implied", I think because OUTPUT INTO does an INSERT. This only works because I want to do an INSERT with the new ID -- if I had wanted to do an UPDATE this would not work.
Update
ts_LastIds
Set
ts_LastId=ts_LastId+1
Output
...
1
In general (if "many rows contain empty values" means "they contains NULL"):
UPDATE store_locator_store_lang t1
JOIN store_locator_store_lang t2 ON t1.id_store = t2.id_store
AND t1.lang_id = 1
AND t2.lang_id = 2
SET t2.partner = COALESCE(t2.partner, t1.partner),
t2.address = COALESCE(...
Only top voted, non community-wiki answers of a minimum length are eligible
