UPSERT but know what is inserted and what is updated
(1) By anonymous on 2021-12-10 18:19:14 [link] [source]
Let's say that I have a table:
drop table if exists new_test;
CREATE TABLE new_test ( id INTEGER NOT NULL, fld_text VARCHAR(20) NOT NULL , fld_int integer not null, updated_at DATETIME default CURRENT_TIMESTAMP NOT NULL, PRIMARY KEY (id) );
create unique index new_test_idx on new_test (fld_text);
insert into new_test (fld_text, fld_int) values ('A', 1), ('B', 2), ('C', 3);
Now I want to insert some rows, but for the existing fld_text if fld_int is the same don't update anything. If for the existing fld_text, fld_int is different - update. If fld_text is new - insert.
I need something like this:
insert into new_test (fld_text, fld_int) values ('B', 2), ('C', 300), ('D', 4) on conflict (fld_text) do update fld_int if fld_int != new_fld_int else do nothing returning just updated and inserted rows;
What I would like to get from last insert is:
C, 300 - updated D, 4 - inserted
B shouldn't be in in the returning list because nothing is changed.
Can I somehow get this behavior in sqlite?
(2) By Richard Hipp (drh) on 2021-12-10 19:01:39 in reply to 1 [source]
INSERT INTO new_test(fld_text, fld_int) VALUES('B',2),('C',300),('D',4) ON CONFLICT(fld_text) DO UPDATE SET fld_int=excluded.fld_int WHERE fld_int<>excluded.fld_int RETURNING fld_text, fld_int;
(3) By anonymous on 2021-12-11 07:44:54 in reply to 2 [link] [source]
Yes, that is it. Thanks. My main problem is now that sqlalchemy doesn't support returning for sqlite at all. I don't se any other option than to use text and generate sqlquery as string. In that case I will loose orm and instead of python table class objects I will get tupples.
(4) By anonymous on 2021-12-11 10:55:41 in reply to 2 [link] [source]
How about this one: drop table if exists new_test; CREATE TABLE new_test ( id INTEGER NOT NULL, fld_text VARCHAR(20) NOT NULL, fld_int1 integer, fld_int2 integer, PRIMARY KEY (id) ); create unique index new_test_idx on new_test (fld_text); insert into new_test (fld_text, fld_int1, fld_int2) values ('A',1, 1), ('B',2, 2), ('C',3, 2); select * from new_test; id|fld_text|fld_int1|fld_int2| --+--------+--------+--------+ 1|A | 1| 1| 2|B | 2| 2| 3|C | 3| 2| INSERT INTO new_test (fld_text, fld_int1, fld_int2) VALUES ('B',null, 20), ('C',300, null), ('D',4, 4) ON CONFLICT(fld_text) DO UPDATE SET fld_int1=excluded.fld_int1, fld_int2=excluded.fld_int2 WHERE (excluded.fld_int1 is not null and fld_int1<>excluded.fld_int1) or (fld_int2<>excluded.fld_int2 is not null and fld_int2<>excluded.fld_int2) --returning fld_text, fld_int1, fld_int2 ; select * from new_test; id|fld_text|fld_int1|fld_int2| --+--------+--------+--------+ 1|A | 1| 1| 2|B | | 20| 3|C | 300| | 4|D | 4| 4| I would like that if new value is null not to replace it. So, in row id=2, I would like to keep old value fld_int1=2 instead of replacing it with null. Is it possible to do that?
(5) By Ryan Smith (cuz) on 2021-12-11 15:16:39 in reply to 4 [link] [source]
There are Two ways of achieving that - using IFNULL(A, B) which retrurns A, unless it is NULL, in which case it returns B, or, COALESCE(A, B, C, ...) which returns the first non-NULL value (A or B or C or ... etc.) from the left.
See here both demonstrated in your query:
-- SQLite version 3.35.4 [ Release: 2021-04-02 ] on SQLitespeed version 2.1.3.11.
================================================================================================
drop table if exists new_test;
CREATE TABLE new_test (
id INTEGER NOT NULL,
fld_text VARCHAR(20) NOT NULL,
fld_int1 integer,
fld_int2 integer,
PRIMARY KEY (id)
);
create unique index new_test_idx on
new_test (fld_text);
insert into new_test
(fld_text, fld_int1, fld_int2)
values
('A',1, 1),
('B',2, 2),
('C',3, 2);
select * from new_test;
-- id |fld_text| fld_int1 |fld_int2
-- ------------|--------|------------|--------
-- 1 | A | 1 | 1
-- 2 | B | 2 | 2
-- 3 | C | 3 | 2
INSERT INTO new_test
(fld_text, fld_int1, fld_int2)
VALUES
('B',null, 20),
('C',300, null),
('D',4, 4)
ON CONFLICT(fld_text) DO UPDATE SET
fld_int1=COALESCE(excluded.fld_int1,fld_int1),
fld_int2=COALESCE(excluded.fld_int2,fld_int2)
WHERE (excluded.fld_int1 is not null and fld_int1<>excluded.fld_int1)
or (fld_int2<>excluded.fld_int2 is not null and fld_int2<>excluded.fld_int2)
;
select * from new_test;
-- id |fld_text|fld_int1|fld_int2
-- ------------|--------|--------|--------
-- 1 | A | 1 | 1
-- 2 | B | 2 | 20
-- 3 | C | 300 | 2
-- 4 | D | 4 | 4
INSERT INTO new_test
(fld_text, fld_int1, fld_int2)
VALUES
('B',null, 21),
('C',301, null),
('D',5, 5)
ON CONFLICT(fld_text) DO UPDATE SET
fld_int1=IFNULL(excluded.fld_int1,fld_int1),
fld_int2=IFNULL(excluded.fld_int2,fld_int2)
WHERE (excluded.fld_int1 is not null and fld_int1<>excluded.fld_int1)
or (fld_int2<>excluded.fld_int2 is not null and fld_int2<>excluded.fld_int2)
;
select * from new_test;
-- id |fld_text|fld_int1|fld_int2
-- ------------|--------|--------|--------
-- 1 | A | 1 | 1
-- 2 | B | 2 | 21
-- 3 | C | 301 | 2
-- 4 | D | 5 | 5
(6) By anonymous on 2021-12-21 20:36:34 in reply to 5 [link] [source]
It works. Thanks again.