Replacing multiple statements with a MERGE
in [DB2]
|
From: Lennart Jonsson on 28 Jun 2010 19:17 On 2010-06-25 19:47, --CELKO-- wrote: > Here is a problem that has me stuck. This is a skeleton of an > auction. We start with a simple table of bids for the items. The key > is the item's identifier and sequential bid number within each time. > To give the table a key when an item is put on auction, we put in a > dummy bid and amount. > > CREATE TABLE Auctions > (item_id CHAR(8) NOT NULL, > bid_seq INTEGER DEFAULT 0 NOT NULL > CHECK (bid_seq >= 0), > PRIMARY KEY (item_id, bid_seq), > bid_amt DECIMAL (8,2) DEFAULT 0.00 NOT NULL > CHECK (bid_amt >= 0.00)); > > INSERT INTO Auctions (item_id) > VALUES ('Item A'), ('Item B'), ('Item C'); > > Assume a single bidder so that the Actions tables does not need a > bidder_id column as part of the key. He puts in a list of his bids on > the items on auction. > > CREATE TABLE Bid_List > (item_id CHAR(8) NOT NULL PRIMARY KEY, > bid_amt DECIMAL (8,2) NOT NULL > CHECK (bid_amt >= 0.00)); > > INSERT INTO Bid_List (item_id, bid_amt) > VALUES ('Item A', 14.55), ('Item C', 13.50); > > Notice that he cannot make two bids on the same item thanks to the key > and cannot bid a negative amount (he can do charity work for free, > however). There is no need to use IDENTITY or @@IDENTITY (row at a > time numbering) to get multiple items sequentially numbered. > > INSERT INTO Auctions (item_id, bid_seq, bid_amt) > SELECT DISTINCT Bid_List.item_id, MAX(Auctions.bid_seq) OVER > (PARTITION BY Auctions.item_id)+1, Bid_List.bid_amt > FROM Auctions, Bid_List > WHERE Auctions.item_id = Bid_List.item_id; > > item_id bid_seq bid_amt > ========================= > Item A 0 0.00 > Item A 1 14.55 > Item B 0 0.00 > Item B 1 13.50 > Item C 0 0.00 > > Obviously it is easy to have VIEWs to show the items with and without > a bid, the highest current bid, etc. When some of the items have real > bids, the dummy bids can be dropped to save space and make other > computations in the real system much easier. To clean out the dummy > bids once we get a real bid can be done with way: > > DELETE FROM Auctions > WHERE bid_seq = 0 > AND EXISTS > (SELECT * > FROM Auctions AS A1 > WHERE Auctions.item_id = A1.item_id > AND bid_seq > 0); > > item_id bid_seq bid_amt > ========================= > Item A 1 14.55 > Item B 1 13.50 > Item C 0 0.00 > > There might be a way to put all of this code into a single MERGE > statement. I cannot figure it out. Anyone see a solution? Not very pretty,and I'm not sure it will hold for all cases, but: MERGE INTO Auctions a USING ( select item_id, 0 as bid_seq, bid_amt, coalesce((select max(bid_seq) from Auctions x where x.item_id = y.item_id),0) + 1 as next_bid_seq from Bid_List y ) b on (a.item_id, a.bid_seq) = (b.item_id, b.bid_seq) when matched then update set (bid_seq, bid_amt) = (1, b.bid_amt) when not matched then insert (item_id, bid_seq, bid_amt) values (b.item_id, b.next_bid_seq, b.bid_amt); /Lennart
From: Lennart Jonsson on 28 Jun 2010 19:33 On 2010-06-29 01:17, Lennart Jonsson wrote: [...] > MERGE INTO Auctions a > USING ( > select item_id, 0 as bid_seq, bid_amt, > coalesce((select max(bid_seq) > from Auctions x > where x.item_id = y.item_id),0) + 1 as next_bid_seq > from Bid_List y > ) b on (a.item_id, a.bid_seq) = (b.item_id, b.bid_seq) > when matched then > update set (bid_seq, bid_amt) = (1, b.bid_amt) > when not matched then > insert (item_id, bid_seq, bid_amt) > values (b.item_id, b.next_bid_seq, b.bid_amt); > coalesce can be removed since it is asserted that there is at least 1 bid per item, and the constant 1 can be replaced with next_bid_seq. It may also make it clearer to name the 0 as init_bid_seq or even dummy_bid_seq MERGE INTO Auctions a USING ( select item_id, 0 as dummy_bid_seq, bid_amt, (select max(bid_seq) from Auctions x where x.item_id = y.item_id) + 1 as next_bid_seq from Bid_List y ) b on (a.item_id, a.bid_seq) = (b.item_id, b.dummy_bid_seq) when matched then update set (bid_seq, bid_amt) = (b.next_bid_seq, b.bid_amt) when not matched then insert (item_id, bid_seq, bid_amt) values (b.item_id, b.next_bid_seq, b.bid_amt); /Lennart
From: Tonkuma on 30 Jun 2010 02:49 On Jun 29, 8:33 am, Lennart Jonsson <erik.lennart.jons...(a)gmail.com> wrote: > .... > coalesce can be removed since it is asserted that there is at least 1 > bid per item, and the constant 1 can be replaced with next_bid_seq. It > may also make it clearer to name the 0 as init_bid_seq or even dummy_bid_seq > .... The problem might be how to guarantee the existence of all item_id in Bid_List were in Auctions. If there was a row in Bid_list which item_id was not in Auctions, COALESCE would be necessary. For example: (Item E in Bid_list is not in Auctions.) ------------------------------ Commands Entered ------------------------------ SELECT * FROM Auctions ORDER BY item_id , bid_seq; ------------------------------------------------------------------------------ ITEM_ID BID_SEQ BID_AMT -------- ----------- ---------- Item A 1 14.55 Item A 2 16.95 Item B 1 20.05 Item C 1 13.50 Item D 0 0.00 5 record(s) selected. ------------------------------ Commands Entered ------------------------------ SELECT * FROM Bid_List; ------------------------------------------------------------------------------ ITEM_ID BID_AMT -------- ---------- Item A 17.05 Item B 20.15 Item D 7.05 Item E 9.99 4 record(s) selected. ------------------------------ Commands Entered ------------------------------ MERGE INTO Auctions a USING ( select item_id, 0 as dummy_bid_seq, bid_amt, (select max(bid_seq) from Auctions x where x.item_id = y.item_id) + 1 as next_bid_seq from Bid_List y ) b on (a.item_id, a.bid_seq) = (b.item_id, b.dummy_bid_seq) when matched then update set (bid_seq, bid_amt) = (b.next_bid_seq, b.bid_amt) when not matched then insert (item_id, bid_seq, bid_amt) values (b.item_id, b.next_bid_seq, b.bid_amt); ------------------------------------------------------------------------------ DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0407N Assignment of a NULL value to a NOT NULL column "TBSPACEID=3, TABLEID=89, COLNO=1" is not allowed. SQLSTATE=23502 If COALESCE was used, Item E was added in Auctions. ------------------------------ Commands Entered ------------------------------ MERGE INTO Auctions a USING ( select item_id, 0 as bid_seq, bid_amt, coalesce((select max(bid_seq) from Auctions x where x.item_id = y.item_id),0) + 1 as next_bid_seq from Bid_List y ) b on (a.item_id, a.bid_seq) = (b.item_id, b.bid_seq) when matched then update set (bid_seq, bid_amt) = (1, b.bid_amt) when not matched then insert (item_id, bid_seq, bid_amt) values (b.item_id, b.next_bid_seq, b.bid_amt); ------------------------------------------------------------------------------ DB20000I The SQL command completed successfully. ------------------------------ Commands Entered ------------------------------ SELECT * FROM Auctions ORDER BY item_id , bid_seq; ------------------------------------------------------------------------------ ITEM_ID BID_SEQ BID_AMT -------- ----------- ---------- Item A 1 14.55 Item A 2 16.95 Item A 3 17.05 Item B 1 20.05 Item B 2 20.15 Item C 1 13.50 Item D 1 7.05 Item E 1 9.99 8 record(s) selected.
From: Tonkuma on 30 Jun 2010 02:54 On Jun 30, 3:49 pm, Tonkuma <tonk...(a)fiberbit.net> wrote: > On Jun 29, 8:33 am, Lennart Jonsson <erik.lennart.jons...(a)gmail.com> > wrote:> .... > > coalesce can be removed since it is asserted that there is at least 1 > > bid per item, and the constant 1 can be replaced with next_bid_seq. It > > may also make it clearer to name the 0 as init_bid_seq or even dummy_bid_seq > > .... > > The problem might be how to guarantee the existence of all item_id in > Bid_List were in Auctions. > > If there was a row in Bid_list which item_id was not in Auctions, > COALESCE would be necessary. > For example: > (Item E in Bid_list is not in Auctions.) > ------------------------------ Commands Entered > ------------------------------ > SELECT * FROM Auctions > ORDER BY item_id , bid_seq; > ------------------------------------------------------------------------------ > > ITEM_ID BID_SEQ BID_AMT > -------- ----------- ---------- > Item A 1 14.55 > Item A 2 16.95 > Item B 1 20.05 > Item C 1 13.50 > Item D 0 0.00 > > 5 record(s) selected. > > ------------------------------ Commands Entered > ------------------------------ > SELECT * FROM Bid_List; > ------------------------------------------------------------------------------ > > ITEM_ID BID_AMT > -------- ---------- > Item A 17.05 > Item B 20.15 > Item D 7.05 > Item E 9.99 > > 4 record(s) selected. > Here is an example which ignore "Item E". ------------------------------ Commands Entered ------------------------------ MERGE INTO Auctions a USING Bid_List b ON b.item_id = a.item_id AND a.bid_seq = 0 WHEN MATCHED THEN UPDATE SET (bid_seq , bid_amt) = (1 , b.bid_amt) WHEN NOT MATCHED AND EXISTS (SELECT 0 FROM Auctions e WHERE e.item_id = b.item_id ) THEN INSERT ( item_id , bid_seq , bid_amt ) VALUES ( b.item_id , (SELECT MAX(bid_seq) + 1 FROM Auctions m WHERE m.item_id = b.item_id) , b.bid_amt ) ; ------------------------------------------------------------------------------ DB20000I The SQL command completed successfully. ------------------------------ Commands Entered ------------------------------ SELECT * FROM Auctions ORDER BY item_id , bid_seq ; ------------------------------------------------------------------------------ ITEM_ID BID_SEQ BID_AMT -------- ----------- ---------- Item A 1 14.55 Item A 2 16.95 Item A 3 17.05 Item B 1 20.05 Item B 2 20.15 Item C 1 13.50 Item D 1 7.05 7 record(s) selected.
From: Will Honea on 30 Jun 2010 17:20
Tonkuma wrote: > WHEN NOT MATCHED > AND EXISTS > (SELECT 0 > FROM Auctions e > WHERE e.item_id = b.item_id > ) THEN > INSERT Not the same issue, but I had never considered the syntax until I saw this. The same structure works to avoid an annoying "more than on row identified for update" bomb that has been plaguing me for a while now. Serendipity, indeed. Thank you. -- Will Honea |