Prev: Insert a one returned value from a MAX function in SSRS in Footer
Next: Search and Replace within string
From: --CELKO-- on 25 Jun 2010 13:48
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?
From: Eric Isaacs on 25 Jun 2010 16:52
Joe,

I would create an Items table and a Bids table and separate them. I
think that might simplify things for you. The bids would be the
actual bids and you wouldn't need dummy data to create the records.

-Eric Isaacs
From: Erland Sommarskog on 26 Jun 2010 09:14
--CELKO-- (jcelko212(a)earthlink.net) writes:
> 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.

I assume that your real-world case is different, because this sounds
like a poor database design to me. Surely there is information about
the items that calls for a table of their own.

Overall, dummy rows is something I like to avoid, but surely I've made
myself guilty to those myself. Sometimes it is the best solution. But
in this particular case, since an item at an auction usually have a
starting price, and it could make sense to include it in the bid list -
and keep it there.

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

Once I have tricky data model like this, my preference is to have
the DELETE in a trigger, so that no matter what operations that are
performed on the table, the business rule is respected.

But if you want to do it in a MERGE, this is possible, but it is
a little messy:

WITH newbids (item_id, bid_amt, bid_seq) AS (
SELECT DISTINCT B.item_id, B.bid_amt,
MAX(A.bid_seq) OVER (PARTITION BY A.item_id) + 1
FROM Auctions A
JOIN Bid_List B ON A.item_id = B.item_id
)
MERGE Auctions A
USING newbids AS B ON A.item_id = B.item_id
AND A.bid_seq = B.bid_seq
WHEN NOT MATCHED BY TARGET THEN
INSERT (item_id, bid_seq, bid_amt)
VALUES (B.item_id, B.bid_seq, B.bid_amt)
WHEN NOT MATCHED BY SOURCE AND A.bid_seq = 0 AND
EXISTS (SELECT *
FROM newbids B2
WHERE A.item_id = B2.item_id) THEN
DELETE
;


An alternative that avoids the subquery is:

WITH newbids (item_id, bid_amt, bid_seq, first_seq) AS (
SELECT DISTINCT B.item_id, B.bid_amt,
MAX(A.bid_seq) OVER (PARTITION BY A.item_id) + 1,
MIN(A.bid_seq) OVER (PARTITION BY A.item_id)
FROM Auctions A
JOIN Bid_List B ON A.item_id = B.item_id
)
MERGE Auctions A
USING newbids AS B ON A.item_id = B.item_id
WHEN MATCHED THEN
INSERT (item_id, bid_seq, bid_amt)
VALUES (B.item_id, B.bid_seq, B.bid_amt)
WHEN MATCHED AND A.bid_seq = 0 AND B.first_seq = 0 THEN
DELETE
;

But alas:

Msg 10711, Level 15, State 1, Line 10
An action of type 'INSERT' is not allowed in the 'WHEN MATCHED' clause of
a MERGE statement.



--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: Erland Sommarskog on 26 Jun 2010 13:33
My working query had an imperfection; there is no need to refer to the
CTE in the subquery in the NOT MATCHED BY SOURCE clause:

WITH newbids (item_id, bid_amt, bid_seq) AS (
SELECT DISTINCT B.item_id, B.bid_amt,
MAX(A.bid_seq) OVER (PARTITION BY A.item_id) + 1
FROM Auctions A
JOIN Bid_List B ON A.item_id = B.item_id
)
MERGE Auctions A
USING newbids AS B ON A.item_id = B.item_id
AND A.bid_seq = B.bid_seq
WHEN NOT MATCHED BY TARGET THEN
INSERT (item_id, bid_seq, bid_amt)
VALUES (B.item_id, B.bid_seq, B.bid_amt)
WHEN NOT MATCHED BY SOURCE AND A.bid_seq = 0 AND
EXISTS (SELECT *
FROM Bid_List B2
WHERE A.item_id = B2.item_id) THEN
DELETE
;


Here are two more ways:

WITH newbids (type, item_id, bid_amt, bid_seq) AS (
SELECT DISTINCT 'N', B.item_id, B.bid_amt,
MAX(A.bid_seq) OVER (PARTITION BY A.item_id) + 1
FROM Auctions A
JOIN Bid_List B ON A.item_id = B.item_id
UNION ALL
SELECT 'X', A.item_id, NULL, 0
FROM Auctions A
WHERE A.bid_seq = 0
AND EXISTS (SELECT *
FROM Bid_List B
WHERE A.item_id = B.item_id)
)
MERGE Auctions A
USING newbids AS B ON A.item_id = B.item_id
AND A.bid_seq = B.bid_seq
WHEN NOT MATCHED BY TARGET THEN
INSERT (item_id, bid_seq, bid_amt)
VALUES (B.item_id, B.bid_seq, B.bid_amt)
WHEN MATCHED AND A.bid_seq = 0 AND B.bid_seq = 0 THEN
DELETE
;


WITH newbids (item_id, bid_amt, bid_seq) AS (
SELECT DISTINCT B.item_id, B.bid_amt,
MAX(A.bid_seq) OVER (PARTITION BY A.item_id) + 1
FROM Auctions A
JOIN Bid_List B ON A.item_id = B.item_id
),
affectedauctions AS (
SELECT *
FROM Auctions A
WHERE EXISTS (SELECT *
FROM Bid_List B
WHERE A.item_id = B.item_id)
)
MERGE affectedauctions AS A
USING newbids AS B ON A.item_id = B.item_id
AND A.bid_seq = B.bid_seq
WHEN NOT MATCHED BY TARGET THEN
INSERT (item_id, bid_seq, bid_amt)
VALUES (B.item_id, B.bid_seq, B.bid_amt)
WHEN NOT MATCHED BY SOURCE THEN
DELETE
;

As you can see, all are really the same idea, I just move that subquery
around. I vaguely recall that NOT MATCHED BY SOURCE is a Microsoft
extention. In that case, the middle query may be the most palatable
to you.

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: Peso on 28 Jun 2010 05:31
Unless there is some restriction against UPDATE together with MERGE, this
will do.
Or have I misunderstood something?


;WITH cteYak(item_id, bid_seq, bid_amt, bid_age)
AS (
SELECT item_id,
ROW_NUMBER() OVER (PARTITION BY item_id ORDER BY bid_amt) AS
bid_seq,
bid_amt,
ROW_NUMBER() OVER (PARTITION BY item_id ORDER BY bid_amt DESC) AS
bid_age
FROM bid_list
)
MERGE Auctions AS tgt
USING (
SELECT item_id,
bid_seq,
bid_amt
FROM cteYak
WHERE bid_age = 1
) AS src ON src.item_id = tgt.item_id
WHEN MATCHED
THEN UPDATE
SET tgt.bid_seq = src.bid_seq,
tgt.bid_amt = src.bid_amt
WHEN NOT MATCHED BY TARGET
THEN INSERT (
item_id,
bid_seq,
bid_amt
)
VALUES (
src.item_id,
src.bid_seq,
src.bid_amt
);


//Peso




"--CELKO--" wrote in message
news:79dfa7b5-b313-423a-b49a-f8a3bdfc1fdf(a)d37g2000yqm.googlegroups.com...

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?

 |  Next  |  Last
Pages: 1 2
Prev: Insert a one returned value from a MAX function in SSRS in Footer
Next: Search and Replace within string