April 16, 2018 at 6:18 am
I have a table that I am trying to run mass update queries against. For every Unique Product Listed I would like to increment the ID by 1 starting at 0
For Example
CREATE TABLE BatchMasterV1 (
BatchID int NULL,
BatchNumber nvarchar(16) NULL,
ProductDesc nvarchar(32) NULL
);
INSERT INTO BatchMasterV1 (BatchID, BatchNumber, BatchDescription)
VALUES (1, '000000', 'COLOR RED'),
(2, '000001', 'COLOR RED'),
(3, '000002', 'COLOR RED'),
(4, '000012', 'COLOR RED'),
(5, '10/123', 'COLOR RED'),
(6, '10/211', 'COLOR RED'),
(7, '000000', 'COLOR BLUE123'),
(8, '0008', 'COLOR BLUE123'),
(9, '0010', 'COLOR BLUE123'),
(10, '0012', 'COLOR BLUE123'),
(11, '10/123', 'COLOR BLUE123'),
(12, '10/211', 'COLOR BLUE123'),
(13, '000000', 'COLOR YELLOW 23'),
(14, '09/23', 'COLOR YELLOW 23'),
(15, '10/01', 'COLOR YELLOW 23'),
(16, '000000', 'COLOR ORANGE 23');
The common theme is that All "000000" are what I consider Master Records. They were the original records created for each unique batch. Each of these Master records were then duplicated and end users used there own numbering convention for Batch Number. We'd like is so that after the update query is run then the table looks like the following. The Current DB Table has each Master already updated to BatchNumber "000000"
1, '000000', 'COLOR RED'
2, '000001', 'COLOR RED'
3, '000002', 'COLOR RED'
4, '000003', 'COLOR RED'
5, '000004', 'COLOR RED'
6, '000005', 'COLOR RED'
7, '000000', 'COLOR BLUE123'
8, '000001', 'COLOR BLUE123'
9, '000002', 'COLOR BLUE123'
10, '000003', 'COLOR BLUE123'
11, '1000004', 'COLOR BLUE123'
12, '000005', 'COLOR BLUE123'
13, '000000', 'COLOR YELLOW 23'
14, '000001', 'COLOR YELLOW 23'
15, '000002', 'COLOR YELLOW 23'
16, '000000', 'COLOR ORANGE 23'
The original Batch ID's are the common thread in terms of when records were created this providing an associated incremental BatchNUmber. There are some records which may only have one Master, like in the example above "COLOR ORANGE 23". Nothing would be done to these types of Records.
April 16, 2018 at 6:49 am
netguykb - Monday, April 16, 2018 6:18 AMI have a table that I am trying to run mass update queries against. For every Unique Product Listed I would like to increment the ID by 1 starting at 0For Example
CREATE TABLE BatchMasterV1 (
BatchID int NULL,
BatchNumber nvarchar(16) NULL,
ProductDesc nvarchar(32) NULL
);
INSERT INTO BatchMasterV1 (BatchID, BatchNumber, BatchDescription)
VALUES (1, '000000', 'COLOR RED'),
(2, '000001', 'COLOR RED'),
(3, '000002', 'COLOR RED'),
(4, '000012', 'COLOR RED'),
(5, '10/123', 'COLOR RED'),
(6, '10/211', 'COLOR RED'),
(7, '000000', 'COLOR BLUE123'),
(8, '0008', 'COLOR BLUE123'),
(9, '0010', 'COLOR BLUE123'),
(10, '0012', 'COLOR BLUE123'),
(11, '10/123', 'COLOR BLUE123'),
(12, '10/211', 'COLOR BLUE123'),
(13, '000000', 'COLOR YELLOW 23'),
(14, '09/23', 'COLOR YELLOW 23'),
(15, '10/01', 'COLOR YELLOW 23'),
(16, '000000', 'COLOR ORANGE 23');The common theme is that All "000000" are what I consider Master Records. They were the original records created for each unique batch. Each of these Master records were then duplicated and end users used there own numbering convention for Batch Number. We'd like is so that after the update query is run then the table looks like the following. The Current DB Table has each Master already updated to BatchNumber "000000"
1, '000000', 'COLOR RED'
2, '000001', 'COLOR RED'
3, '000002', 'COLOR RED'
4, '000003', 'COLOR RED'
5, '000004', 'COLOR RED'
6, '000005', 'COLOR RED'7, '000000', 'COLOR BLUE123'
8, '000001', 'COLOR BLUE123'
9, '000002', 'COLOR BLUE123'
10, '000003', 'COLOR BLUE123'
11, '1000004', 'COLOR BLUE123'
12, '000005', 'COLOR BLUE123'13, '000000', 'COLOR YELLOW 23'
14, '000001', 'COLOR YELLOW 23'
15, '000002', 'COLOR YELLOW 23'16, '000000', 'COLOR ORANGE 23'
The original Batch ID's are the common thread in terms of when records were created this providing an associated incremental BatchNUmber. There are some records which may only have one Master, like in the example above "COLOR ORANGE 23". Nothing would be done to these types of Records.
Something like this?DROP TABLE IF EXISTS #BatchMasterV1;
CREATE TABLE #BatchMasterV1
(
BatchID INT NULL
, BatchNumber NVARCHAR(16) NULL
, ProductDesc NVARCHAR(32) NULL
);
INSERT #BatchMasterV1 (BatchID, BatchNumber, ProductDesc)
VALUES (1, '000000', 'COLOR RED'),
(2, '000001', 'COLOR RED'),
(3, '000002', 'COLOR RED'),
(4, '000012', 'COLOR RED'),
(5, '10/123', 'COLOR RED'),
(6, '10/211', 'COLOR RED'),
(7, '000000', 'COLOR BLUE123'),
(8, '0008', 'COLOR BLUE123'),
(9, '0010', 'COLOR BLUE123'),
(10, '0012', 'COLOR BLUE123'),
(11, '10/123', 'COLOR BLUE123'),
(12, '10/211', 'COLOR BLUE123'),
(13, '000000', 'COLOR YELLOW 23'),
(14, '09/23', 'COLOR YELLOW 23'),
(15, '10/01', 'COLOR YELLOW 23'),
(16, '000000', 'COLOR ORANGE 23');
SELECT *
FROM #BatchMasterV1 bmv;
WITH Ordered
AS
(
SELECT
bmv.BatchID
, bmv.BatchNumber
, NewBatchNo = RIGHT('000000' + CAST(ROW_NUMBER() OVER (PARTITION BY bmv.ProductDesc
ORDER BY bmv.BatchID
) - 1 AS VARCHAR(6)), 6)
FROM #BatchMasterV1 bmv
)
UPDATE Ordered
SET Ordered.BatchNumber = Ordered.NewBatchNo
WHERE Ordered.BatchNumber <> Ordered.NewBatchNo;
SELECT *
FROM #BatchMasterV1 bmv;
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 16, 2018 at 6:56 am
netguykb - Monday, April 16, 2018 6:18 AMI have a table that I am trying to run mass update queries against. For every Unique Product Listed I would like to increment the ID by 1 starting at 0For Example
CREATE TABLE BatchMasterV1 (
BatchID int NULL,
BatchNumber nvarchar(16) NULL,
ProductDesc nvarchar(32) NULL
);
INSERT INTO BatchMasterV1 (BatchID, BatchNumber, BatchDescription)
VALUES (1, '000000', 'COLOR RED'),
(2, '000001', 'COLOR RED'),
(3, '000002', 'COLOR RED'),
(4, '000012', 'COLOR RED'),
(5, '10/123', 'COLOR RED'),
(6, '10/211', 'COLOR RED'),
(7, '000000', 'COLOR BLUE123'),
(8, '0008', 'COLOR BLUE123'),
(9, '0010', 'COLOR BLUE123'),
(10, '0012', 'COLOR BLUE123'),
(11, '10/123', 'COLOR BLUE123'),
(12, '10/211', 'COLOR BLUE123'),
(13, '000000', 'COLOR YELLOW 23'),
(14, '09/23', 'COLOR YELLOW 23'),
(15, '10/01', 'COLOR YELLOW 23'),
(16, '000000', 'COLOR ORANGE 23');The common theme is that All "000000" are what I consider Master Records. They were the original records created for each unique batch. Each of these Master records were then duplicated and end users used there own numbering convention for Batch Number. We'd like is so that after the update query is run then the table looks like the following. The Current DB Table has each Master already updated to BatchNumber "000000"
1, '000000', 'COLOR RED'
2, '000001', 'COLOR RED'
3, '000002', 'COLOR RED'
4, '000003', 'COLOR RED'
5, '000004', 'COLOR RED'
6, '000005', 'COLOR RED'7, '000000', 'COLOR BLUE123'
8, '000001', 'COLOR BLUE123'
9, '000002', 'COLOR BLUE123'
10, '000003', 'COLOR BLUE123'
11, '1000004', 'COLOR BLUE123'
12, '000005', 'COLOR BLUE123'13, '000000', 'COLOR YELLOW 23'
14, '000001', 'COLOR YELLOW 23'
15, '000002', 'COLOR YELLOW 23'16, '000000', 'COLOR ORANGE 23'
The original Batch ID's are the common thread in terms of when records were created this providing an associated incremental BatchNUmber. There are some records which may only have one Master, like in the example above "COLOR ORANGE 23". Nothing would be done to these types of Records.
Try this:CREATE TABLE #BatchMasterV1 (
BatchID int NULL,
BatchNumber nvarchar(16) NULL,
ProductDesc nvarchar(32) NULL
);
INSERT INTO #BatchMasterV1 (BatchID, BatchNumber, ProductDesc)
VALUES (1, '000000', 'COLOR RED'),
(2, '000001', 'COLOR RED'),
(3, '000002', 'COLOR RED'),
(4, '000012', 'COLOR RED'),
(5, '10/123', 'COLOR RED'),
(6, '10/211', 'COLOR RED'),
(7, '000000', 'COLOR BLUE123'),
(8, '0008', 'COLOR BLUE123'),
(9, '0010', 'COLOR BLUE123'),
(10, '0012', 'COLOR BLUE123'),
(11, '10/123', 'COLOR BLUE123'),
(12, '10/211', 'COLOR BLUE123'),
(13, '000000', 'COLOR YELLOW 23'),
(14, '09/23', 'COLOR YELLOW 23'),
(15, '10/01', 'COLOR YELLOW 23'),
(16, '000000', 'COLOR ORANGE 23');
WITH RANKED AS (
SELECT BM.BatchID, BM.BatchNumber, BM.ProductDesc,
ROW_NUMBER() OVER(PARTITION BY BM.ProductDesc ORDER BY BM.BatchID) AS BatchNum
FROM #BatchMasterV1 AS BM
WHERE BM.BatchNumber <> '000000'
)
UPDATE X
SET X.BatchNumber = RIGHT('00000' + CONVERT(varchar(6), X.BatchNum), 6)
FROM RANKED AS X;
SELECT *
FROM #BatchMasterV1;
DROP TABLE #BatchMasterV1;
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
April 16, 2018 at 6:41 pm
Hey guys thanks again for your help, I definitely have some cob-webs in the old SQL world. Last time I did the stuff I was using DTS. LOL
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply