August 16, 2016 at 1:04 pm
Hello, I have a table where users enter action steps. The SEQ column indicates the step # (step 1, step 2, etc.). I am having trouble updating the table so that if a user re-orders a step, it updates all the other steps accordingly.
For example: if step 3 was changed to step 1... I would like current step 1 to become step 2, and current step 2 to become step 3.
create table #temp(seq int,step varchar(50))
insert into #temp (seq,step) values (1,'do this first')
insert into #temp (seq,step) values (2,'then do this')
insert into #temp (seq,step) values (3,'finally do this')
Thank you in advance for your expertise.
August 16, 2016 at 1:12 pm
adam-639168 (8/16/2016)
Hello, I have a table where users enter action steps. The SEQ column indicates the step # (step 1, step 2, etc.). I am having trouble updating the table so that if a user re-orders a step, it updates all the other steps accordingly.For example: if step 3 was changed to step 1... I would like current step 1 to become step 2, and current step 2 to become step 3.
create table #temp(seq int,step varchar(50))
insert into #temp (seq,step) values (1,'do this first')
insert into #temp (seq,step) values (2,'then do this')
insert into #temp (seq,step) values (3,'finally do this')
Thank you in advance for your expertise.
How would you identify which step 1 is the first and which one is the second?
August 16, 2016 at 1:23 pm
Luis Cazares (8/16/2016)
How would you identify which step 1 is the first and which one is the second?
Ah good point :Whistling: That was most likely my stumbling block prior to posting here. I have an Edited column, which gets updated at the time a step was re-ordered. See below revised table.
create table #temp(seq int,step varchar(50),edited datetime)
insert into #temp (seq,step,edited) values (1,'do this first',getdate())
insert into #temp (seq,step,edited) values (2,'then do this',getdate())
insert into #temp (seq,step,edited) values (3,'finally do this',getdate())
August 16, 2016 at 1:48 pm
What about this?
create table #temp(seq int,step varchar(50))
insert into #temp (seq,step) values (1,'do this first')
insert into #temp (seq,step) values (2,'then do this')
insert into #temp (seq,step) values (3,'finally do this')
insert into #temp (seq,step) values (4,'an additional step');
DECLARE @OldSeq int = 3, @NewSeq int = 1;
WITH CTE AS(
SELECT *, ROW_NUMBER() OVER( ORDER BY CASE WHEN seq = @OldSeq THEN @NewSeq
WHEN seq < @NewSeq THEN seq - 1
ELSE seq + 1 END) rn
FROM #temp
)
UPDATE CTE SET seq = rn;
SELECT *
FROM #temp
ORDER BY seq;
GO
DROP TABLE #temp;
August 16, 2016 at 2:03 pm
Luis Cazares (8/16/2016)
What about this?
create table #temp(seq int,step varchar(50))
insert into #temp (seq,step) values (1,'do this first')
insert into #temp (seq,step) values (2,'then do this')
insert into #temp (seq,step) values (3,'finally do this')
insert into #temp (seq,step) values (4,'an additional step');
DECLARE @OldSeq int = 3, @NewSeq int = 1;
WITH CTE AS(
SELECT *, ROW_NUMBER() OVER( ORDER BY CASE WHEN seq = @OldSeq THEN @NewSeq
WHEN seq < @NewSeq THEN seq - 1
ELSE seq + 1 END) rn
FROM #temp
)
UPDATE CTE SET seq = rn;
SELECT *
FROM #temp
ORDER BY seq;
GO
DROP TABLE #temp;
Works great! After testing, I found that you could not move an earlier step to a later step (e.g. move step 3 to step 4). Works perfectly when moving a later step to earlier in the sequence though.
August 16, 2016 at 2:15 pm
An additional clause should do the trick.
WITH CTE AS(
SELECT *, ROW_NUMBER() OVER( ORDER BY CASE WHEN seq = @OldSeq THEN @NewSeq
WHEN seq = @NewSeq AND @OldSeq < @NewSeq THEN seq - 1
WHEN seq < @NewSeq THEN seq - 1
ELSE seq + 1 END) rn
FROM #temp
)
UPDATE CTE SET seq = rn;
August 16, 2016 at 2:42 pm
Here is a version that allows you to update multiple records at the same time.
DECLARE @a TABLE (
seqINT,
orig_seq INT
)
INSERT @a( seq, orig_seq)
VALUES(1, 1), (2, 2), (3, 3), (4, 4), (5, 5), (6, 6), (7, 7), (8, 8), (9, 9), (10, 10)
DECLARE @updates TABLE(
from_seq INT NOT NULL,
to_seq INT NOT NULL
)
INSERT @updates
VALUES(5, 3), (7,5), (8, 10)
;
WITH from_recs AS (
SELECT *, ROW_NUMBER() OVER(PARTITION BY v.is_update ORDER BY a.seq) AS rn
FROM @a a
LEFT OUTER JOIN @updates u
ON a.seq = u.from_seq
CROSS APPLY ( VALUES ( CASE WHEN u.from_seq IS NULL THEN 0 ELSE 1 END )) v(is_update)
)
, to_recs AS (
SELECT *, ROW_NUMBER() OVER(PARTITION BY v.is_update ORDER BY a.seq) AS rn
FROM @a a
LEFT OUTER JOIN @updates u
ON a.seq = u.to_seq
CROSS APPLY ( VALUES ( CASE WHEN u.from_seq IS NULL THEN 0 ELSE 1 END )) v(is_update)
)
UPDATE fr
SET seq = tr.seq
FROM from_recs fr
INNER JOIN to_recs tr
ON fr.is_update = tr.is_update
AND fr.rn = tr.rn
WHERE fr.seq <> tr.seq
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 16, 2016 at 3:38 pm
Luis Cazares (8/16/2016)
An additional clause should do the trick.
WITH CTE AS(
SELECT *, ROW_NUMBER() OVER( ORDER BY CASE WHEN seq = @OldSeq THEN @NewSeq
WHEN seq = @NewSeq AND @OldSeq < @NewSeq THEN seq - 1
WHEN seq < @NewSeq THEN seq - 1
ELSE seq + 1 END) rn
FROM #temp
)
UPDATE CTE SET seq = rn;
Luis, still having trouble switching seq 1 (do this first) to seq 4. The new sequence should become:
1. "then do this"
2. "finally do this"
3. "an additional step"
4. "do this first"
Thank you for all your efforts on this.
August 17, 2016 at 8:07 am
drew.allen (8/16/2016)
Here is a version that allows you to update multiple records at the same time.
DECLARE @a TABLE (
seqINT,
orig_seq INT
)
INSERT @a( seq, orig_seq)
VALUES(1, 1), (2, 2), (3, 3), (4, 4), (5, 5), (6, 6), (7, 7), (8, 8), (9, 9), (10, 10)
DECLARE @updates TABLE(
from_seq INT NOT NULL,
to_seq INT NOT NULL
)
INSERT @updates
VALUES(5, 3), (7,5), (8, 10)
;
WITH from_recs AS (
SELECT *, ROW_NUMBER() OVER(PARTITION BY v.is_update ORDER BY a.seq) AS rn
FROM @a a
LEFT OUTER JOIN @updates u
ON a.seq = u.from_seq
CROSS APPLY ( VALUES ( CASE WHEN u.from_seq IS NULL THEN 0 ELSE 1 END )) v(is_update)
)
, to_recs AS (
SELECT *, ROW_NUMBER() OVER(PARTITION BY v.is_update ORDER BY a.seq) AS rn
FROM @a a
LEFT OUTER JOIN @updates u
ON a.seq = u.to_seq
CROSS APPLY ( VALUES ( CASE WHEN u.from_seq IS NULL THEN 0 ELSE 1 END )) v(is_update)
)
UPDATE fr
SET seq = tr.seq
FROM from_recs fr
INNER JOIN to_recs tr
ON fr.is_update = tr.is_update
AND fr.rn = tr.rn
WHERE fr.seq <> tr.seq
Drew
Thank you. It took a while for me to digest (non-expert etc. :doze:) but I have successfully incorporated this into our system. Works like a charm. Thank you for your help! 😀
August 17, 2016 at 9:32 am
I realized this morning that my code needed a slight update. My updates sample data had the same ordering for the from_seq and the to_seq, but that is not necessarily the case. Here is the updated code to handle cases where the orderings on the two columns are different.
DECLARE @a TABLE (
seqINT,
orig_seq INT
)
INSERT @a( seq, orig_seq)
VALUES(1, 1), (2, 2), (3, 3), (4, 4), (5, 5), (6, 6), (7, 7), (8, 8), (9, 9), (10, 10)
DECLARE @updates TABLE(
from_seq INT NOT NULL,
to_seq INT NOT NULL
)
INSERT @updates
VALUES(5, 3), (7,5), (6, 10)
;
WITH from_recs AS (
SELECT *, ROW_NUMBER() OVER(PARTITION BY v.is_update ORDER BY a.seq) AS rn
FROM @a a
LEFT OUTER JOIN @updates u
ON a.seq = u.from_seq
CROSS APPLY ( VALUES ( CASE WHEN u.from_seq IS NULL THEN 0 ELSE 1 END )) v(is_update)
)
, to_recs AS (
SELECT *, ROW_NUMBER() OVER(PARTITION BY v.is_update ORDER BY a.seq) AS rn
FROM @a a
LEFT OUTER JOIN @updates u
ON a.seq = u.to_seq
CROSS APPLY ( VALUES ( CASE WHEN u.from_seq IS NULL THEN 0 ELSE 1 END )) v(is_update)
)
UPDATE fr
SET seq = ISNULL(fr.to_seq, tr.seq)
FROM from_recs fr
INNER JOIN to_recs tr
ON fr.is_update = tr.is_update
AND fr.rn = tr.rn
WHERE fr.seq <> tr.seq
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 17, 2016 at 10:40 am
drew.allen (8/17/2016)
I realized this morning that my code needed a slight update. My updates sample data had the same ordering for the from_seq and the to_seq, but that is not necessarily the case. Here is the updated code to handle cases where the orderings on the two columns are different.
DECLARE @a TABLE (
seqINT,
orig_seq INT
)
INSERT @a( seq, orig_seq)
VALUES(1, 1), (2, 2), (3, 3), (4, 4), (5, 5), (6, 6), (7, 7), (8, 8), (9, 9), (10, 10)
DECLARE @updates TABLE(
from_seq INT NOT NULL,
to_seq INT NOT NULL
)
INSERT @updates
VALUES(5, 3), (7,5), (6, 10)
;
WITH from_recs AS (
SELECT *, ROW_NUMBER() OVER(PARTITION BY v.is_update ORDER BY a.seq) AS rn
FROM @a a
LEFT OUTER JOIN @updates u
ON a.seq = u.from_seq
CROSS APPLY ( VALUES ( CASE WHEN u.from_seq IS NULL THEN 0 ELSE 1 END )) v(is_update)
)
, to_recs AS (
SELECT *, ROW_NUMBER() OVER(PARTITION BY v.is_update ORDER BY a.seq) AS rn
FROM @a a
LEFT OUTER JOIN @updates u
ON a.seq = u.to_seq
CROSS APPLY ( VALUES ( CASE WHEN u.from_seq IS NULL THEN 0 ELSE 1 END )) v(is_update)
)
UPDATE fr
SET seq = ISNULL(fr.to_seq, tr.seq)
FROM from_recs fr
INNER JOIN to_recs tr
ON fr.is_update = tr.is_update
AND fr.rn = tr.rn
WHERE fr.seq <> tr.seq
Drew
Thank you! Early morning light bulb was it 😀
August 17, 2016 at 12:22 pm
I apologize for this half answer, but in some of my books I have an update statement that uses a case expression (not a case statement, please?) To move things around in the Netflix queue. If you will sit down and play with the problem, it is really not too bad and a good mental problem. The rows that are outside the range of the original position in the final position are not affected. The initial position is switched to a final position and every in between is moved up (or move down) in the sequence.
Insertion of a new row is pretty obvious; put it at the end of the list of steps, then move it to its final position in the execution sequence.
Sorry to be so vague but right now I am trying to clean up after a dog who just crapped on the floor and do not have access to my books 🙁 welcome to the exciting world of data processing!
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
August 17, 2016 at 1:07 pm
CELKO (8/17/2016)
I apologize for this half answer, but in some of my books I have an update statement that uses a case expression (not a case statement, please?) To move things around in the Netflix queue. If you will sit down and play with the problem, it is really not too bad and a good mental problem. The rows that are outside the range of the original position in the final position are not affected. The initial position is switched to a final position and every in between is moved up (or move down) in the sequence.Insertion of a new row is pretty obvious; put it at the end of the list of steps, then move it to its final position in the execution sequence.
Sorry to be so vague but right now I am trying to clean up after a dog who just crapped on the floor and do not have access to my books 🙁 welcome to the exciting world of data processing!
Wonderful, looking forward to the rest of your answer post-clean-up!
August 17, 2016 at 1:46 pm
CELKO (8/17/2016)
I apologize for this half answer, but in some of my books I have an update statement that uses a case expression (not a case statement, please?) To move things around in the Netflix queue. If you will sit down and play with the problem, it is really not too bad and a good mental problem. The rows that are outside the range of the original position in the final position are not affected. The initial position is switched to a final position and every in between is moved up (or move down) in the sequence.Insertion of a new row is pretty obvious; put it at the end of the list of steps, then move it to its final position in the execution sequence.
Sorry to be so vague but right now I am trying to clean up after a dog who just crapped on the floor and do not have access to my books 🙁 welcome to the exciting world of data processing!
The old-fashioned method was brutal, barbaric and vaguely narcissistic. Delighted to see that there's no suggestion of it in this post. Not bad, Joe. Not bad at all.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
August 17, 2016 at 3:50 pm
drew.allen (8/17/2016)
I realized this morning that my code needed a slight update. My updates sample data had the same ordering for the from_seq and the to_seq, but that is not necessarily the case. Here is the updated code to handle cases where the orderings on the two columns are different.
DECLARE @a TABLE (
seqINT,
orig_seq INT
)
INSERT @a( seq, orig_seq)
VALUES(1, 1), (2, 2), (3, 3), (4, 4), (5, 5), (6, 6), (7, 7), (8, 8), (9, 9), (10, 10)
DECLARE @updates TABLE(
from_seq INT NOT NULL,
to_seq INT NOT NULL
)
INSERT @updates
VALUES(5, 3), (7,5), (6, 10)
;
WITH from_recs AS (
SELECT *, ROW_NUMBER() OVER(PARTITION BY v.is_update ORDER BY a.seq) AS rn
FROM @a a
LEFT OUTER JOIN @updates u
ON a.seq = u.from_seq
CROSS APPLY ( VALUES ( CASE WHEN u.from_seq IS NULL THEN 0 ELSE 1 END )) v(is_update)
)
, to_recs AS (
SELECT *, ROW_NUMBER() OVER(PARTITION BY v.is_update ORDER BY a.seq) AS rn
FROM @a a
LEFT OUTER JOIN @updates u
ON a.seq = u.to_seq
CROSS APPLY ( VALUES ( CASE WHEN u.from_seq IS NULL THEN 0 ELSE 1 END )) v(is_update)
)
UPDATE fr
SET seq = ISNULL(fr.to_seq, tr.seq)
FROM from_recs fr
INNER JOIN to_recs tr
ON fr.is_update = tr.is_update
AND fr.rn = tr.rn
WHERE fr.seq <> tr.seq
Drew
Unless my understanding is incorrect, I believe that the movement in this solution is the wrong way around.
To prove this, let's take a look at only the first movement ... from_seq=5 to_seq=3.
The original seq is:
1, 2, 3, 4, 5, 6, ...
My understanding here is that we are moving [5] up the sequence to where [3] is currently, and [3] and [4] to each move down 1 space. Hence, I would expect the result to be:
1, 2, 5, 3, 4, 6, ...
However, the above code produces the following result, which is to move the [3] down to where [5] is currently, and [4] and [5] each move up 1 space:
1, 2, 4, 5, 3, 6, ...
The following code change will provide what I understand to be the correct result.
DECLARE @a TABLE (
orig_seq INT
, seqINT
)
INSERT @a( seq, orig_seq)
VALUES(1, 1), (2, 2), (3, 3), (4, 4), (5, 5), (6, 6), (7, 7), (8, 8), (9, 9), (10, 10)
DECLARE @updates TABLE(
from_seq INT NOT NULL,
to_seq INT NOT NULL
)
INSERT @updates
VALUES(5, 3)--, (7,5), (6, 10);
WITH from_recs AS (
SELECT *, ROW_NUMBER() OVER(PARTITION BY v.is_update ORDER BY a.seq) AS rn
FROM @a a
LEFT OUTER JOIN @updates u
ON a.seq = u.to_seq--ON a.seq = u.from_seq
CROSS APPLY ( VALUES ( CASE WHEN u.from_seq IS NULL THEN 0 ELSE 1 END )) v(is_update)
)
, to_recs AS (
SELECT *, ROW_NUMBER() OVER(PARTITION BY v.is_update ORDER BY a.seq) AS rn
FROM @a a
LEFT OUTER JOIN @updates u
ON a.seq = u.from_seq--ON a.seq = u.to_seq
CROSS APPLY ( VALUES ( CASE WHEN u.from_seq IS NULL THEN 0 ELSE 1 END )) v(is_update)
)
UPDATE fr
SET seq = ISNULL(tr.seq, fr.to_seq ) --ISNULL(fr.to_seq, tr.seq)
FROM from_recs fr
INNER JOIN to_recs tr
ON fr.is_update = tr.is_update
AND fr.rn = tr.rn
WHERE fr.seq <> tr.seq
select * from @a
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply