You can't specify target table 'item' for update in FROM clause

来源：互联网发布：java随机数生成器编辑：程序博客网时间：2024/05/29 15:03

今天写sql处理历史数据时，报出标题的错误，sql如下:

UPDATE itemSET platf_min_sell_price = NULL, platf_max_sell_price = NULLWHERE    item_id IN (        SELECT DISTINCT            item_id        FROM            item_sku        WHERE            sku_id IN (                SELECT                    sku_id                FROM                    trade_sku_price                WHERE                    item_id IN (                        SELECT                            item_id                        FROM                            item                        WHERE                            platform_id = 123456                        AND platf_min_sell_price IS NOT NULL                        AND platf_max_sell_price IS NOT NULL                    )            )        AND attributes IS NOT NULL    )

意思大概就是你不能一边查着item表一边修改它，否则可能会引起问题。
stackoverflow给出的一种解决办法是，把子句查询结果存放到临时表中，修改后的sql如下：

UPDATE itemSET platf_min_sell_price = NULL, platf_max_sell_price = NULLWHERE    item_id IN (        SELECT            item_id        FROM            (                (                    (                        SELECT DISTINCT                            item_id                        FROM                            item_sku                        WHERE                            sku_id IN (                                SELECT                                    sku_id                                FROM                                    trade_sku_price                                WHERE                                    item_id IN (                                        SELECT                                            item_id                                        FROM                                            item                                        WHERE                                            platform_id = 123456                                        AND platf_min_sell_price IS NOT NULL                                        AND platf_max_sell_price IS NOT NULL                                    )                            )                        AND attributes IS NOT NULL                    )                )            ) AS t    )

阅读全文

0 0