SQLite Forum

how can i identify the data is just inserted into the database or updated the existed item when i use upsert-clause
Login

how can i identify the data is just inserted into the database or updated the existed item when i use upsert-clause

(1) By xliangy on 2021-07-23 09:14:46 [link]

the sqlite3_changes() always returns 1 when i use upsert-clause like "
insert into table1(col1, col2) values(1, 2) on conflict(col1) do update set col2 = excluded.col2;"

is there any other api can help me to identify those two situations?

(2) By Ryan Smith (cuz) on 2021-07-23 11:37:49 in reply to 1 [link]

Probably because the upserts turn into something like individual updates internally where needed.

There's also a total-changes api, the real name escapes me now and I'm not where I can easily check it, it might be sqlite3_total_changes().

I think it is connection/session based, so the idea is to check it before and after the query to establish the changes.

I did not test this or know it for sure, it's just what came to mind reading your question. If you do try it, kindly let us know whether it worked or not.

(4) By xliangy on 2021-07-26 00:53:23 in reply to 2 [link]

i  have  tried sqlite3_total_changes(), but it do not help. i still can not identify  insert or update.

(5) By Keith Medcalf (kmedcalf) on 2021-07-26 01:06:30 in reply to 4 [link]

Of course not.  Both are "changes" to the database.

(3) By Keith Medcalf (kmedcalf) on 2021-07-23 17:13:14 in reply to 1

Use two statements with one by each?  

The purpose of being able to do two by each is to do to by each so you do not have to do the statements one by each.  

If you need to do the process one by each so you can get a count of each then do each separately and not together.

(6) By xliangy on 2021-07-26 01:14:37 in reply to 3 [link]

this may work. but when i use mysql ,i can use the api mysql_stmt_affected_rows() to identify different situations. so i want to find out a way to resolve the problem directly. thanks anyway.

(7) By anonymous on 2021-07-26 15:39:51 in reply to 6 [link]

You can check update hook https://sqlite.org/c3ref/update_hook.html

This will be notified on changes and will tell you about the type of change as well. There are some limitations so check the docs to see if it works for you.

(8) By Marco Bubke (marcob) on 2021-07-26 21:36:56 in reply to 1 [link]

The update hooks work but I think it's easier to update first and if there was nothing updated to insert. You can check the update by the last changed rowid which can be reseted to -1 before you update or by RETURNING. If you use the update hook you have to filter the table or activate or deactivate it around the CALL. RETURNING is working with non rowid tables which the upate hook is not. I used the update hook but RETURNING is in my view much simpler.