SQL query for data migration

Question

I have a table.

CREATE TABLE StockItem
(
    id        NUMBER(8) NOT NULL,
    book_id   NUMBER(8) NOT NULL,
    wh_id     NUMBER(8) NOT NULL,
    quantity  NUMBER(4) NOT NULL,
    price     NUMBER(8,2) NOT NULL
);

ALTER TABLE StockItem
    ADD CONSTRAINT UQ_StockItem UNIQUE (book_id, wh_id);
ALTER TABLE StockItem ADD CONSTRAINT PK_StockItem 
    PRIMARY KEY (id);
ALTER TABLE StockItem ADD CONSTRAINT FK_StockItem_Book 
    FOREIGN KEY (book_id) REFERENCES Book (id);
ALTER TABLE StockItem ADD CONSTRAINT FK_StockItem_Warehouse 
    FOREIGN KEY (wh_id) REFERENCES Warehouse (id);

Now suppose wh_id = 1 is closed and I should move all the books to wh_id = 2. Book prices for wh_id = 2 should be the same as they were in wh_id = 1 and quantity should be calculated properly (e.g. increased for the same book_id for two werehouses). How can I do that?

UPD:

Here is how I can get new quantity values for the books.

SELECT BOOK_ID, SUM(QUANTITY) 
FROM STOCKITEM
WHERE WH_ID IN (1, 2)
GROUP BY BOOK_ID;

Now I should update them somehow.

UPDATE STOCKITEM
SET QUANTITY = /* My summed quantity */
WHERE BOOK_ID = /* Book ids from 1 and 2 wh */

Is this what you are looking for? You spcify your `BOOK_ID` and it updates it with the count. `UPDATE STOCKITEM SET QUANTITY = (SELECT COUNT(*) FROM STOCKITEM WHERE BOOK_ID = yourBookId) WHERE BOOK_ID = /* Book ids from 1 and 2 wh */` — David Starkey, Mar 26 '13 at 16:22
Not quite. There is a field called quantity. select count(*) is not a quantity. I will try to explain. If I have the same book in two different werehouses with wh_id = 1 and wh_id = 2 then wh2_quantity = wh1_quantity + wh2_quantity. I got this value from my select request above in column SUM (QUANTITY). Next thing I need is to update the rows with book_ids from my select query and set new quantity values (i.e. update books quantity on wh2). I don't know how. :( — Pavel Shchegolevatykh, Mar 26 '13 at 16:39

score 1 · Answer 1 · edited May 23 '17 at 11:53

UPDATE (SELECT SI2.quantity AS qty2, SI2.price as p2, SI1.quantity as qty1, SISI1.price as p1
        FROM   StockItem SI2 INNER JOIN StockItem SI1 
        ON     (SI2.book_id = SI1.book_id)
        WHERE  SI2.wh_id = 2 AND SI1.wh_id = 1) WH
SET WH.qty2 = WH.qty2 + WH.qty1,
    WH.p2 = WH.p1

This answer is based on Update statement with inner join on Oracle and it requires Oracle to accept WH as updatable. I can't test.

score 1 · Accepted Answer · answered Mar 26 '13 at 16:49

I think you should do an INNER JOIN with your SELECT statement:

UPDATE stockItem 
INNER JOIN (SELECT book_id, SUM(quantity) AS total 
            FROM stockItem WHERE wh_id IN (1, 2)
            GROUP BY book_id) AS qntyBooks 
  ON qntyBooks.book_id = stockItem.book_id 
SET 
  stockItem.quantity = qntyBooks.total, 
  stockItem.wh_id = 2; -- all wh_id in 1, 2 will change to 2

I tested on MySQL (I think may be the same to Oracle11g) and worked like this:

Before update:

 ---- --------- ------- ---------- ------- 
| id | book_id | wh_id | quantity | price |
 ---- --------- ------- ---------- ------- 
|  1 |       1 |     1 |        3 |    10 |
|  2 |       2 |     1 |        3 |    20 |
|  3 |       3 |     1 |        3 |    30 |
|  4 |       1 |     2 |        2 |    40 |
|  5 |       2 |     2 |        2 |    50 |
 ---- --------- ------- ---------- -------

After update:

 ---- --------- ------- ---------- ------- 
| id | book_id | wh_id | quantity | price |
 ---- --------- ------- ---------- ------- 
|  1 |       1 |     2 |        5 |    10 |
|  2 |       2 |     2 |        5 |    20 |
|  3 |       3 |     2 |        3 |    30 |
|  4 |       1 |     2 |        5 |    40 |
|  5 |       2 |     2 |        5 |    50 |
 ---- --------- ------- ---------- -------

I hope it is what you want. Good luck!

Oracle doesn't support INNER JOINs within UPDATE statement. Searching for solution to replace this statement. Thank you. — Pavel Shchegolevatykh, Mar 26 '13 at 17:14

score 1 · Answer 3 · answered Mar 26 '13 at 17:00

1

With your current database design, you can't. The reason is CONSTRAINT UQ_StockItem UNIQUE (book_id, wh_id).

If you have book_id = 1 at price = $1.00 in warehouse 1, and book_id = 1 at price = $1.05 in warehouse 2, this constraint will prevent you from moving the book from one warehouse to another while maintaining the original price.

Something's gotta give.

answered Mar 26 '13 at 17:00

Dan Bracuk

20,699
4
26
43

I should set price from warehouse#1 and forget the price that was originally in wh#2. I don't have to maintain both of them. – Pavel Shchegolevatykh Mar 26 '13 at 17:12

SQL query for data migration

3 Answers3