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    3

Location_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?

It would probably help if you let us know why you wanted to do it in a less performant way, using a subquery? This is thoroughly untested, but I guess you could do something like:

UPDATE books
SET location_id = 2
WHERE location_id IN (
    SELECT location_id
    FROM books
    WHERE location_id = 4
)

I don't think I have a good reason to be honest, I was just curious :-)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.