Hi all, I have a question about updating two values in a sql table using a subquery. My database is called books and the below table books too:
id title author location_id
11 Tera Netrear 4
14 Disser Bubu 1
15 Tenno Puoio 4
16 Oban Tyrone 5
18 Sisnobi Teru 3Location_id is indeed another table as it's a foreign key in this table but that shouldn't matter. So what I want to do is to change all the location_id currently at 4 to 2. I know i could do it like this
update books set location_id = 1 where location_id = 4 but I'd like to know if I could do it with a subquery, something like update (SELECT * from books where location_id = 4) as thebook set thebook.location_id = 2 but when I run this query it tells me that the target table thebook UPDATE is not updatable. Why is that and can this be achieved - I don't really mind if it's not performant etc, I think it should be possible but I can't quite get the right way to do it...
So, the idea is, make a selection first then change a/more value/s on that selection, this is the concept I'm try to prove.
Any idea?