mysql - 如何修复 SQL 错误 (1248) : Every derived table must have its own alias

Question

我在mysql更新中做错了什么？

我尝试了很多不同的方法，但无法使其工作。

在同一张表上进行更新。更正了如图所示的 SQL，但我仍然在后面的评论中得到了描述

update auctions A 
SET A.active = -1 
WHERE A.auction_id IN
(
    SELECT auction_id 
    FROM 
    (
        SELECT B.auction_id FROM
        table auctions 
        WHERE B.auction_id = A.auction_id AND B.active = 0 AND B.ended_on < "2019-04-18" AND B.ended_on > "2018-01-06" AND B.item_id 
        not IN 
                (
                    SELECT item_id 
                    FROM 
                    (
                        SELECT C.item_id from auctions C 
                        WHERE C.active = 1 
                        AND C.item_id = B.item_id
                   )    AS temp_c
             )

    )    AS temp_b
);

INSERT INTO `auctions` (`auction_id`, `item_id`, `active`, `created_by`, `started_on`, `buy_price`, `prefs`, `ended_on`, `bids`) VALUES (7333209574, 20354, 1, 2, '2019-08-23 16:12:51', NULL, 'a:23', NULL, 0);
INSERT INTO `auctions` (`auction_id`, `item_id`, `active`, `created_by`, `started_on`, `buy_price`, `prefs`, `ended_on`, `bids`) VALUES (7333209575, 20354, 0, 2, '2018-03-13 16:12:51', NULL, 'a:23', '2018-03-23 16:30:31', 0);
INSERT INTO `auctions` (`auction_id`, `item_id`, `active`, `created_by`, `started_on`, `buy_price`, `prefs`, `ended_on`, `bids`) VALUES (7333209576, 20752, 0, 2, '2018-02-13 16:12:51', NULL, 'a:23', '2018-02-23 16:30:31', 0);
INSERT INTO `auctions` (`auction_id`, `item_id`, `active`, `created_by`, `started_on`, `buy_price`, `prefs`, `ended_on`, `bids`) VALUES (7333209577, 20752, 0, 2, '2018-02-13 16:12:51', NULL, 'a:23', '2018-02-23 16:30:31', 0);
INSERT INTO `auctions` (`auction_id`, `item_id`, `active`, `created_by`, `started_on`, `buy_price`, `prefs`, `ended_on`, `bids`) VALUES (7333209577, 20752, 0, 2, '2018-06-13 16:12:51', NULL, 'a:23', '2018-06-23 16:30:31', 0);


CREATE TABLE `auctions` (
    `auction_id` BIGINT(20) NOT NULL,
    `item_id` INT(11) NOT NULL,
    `active` TINYINT(4) NULL DEFAULT '1',
    `created_by` INT(11) NULL DEFAULT NULL,
    `started_on` DATETIME NULL DEFAULT NULL,
    `buy_price` DOUBLE NULL DEFAULT NULL,
    `prefs` TEXT NULL COLLATE 'utf8_unicode_ci',
    `ended_on` DATETIME NULL DEFAULT NULL,
    `bids` INT(11) NOT NULL DEFAULT '0',
    PRIMARY KEY (`auction_id`),
    INDEX `item_id` (`item_id`),
    INDEX `created_by` (`created_by`),
    CONSTRAINT `auctions_ibfk_1` FOREIGN KEY (`item_id`) REFERENCES `data_1` (`id`),
    CONSTRAINT `auctions_ibfk_2` FOREIGN KEY (`created_by`) REFERENCES `login` (`id`)
)
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB
;

这是包含更新语句的正确输出的选择语句。您的答案包含 item_id 具有 active = 1 的auction_id，但它不应该。

SELECT * from auctions WHERE active = 0 AND ended_on < "2019-04-18" AND ended_on > "2018-01-06" AND item_id NOT IN (SELECT item_id FROM auctions WHERE active = 1);

这是 EXPLAIN 以 INSERT 形式的输出

INSERT INTO `NieznanaTabela` (`id`, `select_type`, `table`, `type`, `possible_keys`, `key`, `key_len`, `ref`, `rows`, `Extra`) VALUES (1, 'SIMPLE', 'A', 'ALL', 'item_id', NULL, NULL, NULL, 20554, 'Using where');
INSERT INTO `NieznanaTabela` (`id`, `select_type`, `table`, `type`, `possible_keys`, `key`, `key_len`, `ref`, `rows`, `Extra`) VALUES (1, 'SIMPLE', 'B', 'ref', 'item_id', 'item_id', '4', 'dbauction.A.item_id', 10, 'Using where');

Answer 1

要求:如果拍卖在某个时间范围内且事件 = 0 并且该拍卖中的项目在任何拍卖中均不活动，即事件 <> 1，则标记事件 = -1。

这将是一个简单的更新查询。

UPDATE auctions A
INNER JOIN auctions B ON A.item_id = B.item_id AND A.auction_id <> B.auction_id
SET A.active = -1
WHERE A.ended_on > '2018-01-06' AND A.ended_on < '2019-04-18'  AND A.active = 0 AND B.active <> 1;

要验证哪些记录将被更新，您可以使用 select 语句。

SELECT * 
FROM auctions A
INNER JOIN auctions B ON A.item_id = B.item_id AND A.auction_id <> B.auction_id
WHERE A.ended_on > '2018-01-06' AND A.ended_on < '2019-04-18'  AND A.active = 0 AND B.active <> 1;

现有查询的引用:

• MySQL Update Syntax - 不要在更新语句中使用table关键字。
• Every Derived Table must have alias - 每个子表/临时表都应该有别名，可用于外部查询中的引用。