SQL SERVER, ID column update to unique value

Sgroomy 0
Viewed 52

Good morning, i have a big problem with an SQL server that hasn't been setted in the properly way, that server has some table with relations. i have a TableA where there are 2 columns, ID and NUMBER_ORDER. i have a TableB where there are 10 columns, the first one is a column of ID's related to the TableA, and other columns are related to the TEST of each NUMBER_ORDER. Example: TABLEA

ID NUMBER_ORDER
1 110000
2 110001
3 110002
4 110003

TABLEB

ID Test date NumberofTest
1 this is a test for 110000 20/09/2022 10:15 1
1 this is another test for 110000 20/09/2022 10:20 2
2 this is a test for 110001 21/09/2022 9:10 1
2 this is another test for 110001 21/09/2022 9:20 2

As u can see in TableB each ID of TableA appear more times because there are different Tests, this has suppose to be like this.

Now I explain my problem:

In this tables no PrimaryKey index has been setted. what just happened as u can imagine is that now TableA have double ID's for different NUMBER_ORDER in Table_B didn't changed a lot, cause records are writed correctly, but for example if ID "1" is doubled we have it doubled as well in TableB, like this, Example DOUBLEID: TABLEA

ID NUMBER_ORDER
1 110000
1 110001
2 110002
3 110003

TABLEB

ID Test date NumberofTest
1 this is a test for 110000 20/09/2022 10:15 1
1 this is another test for 110000 20/09/2022 10:20 2
1 this is a test for 110001 21/09/2022 9:10 1
1 this is another test for 110001 21/09/2022 9:20 2

My question is:

How can i reset ID column of TableA without loosing the relation in TableB to make all ID's that i have Unique ? Imagine that i have something like 1000 ID doubled in Table A, and usually for Each ID in TableB there are like 150 rows of different Tests, so i can't update them manually. thanks in advance if you only take the time to read it. i hope my question is quiet clear.

EDIT: Does exist a way maybe to delete all numbers from id column and reset them with an auto_increment or a cycle ? if i do it in TableA it will do the same on TableB automatically ? Because it will be perfect, the ID in those 2 table is only a relation to see what test is related to what NUMBER_ORDER so it doesn't matter if it change from 1 to 2 but the important thing is to make it unique.

Best regards.

As requested i'll try to make the tables more realistic as possible.

TableA as should it be:

ID ORDER
1 112563
2 116558
3 113365
4 102567
5 104758
6 105967

TableB as should it be :

ID teststep key min max value res Time
1 AFL AFL_1_engine 3500 3700 3567 OK 2018-12-19 16:52
1 AFL AFL_4_sitting - - - OK 2018-12-19 16:53
2 AFL AFL_1_engine 3500 3700 3780 NOK 2019-10-15 10:00
2 AFL AFL_1_engine - - - OK 2019-10-15 10:02

exc... for others ID's

tsql sql-server sql
1 Answers

When the correct values, that should be in TableA are returned by this query:

select
  row_number() over (order by value) as Id,
  value as NUMBER_ORDER
FROM (
   select 
      distinct  value
   from TableB
   cross apply string_split(test,' ')
   where value like '1%'
  ) x

You should be helped with this DBFIDDLE

Luuk0
Related