Member Avatar for Violet_82

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?

Edited by Violet_82
  • 2 Contributors
  • 2 Replies
  • 391 Views
  • 3 Days Discussion Span
  • Latest Post Latest Post by Violet_82

Dani AI

Generated

Brief explanation and safe ways to do what described.

MySQL will refuse to update a derived-table or to modify a table while simultaneously selecting from that same table in a subquery; that is why the “target table … is not updatable” / ERROR 1093 happens. The restriction exists to prevent ambiguous self-referential updates. (dev.mysql.com)

If the goal is “select some rows, then change a column on those rows,” the most robust pattern is to materialize the selection (temporary table or CTE) and then join it for the update. Example using a temporary table (works safely on old and new MySQL versions):

CREATE TEMPORARY TABLE tmp_ids (id INT PRIMARY KEY);

INSERT INTO tmp_ids (id)
SELECT id FROM books WHERE location_id = 4;

UPDATE books b
JOIN tmp_ids t ON b.id = t.id
SET b.location_id = 2;

DROP TEMPORARY TABLE tmp_ids;

This avoids optimizer surprises and gives you a clear, testable two-step workflow. (baeldung.com)

A single-statement multi-table UPDATE that joins a derived table is another option (keeps it in one statement). If you use a derived table, you may need to force materialization (wrap the inner SELECT or use the NO_MERGE hint / change derived_merge) so the server doesn’t try to merge the subquery back into the outer query and re-raise the same error. Example pattern (materialize the subquery):

UPDATE books AS b
JOIN (SELECT id FROM (SELECT id FROM books WHERE location_id = 4) AS sel1) AS sel
  ON b.id = sel.id
SET b.location_id = 2;

Note: forcing materialization or using optimizer hints is version- and config-dependent. (dev.mysql.com)

If you run MySQL 8+, a CTE + JOIN is a readable option:

WITH to_move AS (SELECT id FROM books WHERE location_id = 4)
UPDATE books b
JOIN to_move t ON b.id = t.id
SET b.location_id = 2;

CTE support exists, but some older 8.x behaviors/bugs around read-only CTEs have been reported—test on your server first. (dev.mysql.com)

Practical tips: prefer the simple single-row UPDATE for tiny ad-hoc changes; for bulk/production changes, run inside a transaction (or test on a snapshot), ensure the join column is indexed (id) and be mindful of foreign-key constraints and InnoDB processing order. (dev.mysql.com)

(References: MySQL manual + community threads explaining ERROR 1093 and the materialization/workarounds.)

Recommended Answers

All 2 Replies

Member Avatar for Dani

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
)
Member Avatar for Violet_82

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.