August 3, 2007 at 2:02 am
I have this datas...
CREATE TABLE Query_Result (
id INTEGER,
order_id INTEGER,
supplierCode INTEGER,
slip_no INTEGER);
INSERT INTO Query_Result (id, order_id, supplierCode, slip_no) VALUES (4426,923,4375,null);
INSERT INTO Query_Result (id, order_id, supplierCode, slip_no) VALUES (5380,923,4375,null);
INSERT INTO Query_Result (id, order_id, supplierCode, slip_no) VALUES (4583,954,4375,null);
INSERT INTO Query_Result (id, order_id, supplierCode, slip_no) VALUES (4580,954,7468,null);
INSERT INTO Query_Result (id, order_id, supplierCode, slip_no) VALUES (4581,954,7468,null);
INSERT INTO Query_Result (id, order_id, supplierCode, slip_no) VALUES (4665,970,4375,null);
INSERT INTO Query_Result (id, order_id, supplierCode, slip_no) VALUES (4662,970,7468,null);
INSERT INTO Query_Result (id, order_id, supplierCode, slip_no) VALUES (4663,970,7468,null);
INSERT INTO Query_Result (id, order_id, supplierCode, slip_no) VALUES (4664,970,7468,null);
INSERT INTO Query_Result (id, order_id, supplierCode, slip_no) VALUES (4930,1012,2342,null);
INSERT INTO Query_Result (id, order_id, supplierCode, slip_no) VALUES (4931,1012,2342,null);
INSERT INTO Query_Result (id, order_id, supplierCode, slip_no) VALUES (4932,1012,2342,null);
INSERT INTO Query_Result (id, order_id, supplierCode, slip_no) VALUES (4933,1012,7468,null);
INSERT INTO Query_Result (id, order_id, supplierCode, slip_no) VALUES (4961,1017,4375,null);
INSERT INTO Query_Result (id, order_id, supplierCode, slip_no) VALUES (4962,1017,4375,null);
INSERT INTO Query_Result (id, order_id, supplierCode, slip_no) VALUES (5090,1040,2343,null);
INSERT INTO Query_Result (id, order_id, supplierCode, slip_no) VALUES (5086,1040,4375,null);
INSERT INTO Query_Result (id, order_id, supplierCode, slip_no) VALUES (5087,1040,4375,null);
INSERT INTO Query_Result (id, order_id, supplierCode, slip_no) VALUES (5088,1040,4375,null);
select * from Query_Result
drop table Query_Result
Records are group by order_id and supplierCode, On first execution the update statement,
what I want is to update those records who have no slip_no.
slip_no is a incremented by one starting with zero(0) and it is concatenated with order_id.
desired result should be..
CREATE TABLE Query_Result (
id INTEGER,
order_id INTEGER,
supplierCode INTEGER,
slip_no INTEGER);
INSERT INTO Query_Result (id, order_id, supplierCode, slip_no) VALUES (4426,923,4375,9230);
INSERT INTO Query_Result (id, order_id, supplierCode, slip_no) VALUES (5380,923,4375,9230);
INSERT INTO Query_Result (id, order_id, supplierCode, slip_no) VALUES (4583,954,4375,9540);
INSERT INTO Query_Result (id, order_id, supplierCode, slip_no) VALUES (4580,954,7468,9541);
INSERT INTO Query_Result (id, order_id, supplierCode, slip_no) VALUES (4581,954,7468,9541);
INSERT INTO Query_Result (id, order_id, supplierCode, slip_no) VALUES (4665,970,4375,9700);
INSERT INTO Query_Result (id, order_id, supplierCode, slip_no) VALUES (4662,970,7468,9701);
INSERT INTO Query_Result (id, order_id, supplierCode, slip_no) VALUES (4663,970,7468,9701);
INSERT INTO Query_Result (id, order_id, supplierCode, slip_no) VALUES (4664,970,7468,9701);
INSERT INTO Query_Result (id, order_id, supplierCode, slip_no) VALUES (4930,1012,2342,10120);
INSERT INTO Query_Result (id, order_id, supplierCode, slip_no) VALUES (4931,1012,2342,10120);
INSERT INTO Query_Result (id, order_id, supplierCode, slip_no) VALUES (4932,1012,2342,10120);
INSERT INTO Query_Result (id, order_id, supplierCode, slip_no) VALUES (4933,1012,7468,10121);
INSERT INTO Query_Result (id, order_id, supplierCode, slip_no) VALUES (4961,1017,4375,10170);
INSERT INTO Query_Result (id, order_id, supplierCode, slip_no) VALUES (4962,1017,4375,10170);
INSERT INTO Query_Result (id, order_id, supplierCode, slip_no) VALUES (5090,1040,2343,10400);
INSERT INTO Query_Result (id, order_id, supplierCode, slip_no) VALUES (5086,1040,4375,10401);
INSERT INTO Query_Result (id, order_id, supplierCode, slip_no) VALUES (5087,1040,4375,10401);
INSERT INTO Query_Result (id, order_id, supplierCode, slip_no) VALUES (5088,1040,4375,10401);
select * from Query_Result
drop table Query_Result
however if there is a new inserted record.
INSERT INTO Query_Result (id, order_id, supplierCode, slip_no) VALUES (4582,954,7468,null);
slip_no is incremented. Incrementation also happens if theres new record.
New result is this...
CREATE TABLE Query_Result (
id INTEGER,
order_id INTEGER,
supplierCode INTEGER,
slip_no INTEGER);
INSERT INTO Query_Result (id, order_id, supplierCode, slip_no) VALUES (4426,923,4375,9230);
INSERT INTO Query_Result (id, order_id, supplierCode, slip_no) VALUES (5380,923,4375,9230);
INSERT INTO Query_Result (id, order_id, supplierCode, slip_no) VALUES (4583,954,4375,9540);
INSERT INTO Query_Result (id, order_id, supplierCode, slip_no) VALUES (4580,954,7468,9541);
INSERT INTO Query_Result (id, order_id, supplierCode, slip_no) VALUES (4581,954,7468,9541);
INSERT INTO Query_Result (id, order_id, supplierCode, slip_no) VALUES (4582,954,7468,9542);
INSERT INTO Query_Result (id, order_id, supplierCode, slip_no) VALUES (4665,970,4375,9700);
INSERT INTO Query_Result (id, order_id, supplierCode, slip_no) VALUES (4662,970,7468,9701);
INSERT INTO Query_Result (id, order_id, supplierCode, slip_no) VALUES (4663,970,7468,9701);
INSERT INTO Query_Result (id, order_id, supplierCode, slip_no) VALUES (4664,970,7468,9701);
INSERT INTO Query_Result (id, order_id, supplierCode, slip_no) VALUES (4930,1012,2342,10120);
INSERT INTO Query_Result (id, order_id, supplierCode, slip_no) VALUES (4931,1012,2342,10120);
INSERT INTO Query_Result (id, order_id, supplierCode, slip_no) VALUES (4932,1012,2342,10120);
INSERT INTO Query_Result (id, order_id, supplierCode, slip_no) VALUES (4933,1012,7468,10121);
INSERT INTO Query_Result (id, order_id, supplierCode, slip_no) VALUES (4961,1017,4375,10170);
INSERT INTO Query_Result (id, order_id, supplierCode, slip_no) VALUES (4962,1017,4375,10170);
INSERT INTO Query_Result (id, order_id, supplierCode, slip_no) VALUES (5090,1040,2343,10400);
INSERT INTO Query_Result (id, order_id, supplierCode, slip_no) VALUES (5086,1040,4375,10401);
INSERT INTO Query_Result (id, order_id, supplierCode, slip_no) VALUES (5087,1040,4375,10401);
INSERT INTO Query_Result (id, order_id, supplierCode, slip_no) VALUES (5088,1040,4375,10401);
select * from Query_Result
drop table Query_Result
Any Help will be greatly appreciated.
August 3, 2007 at 2:40 am
A few questions first, to help us understand what you are trying to do:
1- Which columns make your PK? (Or, what is the purpose of the id column?
2- Why do different supplier_id's share one order_id? (see records 3 & 4)
3- How many slip_nos can be inserted at most per order_id? (what happens after you've suffixed 9?) Depending on the answer to Q3, you may want to consider converting slip_no to varchar.
If order_id is not shared between supplier_id's and slip_nos won't go beyond 9, then you can use
@next_slip_no = SELECT MAX(slip_no) FROM QUERY_RESULT WHERE order_id = @this_order_id
IF @next_slip_no = 0 Then
@next_slip_no = @this_order_id * 10
--(or @this_order_id +'0' for varchar)
ELSE
@next_slip_no = @next_slip_no + 1
--(or string manipulation for varchar)
Use @next_slip_no in your INSERT statement for the value placeholder for slip_no column.
PS. I am not expert with SQL syntax, but I try not to hold back if I believe I can answer a post reasonably well in spite of that handicap. I trust others will be gentle enough to point out my coding shortfalls.
August 3, 2007 at 3:15 am
1- Which columns make your PK? (Or, what is the purpose of the id column?
column id is the PK.
2- Why do different supplier_id's share one order_id? (see records 3 & 4)
This transaction is as detail table. Different item might have different supplier on different order_id. That is why I have to create a shared slip_no on each order_id and supplier. This only happens on first update. However, on second update if there is a new record inserted. new slip_no will be incremented then.
3- How many slip_nos can be inserted at most per order_id? (what happens after you've suffixed 9?) Depending on the answer to Q3, you may want to consider converting slip_no to varchar.
order_id + '10' ... '11' ... '12' and so on. It should be handled as varchar.
August 3, 2007 at 4:41 am
1. It is not a good idea to prefix the slip_no with the order_id in the db. Do this in the front end or use a view.
2. In SQL2000 you probably need to use a temp table to generate the offsets for the UPDATE statement. The ROW_NUMBER() function in SQL2005 makes this much easier.
-- *** Test Data ***
CREATE TABLE #TestData
(
order_id int NOT NULL
,supplierCode int NOT NULL
,slip_no int NULL
)
INSERT INTO #TestData
SELECT 923, 4375, NULL UNION ALL
SELECT 923, 4375, NULL UNION ALL
SELECT 954, 4375, NULL UNION ALL
SELECT 954, 7468, NULL UNION ALL
SELECT 954, 7468, NULL UNION ALL
SELECT 970, 4375, NULL UNION ALL
SELECT 970, 7468, NULL UNION ALL
SELECT 970, 7468, NULL UNION ALL
SELECT 970, 7468, NULL UNION ALL
SELECT 1012, 2342, NULL UNION ALL
SELECT 1012, 2342, NULL UNION ALL
SELECT 1012, 2342, NULL UNION ALL
SELECT 1012, 7468, NULL UNION ALL
SELECT 1017, 4375, NULL UNION ALL
SELECT 1017, 4375, NULL UNION ALL
SELECT 1040, 2343, NULL UNION ALL
SELECT 1040, 4375, NULL UNION ALL
SELECT 1040, 4375, NULL UNION ALL
SELECT 1040, 4375, NULL
-- *** End Test Data ***
-- Update
CREATE TABLE #temp
(
RowID int IDENTITY NOT NULL
,order_id int NOT NULL
,supplierCode int NOT NULL
)
-- Put indexes on #temp if needed
INSERT INTO #temp (order_id, supplierCode)
SELECT DISTINCT order_id, supplierCode
FROM #testdata
WHERE slip_no IS NULL
ORDER BY order_id, supplierCode
OPTION (MAXDOP 1)
UPDATE TD
SET slip_no = D2.MaxSlipNo + T.RowID - D1.RowID + 1
FROM #TestData TD
JOIN #temp T
ON TD.order_id = T.order_id
AND TD.supplierCode = T.supplierCode
JOIN (
SELECT T1.order_id
,MIN(T1.RowID) AS RowID
FROM #temp T1
GROUP BY T1.order_id
) D1
ON TD.order_id = D1.order_id
JOIN (
SELECT TD1.order_id
,ISNULL(MAX(TD1.slip_no), -1) AS MaxSlipNo
FROM #TestData TD1 WITH (UPDLOCK) -- this stops duplicates/deadlocks
GROUP BY TD1.order_id
) D2
ON TD.order_id = D2.order_id
-- Insert
INSERT INTO #TestData
SELECT 954, 7468, ISNULL(MAX(TD.slip_no), -1) + 1
FROM #TestData TD WITH (UPDLOCK) -- this stops duplicates/deadlocks
WHERE TD.order_id = 954
SELECT *
,CAST(order_id AS varchar(20)) + CAST(slip_no AS varchar(20)) AS ViewSlipNo
FROM #TestData
ORDER BY order_id, supplierCode
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply