Prev: Insert a one returned value from a MAX function in SSRS in Footer
Next: Search and Replace within string
From: Peso on 28 Jun 2010 05:51
Now I think I have understood the requirements correctly.

MERGE Auctions AS tgt
USING bid_list AS src ON src.item_id = tgt.item_id
WHEN MATCHED AND tgt.bid_amt < src.bid_amt
THEN UPDATE
SET tgt.bid_seq = tgt.bid_seq + 1,
--tgt.bid_seq += 1, -- New compound operator style, may not
be supported on all platforms
tgt.bid_amt = src.bid_amt
-- This part is only needed since there is no foreign key constraint between
Auction and bid_list. Design flaw?
-- This section adds an item in Auction table if there is a bid on it in
bid_list table, but the item is missing in Auction table.
WHEN NOT MATCHED BY TARGET
THEN INSERT (
item_id,
bid_seq,
bid_amt
)
VALUES (
src.item_id,
1,
src.bid_amt
);

From: Peso on 28 Jun 2010 06:08
If there is a foreign key constraint between Auction and bid_list table,
this may be sufficient

UPDATE tgt
SET tgt.bid_seq = tgt.bid_seq + 1,
tgt.bid_amt = src.bid_amt
FROM Auctions AS tgt
INNER JOIN bid_list AS src ON src.item_id = tgt.item_id
WHERE tgt.bid_amt < src.bid_amt

It works on SQL Server but maybe not on other platforms. I can't be that
hard to rewrite it.

//Peso

From: --CELKO-- on 28 Jun 2010 12:36
This is a very bony skeleton. The bids also have creation and
expiration dates, there is an unknown number of bidders, bids and
offers can be retracted, etc.

There are actually no physical items; what I am calling an auction is
a request for services (“I need to move a one-room apartment from
Austin to New York City. Who wants to make an offer?”). Since there is
no inventory, we did not go with an Items table and have a dummy place
holder in the design. The item is created by being offered. Having
them all in one table makes some computations easier (service fees,
even if no bid is made; time before a bid is made; step size in bid
sequence; etc.)

The MERGE statement defined in the SQL:2003 Standard permitted at most
one WHEN MATCHED and at most one WHEN NOT MATCHED clause. You are also
limited to UPDATE and INSERT actions (no DELETE) and an INNER JOIN in
the ON clause.

Implementations of MERGE are all over the place. INFORMIX, DB2, Oracle
SQL Anywhere and MS-SQL Server 2008 are all a little different. Oh,
you cannot use a CTE with a MERGE in Standard SQL and other SQL
products, so the source has to be a derived table.

I don't have the draft of the SQL:2006 Standard with me right now; I
hope it is stronger now. The SOURCE and TARGET extensions look look
good, even if they hide a FULL OUTER JOIN under the covers.

I was thinking that the first bid would update the dummy from (bid_seq
= 0) to (bid_seq = 1), and then other bids would be inserted, so I
would have portable code. I completely missed the MIN(bid_seq) OVER()
trick.
First  |  Prev  | 
Pages: 1 2
Prev: Insert a one returned value from a MAX function in SSRS in Footer
Next: Search and Replace within string