Can one insert statement also do updates?

(1.1) By Daniel Steigerwald (steida) on 2024-07-18 09:40:09 edited from 1.0 [link] [source]

I can’t find whether CTE invoked by insert can select and update within the same table. Postgres afaik supports that.

(2) By Richard Hipp (drh) on 2024-07-18 09:54:56 in reply to 1.1 [source]

I do not understand your question. Why don't you demonstrate by showing us actual SQL code that works on PostgreSQL but not on SQLite?

(3.1) By Daniel Steigerwald (steida) on 2024-07-20 11:26:35 edited from 3.0 in reply to 2 [link] [source]

I wrote AFAIK (as far as I know) because I read somewhere it should be possible. Sorry, I don't have an example of such SQL.

I'm asking because I'm implementing a logic that must run within SQLite itself.

My question is whether I have to write two statements, one for the insert and the second for the update. The problem is I would like to reuse some values I read for insert. If that's not possible, I will have to read them again.

I hope this makes sense in what I'm writing. Thank you for your time.

(4) By Gunter Hick (gunter_hick) on 2024-07-18 20:15:42 in reply to 3.0 [link] [source]

CTEs are data sources, not data sinks. The part that actually changes the database comes at the end and can be an INSERT, UPDATE or DELETE statement; as such, it can only modify exactly one table.

If you need to do something to some other table whenever an INSERT, UPDATE or DELETE happens on your table, then you should be writing a trigger program (which again can invoke an iNSERT, UPDATE or DELETE on exactly one "some other table").

(5) By Daniel Steigerwald (steida) on 2024-07-18 20:42:13 in reply to 4 [link] [source]

Thank you for the answer. I don't have to do something to some other table. I want to insert and update the same table in one statement.

(6.1) By Bone (boneatjp) on 2024-07-22 08:59:21 edited from 6.0 in reply to 5 [link] [source]

Deleted

(7) By Daniel Steigerwald (steida) on 2024-07-20 18:36:20 in reply to 6.0 [link] [source]

I need an update if the insert was successful and no update if the insert would violate the unique index. Is that semantic possible with SQLite?

(8.1) By Bone (boneatjp) on 2024-07-22 08:59:31 edited from 8.0 in reply to 7 [link] [source]

Deleted

(9) By Holger J (holgerj) on 2024-07-21 14:14:31 in reply to 7 [link] [source]

It would still be useful if you gave us the two SQL statements you want to combine into one. Including table creation and data, of course.

(10) By Spindrift (spindrift) on 2024-07-21 17:29:04 in reply to 7 [link] [source]

That sounds like you just want a before trigger.

Check if the insert would proceed (in the trigger). If so, update as you wish then allow it to insert.

If the index is already there, do nothing.

Then the conflict clause on the table kills the insert.

Are we missing anything more subtle about your intent?

(11.1) By Daniel Steigerwald (steida) on 2024-07-22 08:08:06 edited from 11.0 in reply to 10 [link] [source]

The happy day workflow is one insert with several updates in the same table if insert is allowed (does not violate the PK).

create table Message (
  timestamp int primary key,
  hash text
)

What I do right now is insert a message without a hash, and then in the update part, I check whether the timestamp has a hash. The already-inserted message has a computed hash, so it does nothing (and also skips other updates).

insert into Message (timestamp)
values (@timestamp)
on conflict do nothing

The update part uses a recursive CTE so it can't be triggered by the insert. I don't think the insert could be invoked by a trigger because there could be several updates, and I don't know which one needs the insert.

(12) By Spindrift (spindrift) on 2024-07-22 09:52:11 in reply to 11.1 [link] [source]

I see, thank you for the explanation.

I think we're back to needing to see some actual functioning SQL code.

(13) By Holger J (holgerj) on 2024-07-22 13:06:15 in reply to 11.1 [link] [source]

The ISO SQL language doesn't know the @ sign. There are no variables in ISO SQL.

You could use a default value, which is only used when no value for a column isn't provided.

If you need a computed value individually for each row, a row trigger can do that for you, because the DEFAULT clause only allows static values (or CURRENT_... functions).