Updating & Deleting Data

[00:00:00]
>> Updates and deletes, let's just do a normal old update now. So I'm gonna say UPDATE ingredients. Set image equal to WATERMELON.jpg or we can go back to I don't know, we'll call it strawberry I guess. .jpg WHERE title = watermelon. You can see there it didn't update.

[00:00:38]
So now if I say select star FROM the. In case is unclear, you can hit up, it'll scroll backwards through the queries that you've done before, which is what I'm doing. Okay, so I've done this update here. You just say set whatever do you want and then you give it a WHERE clause to define which one.

[00:01:04]
If I didn't give it a WHERE clause, it would update literally every single one, right, might be what you're trying to do, right? You might want literally everything to have a picture of a strawberry. You have a weird recipe app in my opinion at that point, but it's possible, right?

[00:01:24]
What if you wanna see what you're doing, right? And this actually works on the inserts this works under deletes, what if you want to see what the result of your works are after you've done them? So we can say UPDATE ingredients, set image equal to watermelon.jpg, where title equals watermelon and then I'm going to say returning.

[00:01:58]
And you can just ask for whatever you want back right? In this case, I want back the title, the image and the id and the title, right? So not only am I updating it right, this is an update command, right? It's not a select command. By saying RETURNING, this is like, hey, once you've done everything, give me back everything that you updated.

[00:02:21]
I'm sure you can imagine this being useful, like let's say you're updating a user's profile, you wanna confirm to the user that's like, hey, you set your new picture and you told me that you now live in Minneapolis instead of Seattle. So, I'm going to update the, database and ask for the new information back, so, that I can send that back down to the user.

[00:02:38]
To confirm to them, your stuff actually changed, I promise, I did it. So that's what the returning does. Now this is kind of a burdensome thing to say let's say, I literally just wanna get everything back. That's what the star is for, so I can actually just say RETURN star, which just means everything, right?

[00:03:00]
Notice this one, it gave me back everything in the order I asked for it, title, image, id, type. This one will give it back to you, I think it's generally in the order we created it, but I don't think you're guaranteed. Let's do another insert really quick. I wanna show you that you can delete multiple things with one query.

[00:03:25]
We can even copy and paste this one directly out of it because there's nothing really new here. Scroll down and copy. This one here, that one. So insert into ingredients title image, not real one, delete.jpg, delete to this. Actually, I think it's going to error cuz I was not, which is fine.

[00:03:51]
Yep, okay, so we did look up what this 0 is It's always going to be 0. So in modern Postgres, which is I think post nine, this will always return to 0. It used to be like an identifier for object ids, which they don't use anymore. But there's enough stuff that depends on that 0 being there or that number being there, that they kind of constricted by backwards compatibility for that always to be there.

[00:04:18]
So, in other words, ignore it. The second number is the only one that you care about. So now I have two titles or I have two images where the image is delete.jpg. And I want to delete both of these with one query. So I can say, DELETE FROM ingredients, WHERE image equals delete.jpg?

[00:04:51]
For fun, let's just say I want to RETURN all of them as well. Again, this returning part is optional. It's only if you wanna see what was actually deleted. So that I had id 100 and id 101, title image type vegetable. So again that default vegetable is coming from our constraint here from back/dingredients So this one has a default here of vegetable.

[00:05:26]
If you didn't delete and recreate the table with me, you probably didn't get this default. In which case, this first insert might've not worked. In which case, you would have to go back and add, not really, we have to add another type here of vegetable, or meat, or something like that.

[00:05:46]
But if you did at the default constraint, then it would work. So just to kinda show you what that would like. I'm gonna put, it doesn't matter cuz we have no constraint on it for it to be valid. And then here you would have to put type, okay?

[00:06:10]
Insert it again and then again we can do the same DELETE FROM RERUTNING star, and you can see here. Notice that we're inserting and deleting here. Here's 101, this is 102 and 103, it did not reuse those old ids, right? It's just continuing to increment. So if you delete something that unique identifier is gone forever.

[00:06:37]
I don't know about you but like I feel like weird about deleting something and it's like never going to come back. I don't know if the style is the right word for it, attached. I don't know, I probably should ask a therapist about that. Like the object permanence of unique identifiers in the universe.

[00:06:58]
I feel bad for qubits that never going to exist again. Just therapy with Brian now. Anyway, so the Delete funds are really similar to a updates. We just don't have a set in there, right? All you're doing is just you're identifying deleting from. You're giving us some WHERE clause to say, like, delete these things so you can kinda pick the things that you delete.

[00:07:25]
And then you can return them if you're interested in seeing what you deleted, otherwise it'll just show you the number of delete to right.