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 [link] [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 [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.