question

KennyGua-7621 avatar image
0 Votes"
KennyGua-7621 asked AndreiFomitchev published

Counter digit update for the Ids

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

sql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

YitzhakKhabinsky-0887 avatar image
1 Vote"
YitzhakKhabinsky-0887 answered KennyGua-7621 commented

Hi @KennyGua-7621,

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;
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@KennyGua-7621,
I updated the answer to support 'Z'.

0 Votes 0 ·
KennyGua-7621 avatar image KennyGua-7621 YitzhakKhabinsky-0887 ·

Thanks your help

0 Votes 0 ·
KennyGua-7621 avatar image
0 Votes"
KennyGua-7621 answered YitzhakKhabinsky-0887 commented

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

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @KennyGua-7621,

Please delete this "answer" because it is not an answer.
If you need to provide any adjustments, just edit your original question.
Again, you need to add a primary key to a table.

0 Votes 0 ·
JoeCelko-6699 avatar image
1 Vote"
JoeCelko-6699 Suspended answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AndreiFomitchev avatar image
0 Votes"
AndreiFomitchev answered AndreiFomitchev published
  • 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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.