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?

If you insert into Auctions by the following statement,
dummy rows(with bid_amt = 0) and delete statement for the rows will be
not necessary.

INSERT INTO Auctions
(item_id , bid_seq , bid_amt)
SELECT item_id
, COALESCE( (SELECT MAX(A.bid_seq)
FROM Auctions A
WHERE A.item_id = B.item_id)
, 0
) + 1
, bid_amt
FROM Bid_List B
;

auction, according to normalization theory.

Like this:
CREATE TABLE Items
( item_id CHAR(8) NOT NULL PRIMARY KEY
);

INSERT INTO Items
VALUES 'Item A' , 'Item B' , 'Item C';

ALTER TABLE Auctions
ADD CONSTRAINT auctions_ref_items
FOREIGN KEY(item_id) REFERENCES Items;

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.

On 2010-06-28 18:37, --CELKO-- wrote:
[...]
The 2006-2008 Draft is availible here:

http://www.wiscorp.com/SQLStandards.html


/Lennart

On 2010-06-25 19:47, --CELKO-- wrote:
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

On 2010-06-29 01:17, Lennart Jonsson 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

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

On Jun 29, 8:33*am, Lennart Jonsson <erik.lennart.jons... (AT) gmail (DOT) com>
wrote:
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.

On Jun 30, 3:49*pm, Tonkuma <tonk... (AT) fiberbit (DOT) net> wrote:

------------------------------ 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.

Tonkuma wrote:

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