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

Question

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

Derived table alias can be used to provide reference i.e. we have same column in outer query and inner query then alias given to derived table can be used to distinguish between them. See my answer for your issue. — Dark Knight, Aug 26 '19 at 01:00
We might instead focus on writing a MUCH better query. For that though, we should start with the SELECT that returns the rows for updating, together with their update value. And to that end, see https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query — Strawberry, Aug 26 '19 at 06:58

Dark Knight · Answer 1 · 2019-08-27T10:32:09.840

1

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:

MySQL Update Syntax - Don't use table keyword in update statement.
Every Derived Table must have alias - every sub/temp table should have alias which can be used for reference in outer query.

edited Aug 27 '19 at 10:32

answered Aug 26 '19 at 00:58

Dark Knight

6,116
1
15
37

@HelloWorld, if this answer solves your problem, do accept the answer. :) – Dark Knight Aug 26 '19 at 06:54
Still getting /* Błąd SQL (1054): Unknown column 'B.item_id' in 'where clause' */, just want to add that table A,B,C is the same table. – HelloWorld Aug 26 '19 at 09:10
Please provide expected output with table structure. Include create code and insert queries in question, so we can work on this. – Dark Knight Aug 26 '19 at 09:17
Output should modify active field to -1 when auction_id ended in the specified time frame and its item_id does not contain active field equal to 1. See modified code. – HelloWorld Aug 26 '19 at 11:50
Added a select statement that output should be used for an update statement. – HelloWorld Aug 27 '19 at 10:00
@HelloWorld, verify it and accept & do vote up, if it solves your problem. – Dark Knight Aug 28 '19 at 05:49
When I placed your select statement I had to stop my query after 1 min. and 22 sec. ,it never executed. – HelloWorld Aug 28 '19 at 08:10
Try putting Explain before select statement and share the output. – Dark Knight Aug 28 '19 at 08:21
Sorry I didn't noticed that you updated the question, it didn't send notification. Btw will look into it. – Dark Knight Aug 28 '19 at 21:10
Create separate indexes on `ended_on`& `active` columns and run the query. Revert back in comment with output. So I can be notified. – Dark Knight Aug 28 '19 at 21:14
I do not want to add new indexes. When I checked query that i used to make an update it works fine by itself ,however when tried to use an update it does not work. – HelloWorld Aug 30 '19 at 21:42
It depends on how query has been written, if you have proper index, queries based on that structure will improve performance. I suggest you should try it, you can create a single index `( active, ended_on)`. – Dark Knight Aug 30 '19 at 23:04

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

1 Answers1