Results 1 to 3 of 3

Thread: Subquery

  1. #1
    Join Date
    May 2013
    Posts
    33

    Unanswered: Subquery

    Code:
    SQL> select * from orders;
    
       ORDERID     BOOKID   QUANTITY DATEORDERED
    ---------- ---------- ---------- -------------------------------------
          1001        103          1 12-JAN-04 12.30.00.000000 PM
          1002        101          1 12-FEB-01 12.31.00.000000 PM
          1003        103          2 12-MAR-02 12.34.00.000000 PM
          1004        104          3 12-APR-03 12.36.00.000000 PM
          1006        103          2 12-JUN-01 12.59.00.000000 PM
          1007        101          1 12-JUL-02 12.01.00.000000 PM
          1008        103          1 12-AUG-03 12.02.00.000000 PM
          1010        101          2 12-NOV-05 12.30.00.000000 PM
          1011        103          1 12-DEC-06 12.32.00.000000 PM
          1012        105          1 12-FEB-01 12.40.00.000000 PM
          1013        106          2 12-APR-02 12.44.00.000000 PM
          1014        103          1 12-JUN-03 12.01.00.000000 PM
          1015        106          1 12-JAN-05 12.05.00.000000 PM
          1016        104          2 12-NOV-03 12.28.00.000000 PM
          1017        105          1 12-MAR-02 12.31.00.000000 PM
          1019        106          3 12-JUL-03 12.49.00.000000 PM
          1020        103          1 12-JAN-04 12.51.00.000000 PM
    
    17 rows selected.
    
    SQL> rollback;
    
    Rollback complete.
    
    SQL> delete from orders
      2  where bookid > (select avg(bookid) from orders
      3                  where bookid = '103');
    
    7 rows deleted.
    
    SQL> select * from orders;
    
       ORDERID     BOOKID   QUANTITY DATEORDERED
    ---------- ---------- ---------- -------------------------------------
          1001        103          1 12-JAN-04 12.30.00.000000 PM
          1002        101          1 12-FEB-01 12.31.00.000000 PM
          1003        103          2 12-MAR-02 12.34.00.000000 PM
          1005        102          1 12-MAY-04 12.41.00.000000 PM
          1006        103          2 12-JUN-01 12.59.00.000000 PM
          1007        101          1 12-JUL-02 12.01.00.000000 PM
          1008        103          1 12-AUG-03 12.02.00.000000 PM
          1009        102          4 12-SEP-04 12.22.00.000000 PM
          1010        101          2 12-NOV-05 12.30.00.000000 PM
          1011        103          1 12-DEC-06 12.32.00.000000 PM
          1014        103          1 12-JUN-03 12.01.00.000000 PM
          1018        102          1 12-MAY-01 12.32.00.000000 PM
          1020        103          1 12-JAN-04 12.51.00.000000 PM
    
    13 rows selected.
    
    mysql> delete from orders
        -> where bookid > (select avg(bookid) from orders
        ->                 where bookid = '103');
    ERROR 1093 (HY000): You can't specify target table 'orders' for update in FROM clause
    When i use the same query for selecting in Mysql, i m getting error..As i am new to Mysql i dont know wat is the proper query...can someone let me know the correct query>

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The error message returned from MySQL will help you to understand what problem MySQL is having when it tries to execute your SQL statement or statements. Once you know what is wrong, then you can start to fix it. If you don't understand the error message that MySQL provides, post it and the exact SQL statement that you are using in MySQL here and someone may help you understand it.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    The error is indicating that you cannot reference in the same query the average and using this result in the delete. I would break this down into 2 queries:

    By the way your query does not make any sense. An average of bookid where bookid = 103 will always equal 103. In that case you do not need to do any calculations simply issue the delete as follows: DELETE FROM orders WHERE bookid > 103;

    Code:
    SELECT avg(bookid) INTO @avg FROM orders WHERE bookid = 103;
    DELETE FROM orders WHERE bookid > @avg;
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •