Counter digit update for the Ids

Kenny Gua 411 Reputation points
2021-04-12T16:25:09.94+00:00

Hi, I have to update number in relatedID field. The number should start from 1 to total records of every EID. If any EID has three records then relatedID should update from 1 to 4. If any EID has only one record then relatedid should update only 1. The only condition is that Wife's number should be last number if any Eid has more records. See examples below.
Create table #temp (EId char(10), RelatedID char(1), relation char(10))
Insert into #temp values ('122233',NULL, 'Wife')
Insert into #temp values ('122233',NULL, 'Children')
Insert into #temp values ('122233',NULL, 'Children')

Insert into #temp values ('122234',NULL, 'Children')

Insert into #temp values ('122235',NULL, 'Wife')
Insert into #temp values ('122235',NULL, 'Handicap')
Insert into #temp values ('122235',NULL, 'Children')

Insert into #temp values ('122237',NULL, 'Wife')

Insert into #temp values ('122239',NULL, 'Wife')
Insert into #temp values ('122239',NULL, 'Handicap')
Insert into #temp values ('122239',NULL, 'Children')
Insert into #temp values ('122239',NULL, 'Children')
Insert into #temp values ('122239',NULL, 'Children')

Select * from #temp

--Required results
EId RelatedID relation
122233 3 Wife
122233 1 Children
122233 2 Children
122234 1 Children
122235 3 Wife
122235 2 Handicap
122235 1 Children
122237 1 Wife
122239 5 Wife
122239 4 Handicap
122239 3 Children
122239 2 Children
122239 1 Children

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,536 questions
0 comments No comments
{count} votes

Accepted answer
  1. Yitzhak Khabinsky 24,831 Reputation points
    2021-04-12T17:24:20.82+00:00

    Hi @Kenny Gua ,

    You DB table is missing a primary key. So it is not even in a first normal form.

    SQL

    -- DDL and sample data population, start  
    DECLARE @tbl table (EId char(10), RelatedID char(1), relation char(10));  
    Insert into @tbl values   
    ('122233',NULL, 'Wife')  
    ,('122233',NULL, 'Children')  
    ,('122233',NULL, 'Children')  
    ,('122234',NULL, 'Children')  
    ,('122235',NULL, 'Wife')  
    ,('122235',NULL, 'Handicap')  
    ,('122235',NULL, 'Children')  
    ,('122237',NULL, 'Wife')  
    ,('122239',NULL, 'Wife')  
    ,('122239',NULL, 'Handicap')  
    ,('122239',NULL, 'Children')  
    ,('122239',NULL, 'Children')  
    ,('122239',NULL, 'Children')  
    ,('122239',NULL, 'Z');  
    -- DDL and sample data population, end  
      
    SELECT EId  
     , ROW_NUMBER() OVER (PARTITION BY EId ORDER BY IIF(relation = 'wife', REPLICATE('Z', 20), relation) ASC) AS pos  
     , relation  
    FROM @tbl;  
    
    1 person found this answer helpful.

3 additional answers

Sort by: Most helpful
  1. Joe Celko 16 Reputation points
    2021-04-12T18:43:45.91+00:00

    > I have to update number inrelated_id field [sic]. The number should start from 1 to total records [sic] of every foo_id. If any foo_id has three records [sic] thenrelated_id should update from 1 to 4. If any foo_id has only one record [sic] thenrelated_id should update only 1. The only condition is that Wife's number should be last number if any foo_id has more records [sic]. See examples below. <<

    What you have posted is a total disaster. If you ever read a book on SQL or relational database, you would know that a field refers to part of a column which has meaning in itself, but not a complete meaning. The usual example in the textbooks is that a DATE has three fields in it (year, month, day). In order to be a table, we need to have a key. Each row in a table has to have a unique key. Your declaration has no we declared; your sample data has duplicates. What you're doing is encoding a deck of 1950s punchcards in SQL! Are you trying to mimic pointer chains from an old Bachmann style database? You also don't know that an insertion statement can take a table constructor, so your mimicking the old punchcards that had to be inserted one at a time in a sequence. I assume that "_id" means that this is an identifier. But how can it be null? Think about it!

    CREATE TABLE Foobars
    (foo_id CHAR(10) NOT NULL,
    related_id CHAR(1), --- how can an identifier be unknown?
    relation_name CHAR(10) NOT NULL,
    PRIMARY KEY (foo_id, relation_name));

    Next, let's remove all of the totally non-relational, completely illegal duplicate rows from the insertion. Let's declare a proper primary key. Yes I know it's hard to unlearn punchcards when that's what you grown up with them for the decades. A feature that Dr. Codd (you know who this is?) called "degree of duplication is another basic principle, you're missing.

    INSERT INTO Foobars --- stilll a stinking mess
    VALUES
    ('122233', NULL, 'Wife'),
    ('122233', NULL, 'Children');
    ('122235', NULL, 'Handicap'),
    ('122235', NULL, 'Children'),
    ('122237', NULL, 'Wife'),
    ('122239', NULL, 'Wife'),
    ('122239', NULL, 'Handicap'),
    ('122239', NULL, 'Children');

    You really aren't doing anything right at this point, and you need to stop and educate yourself. In SQL forum is not the place to get an education.

    1 person found this answer helpful.
    0 comments No comments

  2. Kenny Gua 411 Reputation points
    2021-04-12T17:53:31.167+00:00

    Hi, The relatedid/pos is not correct for EID-122239. I just modified the data for relation field and run the query and Wife's relatedid/pos is not correct. The only condition is here that Wife's number should be last number if any Eid has more records. I help in this update script.

    Insert into #temp values ('122239',NULL, 'Wife')
    Insert into #temp values ('122239',NULL, 'Handicap')
    Insert into #temp values ('122239',NULL, 'Children')
    Insert into #temp values ('122239',NULL, 'Children')
    Insert into #temp values ('122239',NULL, 'Z')

    --Current
    EId pos relation
    122239 1 Children
    122239 2 Children
    122239 3 Handicap
    122239 4 Wife
    122239 5 Z

    --Required
    EId pos relation
    122239 1 Children
    122239 2 Children
    122239 3 Handicap
    122239 5 Wife
    122239 4 Z


  3. AndreiFomitchev 91 Reputation points
    2021-04-12T21:50:17.903+00:00

    -- Working Example:
    id EId RelatedID relation
    2 122233 1 Children
    3 122233 2 Children
    1 122233 3 Wife
    4 122234 1 Children
    7 122235 1 Children
    6 122235 2 Handicap
    5 122235 3 Wife
    8 122237 1 Wife
    11 122239 1 Children
    12 122239 2 Children
    13 122239 3 Children
    10 122239 4 Handicap
    14 122239 5 Z
    9 122239 6 Wife

    DECLARE @temp table (id INT IDENTITY, EId char(10), RelatedID char(1), relation char(10))
    Insert into @temp values ('122233',NULL, 'Wife')
    Insert into @temp values ('122233',NULL, 'Children')
    Insert into @temp values ('122233',NULL, 'Children')

    Insert into @temp values ('122234',NULL, 'Children')

    Insert into @temp values ('122235',NULL, 'Wife')
    Insert into @temp values ('122235',NULL, 'Handicap')
    Insert into @temp values ('122235',NULL, 'Children')

    Insert into @temp values ('122237',NULL, 'Wife')

    Insert into @temp values ('122239',NULL, 'Wife')
    Insert into @temp values ('122239',NULL, 'Handicap')
    Insert into @temp values ('122239',NULL, 'Children')
    Insert into @temp values ('122239',NULL, 'Children')
    Insert into @temp values ('122239',NULL, 'Children')
    Insert into @temp values ('122239',NULL, 'Z')

    SELECT Id, EId, RelatedId, Relation,
    ROW_NUMBER() OVER (PARTITION BY EID
    ORDER BY CASE
    WHEN relation='Wife' THEN 9999
    WHEN relation='Children' THEN 1
    WHEN relation='Handicap' THEN 2
    WHEN relation='Z' THEN 3
    ELSE 4 END
    ) AS rn
    FROM @temp
    DECLARE @id INT, @rn INT

    DECLARE cr CURSOR FOR
    SELECT Id, -- EId, RelatedId, Relation,
    ROW_NUMBER() OVER (PARTITION BY EID
    ORDER BY CASE
    WHEN relation='Wife' THEN 9999
    WHEN relation='Children' THEN 1
    WHEN relation='Handicap' THEN 2
    WHEN relation='Z' THEN 3
    ELSE 4 END
    ) AS rn
    FROM @temp
    OPEN cr
    WHILE 1=1 BEGIN
    FETCH NEXT FROM cr INTO @id, @rn
    IF @@Fetch _STATUS <> 0 BREAK
    UPDATE @temp SET RelatedId = @rn WHERE id = @id
    END
    CLOSE cr
    DEALLOCATE cr

    SELECT * FROM @temp ORDER BY EId, RelatedID

    0 comments No comments