December 4, 2017 at 6:08 am
CREATE TABLE #tblStakeholders
(
StakeholderID int,
SortID int,
GroupID int
)
INSERT INTO #tblStakeholders (StakeholderID, SortID, GroupID)
SELECT 1, 1, 5 UNION ALL
SELECT 1, 2, 6 UNION ALL
SELECT 1, 3, 5 UNION ALL
SELECT 1, 4, 6 UNION ALL
SELECT 1, 5, 5 UNION ALL
SELECT 2, 1, 7 UNION ALL
SELECT 2, 2, 6 UNION ALL
SELECT 2, 3, 7 UNION ALL
SELECT 2, 4, 6
SELECT * FROM #tblStakeholders
DELETE FROM #tblStakeholders WHERE StakeholderID = 1 AND GroupID = 6
SELECT * FROM #tblStakeholders
DROP TABLE IF EXISTS #tblStakeholders
After the Delete statement which removes two records with GroupID of 6 for StakeholderID = 1, I am left with 3 records for StakeholderID = 1 which have a SortID of 1, 3, 5
I need to resort those records so that 1,3,5 changes to 1,2,3.
I can do it by putting the 3 records into a cursor and looping through, seeing if the SortIDs are going up one at a time and updating each record as necessary. Is there a way of doing this without a cursor?
(Sorry about formatting, I could not get font colour to change or line spacing to alter).
December 4, 2017 at 6:26 am
use a cte to select from your temp table, but add ROW_NUMBER as new sort id
then select from the cte
December 4, 2017 at 6:26 am
webskater - Monday, December 4, 2017 6:08 AM
CREATE TABLE #tblStakeholders
(
StakeholderID int,
SortID int,
GroupID int
)
INSERT INTO #tblStakeholders (StakeholderID, SortID, GroupID)
SELECT 1, 1, 5 UNION ALL
SELECT 1, 2, 6 UNION ALL
SELECT 1, 3, 5 UNION ALL
SELECT 1, 4, 6 UNION ALL
SELECT 1, 5, 5 UNION ALL
SELECT 2, 1, 7 UNION ALL
SELECT 2, 2, 6 UNION ALL
SELECT 2, 3, 7 UNION ALL
SELECT 2, 4, 6
SELECT * FROM #tblStakeholders
DELETE FROM #tblStakeholders WHERE StakeholderID = 1 AND GroupID = 6
SELECT * FROM #tblStakeholders
DROP TABLE IF EXISTS #tblStakeholdersAfter the Delete statement which removes two records with GroupID of 6 for StakeholderID = 1, I am left with 3 records for StakeholderID = 1 which have a SortID of 1, 3, 5
I need to resort those records so that 1,3,5 changes to 1,2,3.
I can do it by putting the 3 records into a cursor and looping through, seeing if the SortIDs are going up one at a time and updating each record as necessary. Is there a way of doing this without a cursor?
(Sorry about formatting, I could not get font colour to change or line spacing to alter).
I'm not sure why it would be important to reassign a sort number as long as the numbers are sorted. You could calculate this every time using a row_number or update everything in a single step.
WITH CTE AS(
SELECT *, ROW_NUMBER() OVER(PARTITION BY StakeHolderID ORDER BY SortID) NewSortID
FROM #tblStakeholders
)
UPDATE CTE
SET SortID = NewSortID;
December 4, 2017 at 6:42 am
Luis Cazares - Monday, December 4, 2017 6:26 AMwebskater - Monday, December 4, 2017 6:08 AM
CREATE TABLE #tblStakeholders
(
StakeholderID int,
SortID int,
GroupID int
)
INSERT INTO #tblStakeholders (StakeholderID, SortID, GroupID)
SELECT 1, 1, 5 UNION ALL
SELECT 1, 2, 6 UNION ALL
SELECT 1, 3, 5 UNION ALL
SELECT 1, 4, 6 UNION ALL
SELECT 1, 5, 5 UNION ALL
SELECT 2, 1, 7 UNION ALL
SELECT 2, 2, 6 UNION ALL
SELECT 2, 3, 7 UNION ALL
SELECT 2, 4, 6
SELECT * FROM #tblStakeholders
DELETE FROM #tblStakeholders WHERE StakeholderID = 1 AND GroupID = 6
SELECT * FROM #tblStakeholders
DROP TABLE IF EXISTS #tblStakeholdersAfter the Delete statement which removes two records with GroupID of 6 for StakeholderID = 1, I am left with 3 records for StakeholderID = 1 which have a SortID of 1, 3, 5
I need to resort those records so that 1,3,5 changes to 1,2,3.
I can do it by putting the 3 records into a cursor and looping through, seeing if the SortIDs are going up one at a time and updating each record as necessary. Is there a way of doing this without a cursor?
(Sorry about formatting, I could not get font colour to change or line spacing to alter).I'm not sure why it would be important to reassign a sort number as long as the numbers are sorted. You could calculate this every time using a row_number or update everything in a single step.
WITH CTE AS(
SELECT *, ROW_NUMBER() OVER(PARTITION BY StakeHolderID ORDER BY SortID) NewSortID
FROM #tblStakeholders
)
UPDATE CTE
SET SortID = NewSortID;
I have to resort because users can add more steps to any group - and they need to able to say where in the sort order the new step should appear. So, they need to see a sequential sortID at all times.
Thank you for your reply.
December 4, 2017 at 7:18 am
OK. you're not going to like this but that is probably not a good database design pattern. Can you re-factor so that the steps are in one table and the precedent/antecedent relationship is in another.
Currently you have to update every record in the group below the insert/delete point and as pointed out this is probably best done with a CTE and a RANK() or ROWNUMBER() to generate the sequence.
If you can move the sequencing rules into a separate table then you only have to insert one record and update one record on an insert and you only have to update one record on a delete. You would still need to use ROWNUMBER() or RANK() to determine the sequence ID to present to users
This design will also protect you somewhat when you have to refactor to cater for multiple entry points into the same process (e.g. apply in person vs apply on line - The first steps are different, but the downstream steps are identical) and against conditional branching.
It also means that you can have common steps (e.g. Call customer) and use them in multiple work processes
December 4, 2017 at 9:11 am
Luis' solution will work, but you need to be sure this happens. You might think about ensuring that on a delete, that this routine runs for the deleted.stakeholder/groupid combination in a trigger or transaction of some sort (delete in stored proc, run this).
December 5, 2017 at 7:09 am
aaron.reese - Monday, December 4, 2017 7:18 AMOK. you're not going to like this but that is probably not a good database design pattern. Can you re-factor so that the steps are in one table and the precedent/antecedent relationship is in another.Currently you have to update every record in the group below the insert/delete point and as pointed out this is probably best done with a CTE and a RANK() or ROWNUMBER() to generate the sequence.
If you can move the sequencing rules into a separate table then you only have to insert one record and update one record on an insert and you only have to update one record on a delete. You would still need to use ROWNUMBER() or RANK() to determine the sequence ID to present to users
This design will also protect you somewhat when you have to refactor to cater for multiple entry points into the same process (e.g. apply in person vs apply on line - The first steps are different, but the downstream steps are identical) and against conditional branching.
It also means that you can have common steps (e.g. Call customer) and use them in multiple work processes
Thanks for your input ... the steps I have belong to stages which belong to Templates. Every template / stage / step is unique - I can't have a table of steps and assign them to different template/stages. When the template is applied to a project the SortID of the steps on the stage is copied from the Template so it starts out sequenced as the template. But users are able to move steps on a project as they wish - and the sort must be maintained as they move them. And they can add adhoc steps to any project/stage/step and position it anywhere in the sort order. So, as soon as the project is created, the sequencing of the steps can be changed
December 6, 2017 at 1:12 pm
webskater - Tuesday, December 5, 2017 7:09 AMaaron.reese - Monday, December 4, 2017 7:18 AMOK. you're not going to like this but that is probably not a good database design pattern. Can you re-factor so that the steps are in one table and the precedent/antecedent relationship is in another.Currently you have to update every record in the group below the insert/delete point and as pointed out this is probably best done with a CTE and a RANK() or ROWNUMBER() to generate the sequence.
If you can move the sequencing rules into a separate table then you only have to insert one record and update one record on an insert and you only have to update one record on a delete. You would still need to use ROWNUMBER() or RANK() to determine the sequence ID to present to users
This design will also protect you somewhat when you have to refactor to cater for multiple entry points into the same process (e.g. apply in person vs apply on line - The first steps are different, but the downstream steps are identical) and against conditional branching.
It also means that you can have common steps (e.g. Call customer) and use them in multiple work processesThanks for your input ... the steps I have belong to stages which belong to Templates. Every template / stage / step is unique - I can't have a table of steps and assign them to different template/stages. When the template is applied to a project the SortID of the steps on the stage is copied from the Template so it starts out sequenced as the template. But users are able to move steps on a project as they wish - and the sort must be maintained as they move them. And they can add adhoc steps to any project/stage/step and position it anywhere in the sort order. So, as soon as the project is created, the sequencing of the steps can be changed
How, exactly, do you intend to add new steps into the sequence? How would you know from a query, where to place those new items in the sort order? And having done so, what manner of updating the sort id values did you envision? That matters quite a bit, as Luis' method can work but is based solely on existing remaining values. New values inserted in the middle causes the same problem Luis mentions...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 7, 2017 at 10:49 am
You really don’t understand what a table is or how RDBMS works. What you are doing is mimicking a deck of punch cards in SQL. Let’s start off of the basics. The prefix “tbl-†is a design flaw called a Tibble; Phil factor d_id of column making fun of this some years ago. It still a good read. It violates the rule about putting data and metadata in the same table.
But what you posted is in the table at all! Remember that a table needs to have a key, by definition; it is not an option. But a deck of punch cards doesn’t have to have that, do they? _identifiers can never be numeric because you don’t do any math with them. You ought to know how to write and insert statement by now, but instead you are using the very old Sybase syntax.
Your “sort_id†is metadata or display information. The SQL language is based on the idea that all display formatting is done in a presentation layer, never in the database layer. But again, your writing punchcards in SQL.
CREATE TABLE Stakeholders
(stakeholder_id CHAR(3) NOT NULL,
stakeholder_id_seq INTEGER
DEFAULT NEXT VALUE FOR Stakeholder_Seq,
PRIMARY KEY (stakeholder_id, stakeholder_id_seq),
something_group_id CHAR(3) NOT NULL);
see how we have a real key, proper datatypes, and data element names that conformed to ISO 11179 naming rules. I’m going to make a guess that what you really want is a sequence, and need to learn about the new (well, relatively new for SQL Server)
CREATE SEQUENCE statement.
https://www.red-gate.com/simple-talk/sql/learn-sql-server/sql-server-sequence-basics/
here’s a sample of what you might use, but you really need to adjust it to whatever your business rules are.
CREATE SEQUENCE Stakeholder_Seq
AS INTEGER
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 99
NO CYCLE;
while you can do some buffering, if you do insert one row at a time then the default can work for you.
INSERT INTO Stakeholders (stakeholder_id, something_group_id)
VALUES ('001’, '005');
INSERT INTO Stakeholders (stakeholder_id, something_group_id)
VALUES ('001’, '005');
INSERT INTO Stakeholders (stakeholder_id, something_group_id)
VALUES ('001’, '005');
INSERT INTO Stakeholders (stakeholder_id, something_group_id)
VALUES ('001’, '006');
etc.
DELETE FROM Stakeholders
WHERE stakeholder_id = ‘001’
AND something_group_id = ‘006’;
After the DELETE statement which removes two records [sic: rows are not records] with (something_group_id = ‘006’) for (stakeholder_id = ‘001’), I am left with 3 records [sic] for stakeholder_id = ‘001’ which have a stakeholder_id_seq in {1, 3, 5}
>> I need to resort those records [sic] so that 1, 3, 5 changes to 1, 2, 3. <<
WHY? the sequence numbers are still in sequential order. See what I mean about your original “sort_id†being metadata? Only valid relational model, each row (not record) is a fax in itself. The sort order in a deck of punch cards is an external physical fact having nothing whatsoever to do with the data. Your mindset is simply not relational.
>> I can do it by putting the 3 records [sic] into a cursor and looping through, seeing if the Sort_ids are going up one at a time and updating each record [sic] as necessary. Is there a way of doing this without a cursor? <<
see what I mean about your mindset? The first thing you think of is not a set oriented processing approach, but record oriented, sequential processing.
Please post DDL and follow ANSI/ISO standards when asking for help.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply