Mysql Err:1248 Every derived table must have its own alias

Question

I know I have to add alias to sub-query, but I'm not sure where to add it

Query:

UPDATE RETRY_MESSAGE_%d SET NEXT_RETRY_TIME = NOW() 
   WHERE NEW_SCMSGREF = 
     (SELECT NEW_SCMSGREF FROM 
     (SELECT NEW_SCMSGREF FROM RETRY_MESSAGE_%d 
         where DESTADDR = :destAddr ORDER BY PRIORITY, SCTS) 
     WHERE rownum < 2) AS TEMP

What can I try to fix this?

You'll need it on both subqueries. One looks good ('as temp'). — ethrbunny, Apr 04 '13 at 11:37

score 1 · Answer 1 · answered Apr 04 '13 at 11:54

Study the following thoroughly... not a subquery in sight...

 DROP TABLE IF EXISTS my_table;

 CREATE TABLE my_table
 (rownum INT NOT NULL AUTO_INCREMENT PRIMARY KEY
 ,my_column INT NOT NULL
 ,next_retry_time DATETIME NOT NULL
 ,destaddr VARCHAR(20) NOT NULL
 );

 INSERT INTO my_table (my_column,next_retry_time,destaddr)
 VALUES
 (101,NOW(),'my_string'),
 (102,NOW(),'not_my_string'),
 (101,NOW(),'not_my_string'),
 (102,NOW(),'not_my_string'),
 (102,NOW(),'my_string'),
 (103,NOW(),'my_string');

 SELECT * FROM my_table;
 +--------+-----------+---------------------+---------------+
 | rownum | my_column | next_retry_time     | destaddr      |
 +--------+-----------+---------------------+---------------+
 |      1 |       101 | 2013-04-04 12:50:51 | my_string     |
 |      2 |       102 | 2013-04-04 12:50:51 | not_my_string |
 |      3 |       101 | 2013-04-04 12:50:51 | not_my_string |
 |      4 |       102 | 2013-04-04 12:50:51 | not_my_string |
 |      5 |       102 | 2013-04-04 12:50:51 | my_string     |
 |      6 |       103 | 2013-04-04 12:50:51 | my_string     |
 +--------+-----------+---------------------+---------------+

 SELECT *
   FROM my_table x
   JOIN my_table y
     ON y.my_column = x.my_column
  WHERE y.destaddr = 'my_string'
    AND y.rownum < 3;

 +--------+-----------+---------------------+---------------+--------+-----------+---------------------+-----------+
 | rownum | my_column | next_retry_time     | destaddr      | rownum | my_column | next_retry_time     | destaddr  |
 +--------+-----------+---------------------+---------------+--------+-----------+---------------------+-----------+
 |      1 |       101 | 2013-04-04 12:50:51 | my_string     |      1 |       101 | 2013-04-04 12:50:51 | my_string |
 |      3 |       101 | 2013-04-04 12:50:51 | not_my_string |      1 |       101 | 2013-04-04 12:50:51 | my_string |
 +--------+-----------+---------------------+---------------+--------+-----------+---------------------+-----------+

 UPDATE my_table x
   JOIN my_table y
     ON y.my_column = x.my_column
    SET x.next_retry_time = NOW()
  WHERE y.destaddr = 'my_string'
    AND y.rownum < 3;

 SELECT * FROM my_table;
 +--------+-----------+---------------------+---------------+
 | rownum | my_column | next_retry_time     | destaddr      |
 +--------+-----------+---------------------+---------------+
 |      1 |       101 | 2013-04-04 12:52:28 | my_string     |
 |      2 |       102 | 2013-04-04 12:50:51 | not_my_string |
 |      3 |       101 | 2013-04-04 12:52:28 | not_my_string |
 |      4 |       102 | 2013-04-04 12:50:51 | not_my_string |
 |      5 |       102 | 2013-04-04 12:50:51 | my_string     |
 |      6 |       103 | 2013-04-04 12:50:51 | my_string     |
 +--------+-----------+---------------------+---------------+

Thank U guys, After a Long Time :) – VPK Sep 12 '13 at 10:03 — VPK, Sep 12 '13 at 10:03

Mysql Err:1248 Every derived table must have its own alias

1 Answers1