Help me in update - complex
-
hi All,
Hope all are fine
create table #sample(id varchar(100), version_no int, set_type varchar(100), set_no int ,XML_NAME varchar(100))insert into #sample(id,version_no,set_type)
select '12',1,'Red'union all
select '2346/2347',1,'Red'
union allselect '1235/1236',1,'Green'
union allselect '12890',1,'Green'
union all
select '1208',1,'Green'
union all
select '1234/4466',1,'Green'
union all
select '908472',1,'Green'
union allselect '7958326',1,'Blue'
create table #master_sample(set_type varchar(100), seqno int)
insert into #master_sample(set_type, seqno)
select 'Red' , 3400000
union all
select 'Green' , 2000000
union all
select 'Blue', 1Desire Result :
insert into #sample(id,version_no,set_type,set_no,XML_NAME)
select '12',1,'Red',1,'XML_3400001_3400002_1312170922'union all
select '2346/2347',1,'Red',1,'XML_3400001_3400002_1312170922'
union allselect '1235/1236',1,'Green',1,'XML_2000001_2000002_1312170922'
union allselect '12890',1,'Green',1,'XML_2000001_2000002_1312170922'
union all
select '1208',1,'Green',1,'XML_2000003_2000004_1312170922'
union all
select '1234/4466',1,'Green',1,'XML_2000003_2000004_1312170922'
union all
select '908472',1,'Green',2,'XML_2000005_2000005_1312170922'
union all
select '7958326',1,'Blue',1,'XML_1_1_1312170922'
So above is the sample data set.
My goal is to update #sample table, column set_no & XML_name dynamicallyLogic behind update ,
first update is to column set_no :
There are 3 distinct set_type (red,green,blue) - 2 records red , 5 records green & 1 record for blue . Each set_type should be grouped by 4
that is , here green has 5 records , so for id ('1235/1236','12890','1208',1234/4466') set_no should be 1 & for ID ('908472') set_no should be 2.
for Red and blue set_no should be 1 because it has record less than 4
so i tried a updateUPDATE t1
Set set_no = t2.set_no
from #sample t1
inner join (select id,
set_type,Ceiling(Cast(Row_Number () Over (Partition By set_type Order by id) as Float)/4) as set_no
from #sample ) t2
on t1.id = t2.idbut i dont know above update will work when table fills with more records.
My next complex update is ,updating XML_name column ,
That is , a xml name , nothing but real xml should have 2 ID information in it , so Xml name format should be like,
FORMAT : XML_startingSEQNO_endingSEQNO_ DDMMYYHHMM (SEQNO in this table #master_sample)
so lets take Set_type Green ,
for ID : '1235/1236' XML_name : 'XML_2000001_2000002_1312170922'
for ID : '12890' XML_name : 'XML_2000001_2000002_1312170922' -- (Id 's '1235/1236' & '12890' present in this xml that why same name for both ID )FOR ID : '1208' XML_name : 'XML_2000003_2000004_1312170922'
FOR ID : '1234/4466' XML_name : 'XML_2000003_2000004_1312170922'
FOR ID : '908472' XML_name :'XML_2000005_2000005_1312170922' -- this xml will have only 1 id info , because totally 5 green record, 4 is group as 2 and 1 remaining so (XML_2000003_2000004_1312170922)
for green seqno should with 200000 for red with 3400000.
Red :
FOR ID : '12' XML_name : 'XML_3400001_3400002_1312170922'
FOR ID : '2346/2347' XML_name : 'XML_3400001_3400002_1312170922'for Blue :
FOR ID : '7958326' XML_name : 'XML_1_1_1312170922' (SEQNO 1 )Hope i explained clearly please help me out . i got stuck. Don't know if it is possible with CTE or set based . because I have to update millions of record.
-
You're overcomplicating your formula. There is no reason to cast to float. Just use integer division.
UPDATE t1
SET set_no = t2.set_no
FROM #sample t1
INNER JOIN (SELEct id,
set_type, Row_Number () Over (Partition By set_type Order by id) / 4 + 1 as set_no
FROM #sample ) t2
ON t1.id = t2.idDrew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
-- try this
; -- statement termininator
WITH Updater AS (
SELECT set_no,
(Row_Number () Over (Partition By set_type Order by id)+3)/4 as NewSet_no
FROM #sample
)
UPDATE Updater SET set_no = NewSet_no
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden - ChrisM@Work - Wednesday, January 24, 2018 9:52 AM
Thanks a lot ChrisM@Work,
it seems your query gives my desire result. let me check with more records.But in my live scenario its 9999 instead of 4 , so i can add row_number() with 9998 / by 9999 right ? it will work right ?
But i am struggling with another update ,xml_name which seems complex , i think it would be easy for you , check below , if you get some idea , please help me
updating XML_name column ,
That is , a xml name , nothing but real xml should have 2 ID information in it , so Xml name format should be like,
FORMAT : XML_startingSEQNO_endingSEQNO_ DDMMYYHHMM (SEQNO in this table #master_sample)
so lets take Set_type Green ,
for ID : '1235/1236' XML_name : 'XML_2000001_2000002_1312170922'
for ID : '12890' XML_name : 'XML_2000001_2000002_1312170922' -- (Id 's '1235/1236' & '12890' present in this xml that why same name for both ID )FOR ID : '1208' XML_name : 'XML_2000003_2000004_1312170922'
FOR ID : '1234/4466' XML_name : 'XML_2000003_2000004_1312170922'
FOR ID : '908472' XML_name :'XML_2000005_2000005_1312170922' -- this xml will have only 1 id info , because totally 5 green record, 4 is group as 2 and 1 remaining so (XML_2000003_2000004_1312170922)
for green seqno should with 200000 for red with 3400000.
Red :
FOR ID : '12' XML_name : 'XML_3400001_3400002_1312170922'FOR ID : '2346/2347' XML_name : 'XML_3400001_3400002_1312170922'
for Blue :
FOR ID : '7958326' XML_name : 'XML_1_1_1312170922' (SEQNO 1 ) -
Sorry , is it too hard guys ?
- JoNTSQLSrv - Wednesday, January 24, 2018 10:30 PM
Not at all. The problem is, there are inconsistencies in your description. Have a play with this and you will see what I mean:
WITH Ranger AS (
SELECT
s.id, s.version_no, s.set_type,
m.seqno,
rn = ROW_NUMBER() OVER(PARTITION BY s.set_type ORDER BY id)
FROM #sample s
INNER JOIN #master_sample m ON m.set_type = s.set_type
)
SELECT *,
XML_start = CAST(seqno + MIN(rn) OVER (PARTITION BY r.set_type, x.Set_no) AS VARCHAR(10)),
XML_end = CAST(seqno + MAX(rn) OVER (PARTITION BY r.set_type, x.Set_no) AS VARCHAR(10)),
Datebit = REPLACE(CONVERT(VARCHAR(8), GETDATE(), 3),'/','') + REPLACE(CONVERT(VARCHAR(5), GETDATE(), 108),':','')
FROM Ranger r
CROSS APPLY (SELECT Set_no = (rn+3)/4) x
ORDER BY set_type
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden - ChrisM@Work - Thursday, January 25, 2018 2:57 AM
THanks ChrisM@Works ,
Awesome try . I am amazed the way you are thinking to get solution.
sorry for causing inconsistencies with my description , sorry for my bad english. English is not my native language.
From your output , i can see that you didnt get my point .
I tried the solution using while loop . please check the below code , but even in that i am not able to do it dynamically for red, green & blue . I did only for green (by hard coding as green)
DECLARE @min-2 INT,
@div INT,
@count INT,
@buklet INT,
@C INT,
@STR VARCHAR(MAX)declare @dateit VARCHAR(20)
set @dateit = REPLACE(CONVERT(VARCHAR(8), GETDATE(), 3),'/','') + REPLACE(CONVERT(VARCHAR(5), GETDATE(), 108),':','')SET @div=2
declare @updating_temp TABLE (
ID int Identity(1,1) NOT NULL,
id_2 varchar(100),
version_no int,
set_type varchar(100),
set_no int
)INSERT INTO @updating_temp(id_2,version_no,set_type,set_no)
SELECT id,version_no,set_type,set_no
FROM #sample where set_type = 'green'SELECT @min-2=MIN(ID),@count=COUNT(*)
FROM @updating_tempSELECT @buklet = CAST(@count/@div AS INT)
IF @count%@div>0
BEGIN
SET @buklet=@buklet+1
ENDSET @C=0
WHILE @C<@buklet
BEGIN
declare @xml_name varchar(100)
declare @seq_no int
select @seq_no = seqno from #master_sample where set_type = 'green'select @xml_name= 'XML'+'_'+cast (@seq_no+min(ID) as varchar(100))+'_'+ cast(@seq_no+max(ID) as varchar(100) )+'_'+@dateit
FROM @updating_temp
WHERE ID BETWEEN (@min + @C*@div) AND ((@min + @C*@div) +@div-1)update b
set b.XML_NAME = @xml_name
FROM @updating_temp a
inner join #sample b
on a.id_2 = b.id
and a.set_type = b.set_type
WHERE a.ID BETWEEN (@min + @C*@div) AND ((@min + @C*@div) +@div-1)
SET @C=@c+1END
So i want it in cte or set based which could increase performance .
At least i want it to do it for red , green & blue dynamically. In the above while loop code i am able to do it separately by hard coding green or red or blue ,but not dynamically . I am working on it, but something goes wrong in my query , please check below as well. Million thanks in advance
DECLARE @TEMP TABLE
(ID_TEMP INT IDENTITY(1,1), SET_TYPE VARCHAR(100) )DECLARE @SET_TYPE VARCHAR(100)
DECLARE @DATEIT VARCHAR(20)
DECLARE @COUNT_1 INTINSERT INTO @TEMP(SET_TYPE)
SELECT DISTINCT SET_TYPE FROM #SAMPLESET @DATEIT = REPLACE(CONVERT(VARCHAR(8), GETDATE(), 3),'/','') + REPLACE(CONVERT(VARCHAR(5), GETDATE(), 108),':','')
SELECT @COUNT_1 = COUNT(1) FROM @TEMP
WHILE 0 < @COUNT_1
BEGIN
SELECT @SET_TYPE = SET_TYPE FROM @TEMP WHERE ID_TEMP= @COUNT_1DECLARE @min-2 INT,
@DIV INT,
@COUNT INT,
@BUKLET INT,
@C INT,
@STR VARCHAR(MAX)----DECLARE @DATEIT VARCHAR(20)
----SET @DATEIT = REPLACE(CONVERT(VARCHAR(8), GETDATE(), 3),'/','') + REPLACE(CONVERT(VARCHAR(5), GETDATE(), 108),':','')SET @div=2
DECLARE @UPDATING_TEMP TABLE (
ID INT IDENTITY(1,1) NOT NULL,
ID_2 VARCHAR(100),
VERSION_NO INT,
SET_TYPE VARCHAR(100),
SET_NO INT
)INSERT INTO @UPDATING_TEMP(ID_2,VERSION_NO,SET_TYPE,SET_NO)
SELECT ID,VERSION_NO,SET_TYPE,SET_NO
FROM #SAMPLE WHERE SET_TYPE = @SET_TYPESELECT @min-2=MIN(ID),@COUNT=COUNT(*)
FROM @UPDATING_TEMPSELECT @BUKLET = CAST(@COUNT/@DIV AS INT)
IF @COUNT%@DIV>0
BEGIN
SET @BUKLET=@BUKLET+1
ENDSET @C=0
WHILE @C<@BUKLET
BEGIN
DECLARE @XML_NAME VARCHAR(100)
DECLARE @SEQ_NO INT
SELECT @SEQ_NO = SEQNO FROM #MASTER_SAMPLE WHERE SET_TYPE = @SET_TYPESELECT @XML_NAME= 'XML'+'_'+CAST (@SEQ_NO+MIN(ID) AS VARCHAR(100))+'_'+ CAST(@SEQ_NO+MAX(ID) AS VARCHAR(100) )+'_'+@DATEIT
FROM @UPDATING_TEMP
WHERE ID BETWEEN (@MIN + @C*@DIV) AND ((@MIN + @C*@DIV) +@DIV-1)select @SET_TYPE
select @XML_NAME----UPDATE B
----SET B.XML_NAME = @XML_NAME
----FROM @UPDATING_TEMP A
----INNER JOIN #SAMPLE B
----ON A.ID_2 = B.ID
----AND A.SET_TYPE = B.SET_TYPE
----WHERE A.ID BETWEEN (@MIN + @C*@DIV) AND ((@MIN + @C*@DIV) +@DIV-1)
SET @C=@C+1END
SELECT @COUNT_1 = @COUNT_1 -1
END
Hope you can fix it 🙂 - JoNTSQLSrv - Friday, January 26, 2018 9:50 AM
Not really. You've posted two solutions of your own which don't work, but you haven't described what is wrong with them.
You've described my solution as "missing the point" but you haven't described what is wrong with it.
There are inconsistencies between your description of the problem and your desired results from the sample data set.
I cannot continue without clarification, sorry.“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden - ChrisM@Work - Friday, January 26, 2018 9:58 AM
Sorry again ChrisM,
Please check below image . My Desire result
set_type : Green, red & blue should be grouped like 2 id into 1 xml_name , if 1 ID then into 1 xml_nameFor Green totally 5 records so 3 XML_name (2+2+1)-- XML_Name sequence u can understand from image.
For Red 2 records so 1 xml_name (2) --XML_Name sequence u can understand from image.
For Blue 1 record so 1 xml_name (1) --XML_Name sequence u can understand from image.
Hope you are clear Now . Sorry again .Problem with my solution is ,
1. Please check my below solution , Dynamically I am unable to pass green, red & blue , but by hard coding set_type to green or blue or red it is working fine.
DECLARE @min-2 INT,
@div INT,
@count INT,
@buklet INT,
@C INT,
@STR VARCHAR(MAX)declare @dateit VARCHAR(20)
set @dateit = REPLACE(CONVERT(VARCHAR(8), GETDATE(), 3),'/','') + REPLACE(CONVERT(VARCHAR(5), GETDATE(), 108),':','')SET @div=2
declare @updating_temp TABLE (
ID int Identity(1,1) NOT NULL,
id_2 varchar(100),
version_no int,
set_type varchar(100),
set_no int
)INSERT INTO @updating_temp(id_2,version_no,set_type,set_no)
SELECT id,version_no,set_type,set_no
FROM #sample where set_type = 'blue'SELECT @min-2=MIN(ID),@count=COUNT(*)
FROM @updating_tempSELECT @buklet = CAST(@count/@div AS INT)
IF @count%@div>0
BEGIN
SET @buklet=@buklet+1
ENDSET @C=0
WHILE @C<@buklet
BEGIN
declare @xml_name varchar(100)
declare @seq_no int
select @seq_no = (case when seqno = 1 then 0 end) from #master_sample where set_type = 'blue'select @xml_name= 'XML'+'_'+cast (@seq_no+min(ID) as varchar(100))+'_'+ cast(@seq_no+max(ID) as varchar(100) )+'_'+@dateit
FROM @updating_temp
WHERE ID BETWEEN (@min + @C*@div) AND ((@min + @C*@div) +@div-1)update b
set b.XML_NAME = @xml_name
FROM @updating_temp a
inner join #sample b
on a.id_2 = b.id
and a.set_type = b.set_type
WHERE a.ID BETWEEN (@min + @C*@div) AND ((@min + @C*@div) +@div-1)
SET @C=@c+1END
2. I am trying to do it with while loop and it is not set based , so it could affect performance - JoNTSQLSrv - Wednesday, January 24, 2018 8:41 AM
By Bydefinition, a table must have a key, but what you posted was garbagethat could never have a key. Here’s my guess at a correction. Also,you might want to read a book on basic data modeling. There’s nosuch thing as a generic, magical, universal “id†in it valid datamodel. It has to be the identifier of something in particular. Whydid you bother to put a reasonable size and a constraint on your“set_type� If you keep using insanely long VARCHAR(n), then onedate you’re going to have it fill up completely and so many makes amistake. The length of a column is a very important part of thedefinition of a column. Here’s my guess at valid DDL:CREATE TABLE Samples
(sample_id VARCHAR(10) NOT NULL PRIMARY KEY,
version_nbr INTEGER NOT NULL,
set_type VARCHAR(5) NOT NULL
CHECK(set_type IN (‘Red’, ‘Green’, ‘Blue’)),
set_nbr INTEGER,
xml_nameVARCHAR(100));why re you still using the old original Sybase construct for insertions?We have had ANSI/ISO standard syntax for many, many years.
INSERT INTO Samples(id, version_nbr, set_type)
VALUES
('12',1, 'Red'),
('1208',1, 'Green'),
('1234/4466',1, 'Green'),
('1235/1236',1, 'Green'),
('12890',1, 'Green'),
('2346/2347',1, 'Red'),
('7958326',1, 'Blue'),
('908472',1, 'Green');the term “master†comes from tape files, and has no place in RDBMS. This was the tape that was created by merging transaction tapes into the old master to create the new master file. You apparently learn to program from people my age 🙁
More than that, there’s no such thing as a generic sequence number inRDBMS, it has to be the sequence number of something in particular(checks, invoices, etc.). I cannot come up with a good name for thistable, so I’ll just call it foobar..
CREATE TABLEFoobar
(set_typeARCHAR(5) NOT NULL PRIMARY,
sample_seq INTEGER NOT NULL);INSERT INTO Foobar(set_type, sample_seq)
('Red',3400000),
('Green',2000000),
('Blue',1);Desire Result :
('12',1, 'Red', 1, 'XML_3400001_3400002_1312170922'),
('1208',1, 'Green', 1, 'XML_2000003_2000004_1312170922'),
('1234/4466',1, 'Green', 1, 'XML_2000003_2000004_1312170922'),
('1235/1236',1, 'Green', 1, 'XML_2000001_2000002_1312170922'),
('12890',1, 'Green', 1, 'XML_2000001_2000002_1312170922'),
('2346/2347',1, 'Red', 1, 'XML_3400001_3400002_1312170922'),
('7958326',1, 'Blue', 1, 'XML_1_1_1312170922'),
('908472',1, 'Green', 2, 'XML_2000005_2000005_1312170922');Soabove is the sample data set.
Mygoal is to update Samples table, column set_nbr & XML_namedynamically
Logicbehind update,
firstupdate is to column set_nbr :
Thereare 3 distinct set_type (red, green, blue) - 2 records [sic] red, 5records [sic] green & 1 record [sic] for blue. Each set_typeshould be grouped by 4 that is, here green has 5 records,[sic] so forid ('1235/1236', '12890', '1208', 1234/4466') set_nbr should be 1 &for ID ('908472') set_nbr should be 2.
forRed and blue set_nbr should be 1 because it has record [sic] lessthan 4
soI tried a update <<
Rowsare not records. This is more of your “magnetic tape mindset†andnot RDBMS. You are using the old, original Sybase UPDATE syntax thatwe got rid of several years ago. You also cast something to a FLOATfor no particular reason. Look up how a MERGE statement works anduse it
here’sa quick skeleton that might be of some help to you:
MERGEINTO Samples
USING
(SELECTsample_id, set_type,
(ROW_NUMBER()
OVER(PARTITION BY set_type
ORDER BY sample_id))/4
FROMSamples) AS Foobar (sample_id, set_type, set_nbr)
ONSamples.sample_id = Foobar.sample_id
WHENMATCHED
THENUPDATE
SETset_nbr = Foobar.set_nbr;
Please post DDL and follow ANSI/ISO standards when asking for help.
- jcelko212 32090 - Friday, January 26, 2018 11:18 AM
Thanks a lot Joe Celko,
Thanks for your wonderful tips and correction. Sorry that I didn't follow ANSI standardcan you please help me in Dynamically updating XML_name column in my scenario ?
Thanks in advance - JoNTSQLSrv - Wednesday, January 24, 2018 8:41 AM
Post itself complexxx...
- subramaniam.chandrasekar - Monday, January 29, 2018 6:23 AM
why post is complex ? still no clarity ?
- JoNTSQLSrv - Monday, January 29, 2018 7:24 AM
Correct, clarity is poor. However, try this:
WITH Ranger AS (
SELECT
s.id, s.version_no, s.set_type,
m.seqno,
rn = ROW_NUMBER() OVER(PARTITION BY s.set_type ORDER BY id)
FROM #sample s
INNER JOIN #master_sample m ON m.set_type = s.set_type
)
SELECT *,
RowPair,
XML_start = CAST(seqno + MIN(rn) OVER (PARTITION BY r.set_type, x.Set_no, x.RowPair) AS VARCHAR(10)),
XML_end = CAST(seqno + MAX(rn) OVER (PARTITION BY r.set_type, x.Set_no, x.RowPair) AS VARCHAR(10)),
Datebit = REPLACE(CONVERT(VARCHAR(8), GETDATE(), 3),'/','') + REPLACE(CONVERT(VARCHAR(5), GETDATE(), 108),':','')
FROM Ranger r
CROSS APPLY (
SELECT
Set_no = (rn+3)/4,
RowPair = (rn+1)/2
) x
ORDER BY set_type
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden - ChrisM@Work - Monday, January 29, 2018 7:32 AM
Wooow awesome ChrisM@Work,
95% you have solved this complex Update.
Only One changes, That is , This above query is naming XML as 2 even if Set_type has 1 record.
Ok i will try with your solution and come with what i can, i will post it here.. let see if i can.
Thanks a lot for your wonderful help... I wish i could think like you. 🙂
I am inspired by your skill. Keep rocking
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply