CODEWITHSUNDEEP
mysql
HelloWorld
HelloWorld

Reputation: 1

How to fix SQL error (1248): Every derived table must have its own alias

What do I do wrong with the update in mysql?

I tried many different ways and can not make it work.

Update is made on the same table. Corrected the sql as shown but I still get an described in later comment

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
;

Here is the select statement that contains the correct output for an update statement. Your answer contains auction_id that item_id has active = 1 but it shouldn't. 

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);

Here is the output of EXPLAIN in the form of an 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');

Upvotes: 0

Views: 144

Answers (1)

Dark Knight
Dark Knight

Reputation: 6541

Requirement: Mark active = -1, if an auction is within a time frame and active = 0 and the item in that auction is not active in any auction i.e. active <> 1.

It will be a simple update query.

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;

To verify which records will be updated, you can use select statement.

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;

Reference for your existing query:

Upvotes: 1

Related Questions