We've just covered upserts which is a non standard SQL feature that SQLite implements based on the way that Postgres does it. And now we have returning which is also non standard sequel, very helpful and also implemented in a similar way as postgres. Let's take a look. In this last video, we did this thing here where we were selecting from key value and then when there is a conflict on key we did an update to increment the value. And, I mean it works, right?

It works and we get query 1, okay, but like what is the new value? You would potentially have to issue a second query to go back and get the new value which is now 20 unless you use the returning clause and you can say returning everything. So if you return everything, you get the row that was updated back which is incredibly helpful. If you want just the column, just any particular column, you could say, give me value, you could say, give me key, you could say, give me value as new val and that would work just as well. This is extremely incredibly helpful.

So with this zero zero zero, let's do delete from kv where key equals that guy returning everything. This shows you what was deleted. So you can issue, you can issue like a, what is the word that I'm looking for? What was the word? Speculative.

You could issue a speculative delete and then use this returning keyword to get back all of the rows that were returned. So now we can insert it again and you see that there was no conflict, so we inserted the right value and we got one back as the new value. This works with inserts, updates, and deletes. You must remember or you must be aware, that these returnings may not come back in the same order. So, how could we show this?

We can do something like we can do, we can do a second we can do a second insert here and let's make it this one. So we'll insert 1 and 1, otherwise, we'll increment. And if you do the returning, you're not necessarily guaranteed that this 36 are in this order. So you need you do need to be careful that the returning can come back in any arbitrary order. So don't count on it being the same order as whatever you've done over here, which is why it's usually good to return everything got our upsert, we've got got our upsert, we've got our returning, we've got our SQLite is incredibly good.

Returning can be helpful in other scenarios besides upserts. You can imagine inserting and immediately getting back the, perhaps, auto generated ID for that row. Inserting, a row that has either a SQLite random or SQLite date, time or something where SQLite is responsible for generating the value. And then once you insert it, you wanna get that immediately back with out issuing a new query. In that case, you would want to reach for the returning keyword.