如何修复SQL查询中表重复指定错误？含SELECT与UPDATE改写方案

Got it, let's sort out these query errors for you. The issue here is that MySQL restricts referencing the same table directly as both the target of a write operation (like UPDATE) and as a data source in a subquery. Even for SELECT, wrapping the subquery into a derived table (with an alias) avoids any potential ambiguity. Here's how to rewrite both queries:

1. Corrected SELECT Query #

While your original SELECT might not throw an error on its own in most cases, wrapping the inner subquery into a named derived table makes it explicit and avoids conflicts if this query is part of a larger operation (like a DELETE or JOIN).

SELECT pm.* 
FROM `wp_postmeta` pm
WHERE pm.`meta_key` = '_test' 
AND pm.`post_id` IN (
    SELECT post_id 
    FROM (
        SELECT post_id 
        FROM `wp_postmeta` 
        WHERE meta_value = 8023
    ) AS sub_query
);

The key change here is adding AS sub_query to the inner nested SELECT—this tells MySQL to treat that inner result as a temporary, standalone table, so it doesn't confuse it with the outer wp_postmeta reference.

2. Corrected UPDATE Query #

For UPDATE, this same derived table trick is mandatory to fix the "table specified twice" error. You have two solid options:

Option 1: Using IN with a Derived Table #

UPDATE wp_postmeta 
SET meta_value = 5.55 
WHERE meta_key = '_regular_price' 
AND post_id IN (
    SELECT post_id 
    FROM (
        SELECT post_id 
        FROM wp_postmeta 
        WHERE meta_value = 8023
    ) AS sub_posts
);

Option 2: Using JOIN (More Efficient for Large Datasets) #

If you're working with a lot of data, using a JOIN is often faster than IN, as it can leverage table indexes more effectively:

UPDATE wp_postmeta main
JOIN (
    SELECT post_id 
    FROM wp_postmeta 
    WHERE meta_value = 8023
) AS sub_posts ON main.post_id = sub_posts.post_id
SET main.meta_value = 5.55
WHERE main.meta_key = '_regular_price';

Here, we alias the outer wp_postmeta as main and join it with the derived sub_posts table, which makes the table references crystal clear to MySQL.

内容的提问来源于stack exchange，提问作者bigdaveygeorge