Results 1 to 3 of 3
  1. #1
    Join Date
    May 2013
    Posts
    33

    Unanswered: Update using Joins

    Hi , this is MYSQL query...In which , two tables are updating at the same time using joins.

    Code:
    mysql> select * from orders;
    +---------+--------+----------+---------------------+
    | OrderID | BookID | Quantity | DateOrdered         |
    +---------+--------+----------+---------------------+
    |    1001 |    103 |        1 | 2004-01-12 12:30:00 |
    |    1002 |    101 |        1 | 2001-02-12 12:31:00 |
    |    1003 |    103 |        2 | 2002-03-12 12:34:00 |
    |    1004 |    104 |        3 | 2003-04-12 12:36:00 |
    |    1005 |    102 |        1 | 2004-05-12 12:41:00 |
    |    1006 |    103 |        2 | 2001-06-12 12:59:00 |
    |    1007 |    101 |        1 | 2002-07-12 13:01:00 |
    |    1008 |    103 |        1 | 2003-08-12 13:02:00 |
    |    1009 |    102 |        4 | 2004-09-12 13:22:00 |
    |    1010 |    101 |        2 | 2005-11-12 13:30:00 |
    |    1011 |    103 |        1 | 2006-12-12 13:32:00 |
    |    1012 |    105 |        1 | 2001-02-12 13:40:00 |
    |    1013 |    106 |        2 | 2002-04-12 13:44:00 |
    |    1014 |    103 |        1 | 2003-06-12 14:01:00 |
    |    1015 |    106 |        1 | 2005-01-12 14:05:00 |
    |    1016 |    104 |        2 | 2003-11-12 14:28:00 |
    |    1017 |    105 |        1 | 2002-03-12 14:31:00 |
    |    1018 |    102 |        1 | 2001-05-12 14:32:00 |
    |    1019 |    106 |        3 | 2003-07-12 14:49:00 |
    |    1020 |    103 |        1 | 2004-01-12 14:51:00 |
    +---------+--------+----------+---------------------+
    20 rows in set (0.00 sec)
    
    mysql> select * from books;
    +--------+----------+---------+
    | BookID | BookName | InStock |
    +--------+----------+---------+
    |    101 | Writing  |      11 |
    |    102 | News     |      17 |
    |    103 | Angels   |      23 |
    |    104 | Poet     |      32 |
    |    105 | Dunces   |       6 |
    |    106 | Solitude |      28 |
    +--------+----------+---------+
    6 rows in set (0.00 sec)
    
    mysql> UPDATE Books, Orders
        -> SET Orders.Quantity=Orders.Quantity+2,
        ->    Books.InStock=Books.InStock-2
        -> WHERE Books.BookID=Orders.BookID
        ->    AND Orders.OrderID = 1002;
    Query OK, 2 rows affected (0.00 sec)
    Rows matched: 2  Changed: 2  Warnings: 0
    
    mysql> select * from books;
    +--------+----------+---------+
    | BookID | BookName | InStock |
    +--------+----------+---------+
    |    101 | Writing  |       9 |
    |    102 | News     |      17 |
    |    103 | Angels   |      23 |
    |    104 | Poet     |      32 |
    |    105 | Dunces   |       6 |
    |    106 | Solitude |      28 |
    +--------+----------+---------+
    6 rows in set (0.00 sec)
    
    mysql> select * from orders;
    
    +---------+--------+----------+---------------------+
    | OrderID | BookID | Quantity | DateOrdered         |
    +---------+--------+----------+---------------------+
    |    1001 |    103 |        1 | 2004-01-12 12:30:00 |
    |    1002 |    101 |        3 | 2013-07-27 16:58:37 |
    |    1003 |    103 |        2 | 2002-03-12 12:34:00 |
    |    1004 |    104 |        3 | 2003-04-12 12:36:00 |
    |    1005 |    102 |        1 | 2004-05-12 12:41:00 |
    |    1006 |    103 |        2 | 2001-06-12 12:59:00 |
    |    1007 |    101 |        1 | 2002-07-12 13:01:00 |
    |    1008 |    103 |        1 | 2003-08-12 13:02:00 |
    |    1009 |    102 |        4 | 2004-09-12 13:22:00 |
    |    1010 |    101 |        2 | 2005-11-12 13:30:00 |
    |    1011 |    103 |        1 | 2006-12-12 13:32:00 |
    |    1012 |    105 |        1 | 2001-02-12 13:40:00 |
    |    1013 |    106 |        2 | 2002-04-12 13:44:00 |
    |    1014 |    103 |        1 | 2003-06-12 14:01:00 |
    |    1015 |    106 |        1 | 2005-01-12 14:05:00 |
    |    1016 |    104 |        2 | 2003-11-12 14:28:00 |
    |    1017 |    105 |        1 | 2002-03-12 14:31:00 |
    |    1018 |    102 |        1 | 2001-05-12 14:32:00 |
    |    1019 |    106 |        3 | 2003-07-12 14:49:00 |
    |    1020 |    103 |        1 | 2004-01-12 14:51:00 |
    +---------+--------+----------+---------------------+
    20 rows in set (0.00 sec)

    I want query for the same in oracle SQL....Help me to update the tables.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    UPDATE, in Oracle, updates one table at the moment, so you'll need 2 UPDATE statements - one to update BOOKS, another to update ORDERS table. WHERE clause will restrict records to be updated.

  3. #3
    Join Date
    May 2013
    Posts
    33
    can update only one table ah....k got it...thanks littlefoot.

Posting Permissions

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