How to populate a table based on a value from a different table-CodePudding

I have two tables of data which I can join using a left join linked on the ID in both tables. Where the course and the person are the same, I need to populate the RegNumber as the same as the RegNumber which is already there for 1 row:

How it is currently: if I join table 1 and table 2 with a left join.

Table 1

ID      |    Course|    Person
67705   |    A     |    1
68521   |    A     |    1
85742   |    A     |    1 
89625   |    A     |    1
67857   |    B     |    2
86694   |    B     |    2
88075   |    B     |    2
88710   |    C     |    3
47924   |    C     |    3
66981   |    C     |    3
12311   |    B     |    1
12312   |    B     |    1
12313   |    B     |    1

Table 2

ID      |   RegNumber
67705   |    N712316
NULL    |    NULL
NULL    |    NULL
NULL    |    NULL
67857   |    N712338
NULL    |    NULL
NULL    |    NULL
NULL    |    NULL
47924   |    M481035
NULL    |    NULL
12311   |    N645525
NULL    |    NULL
NULL    |    NULL

I need table 2 to look like this:

ID      |  RegNumber
67705   |  N712316
68521   |  N712316
85742   |  N712316
89625   |  N712316
67857   |  N712338
86694   |  N712338
88075   |  N712338
88710   |  N712338
47924   |  M481035
66981   |  M481035
12311   |  N645525
12312   |  N645525
12313   |  N645525

That is, I need to insert new rows into Table 2

Can anyone help me please? This is Totally beyond my capability!

CodePudding user response：

insert into table2 (ID,RegNumber)
select t1.ID,reg.regNumber
from table1 t1
cross join (select top 1 regNumber from table2 r2 join table1 r1 
            on r1.Id = r2.Id 
            and r1.Course = t1.Course 
            and r1.Person = t1.person 
            order by id) reg
where not exists (select 1 from table2 t2 where t1.ID = t2.ID)

you can improve performance a little bit by loading data into temp table first :

select t1.ID , Course,Person,regNumber
into #LoadedData
from table1 t1
join table2 t2 on t1.Id = t2.ID 


insert into table2 (ID,RegNumber)
select t1.ID,reg.regNumber
from table1 t1
cross join (select top 1 regNumber from #LoadedData l
            where l.Course = t1.Course 
            and l.Person = t1.person 
            order by id) reg
where not exists (select 1 from  #LoadedData l where t1.ID = l.ID)

in either case having an index on (ID, Course, Person) will help with performance

CodePudding user response：

Assuming:

You are missing items in table 2 that inherit data from other records in table 1.
What makes two different IDs share the same Regnumber is to have BOTH course and person number in common.

You really need to join table 1 to itself to create the mapping that associates ID 67705 with ID 68521, then you can join in table 2 to pick up the Regnumber.

Try this:

Insert into table2 (ID,RegNumber)
Select right1.ID, left2.RegNumber
From (
    (table2 left2 INNER JOIN
     table1 left1 On (left1.ID=left2.ID)
     INNER JOIN table1 right1 On (left1.Course=right1.Course AND left1.Person=right1.Person)
    ) LEFT OUTER JOIN table2 right2 On (right1.ID=right2.ID)
WHERE right2.ID Is Null

The 4th table join (alias right2) is purely defensive, to handle two records in table2 having identical Person & Course in table1.

CodePudding user response：

I have solved this myself. I concatenated the person and course columns and then joined them using that new concatenated field

insert into table 2 (ID,RegNumber)
select X1.ID,X2.Regnumber 
from (select concat(course,person) as X,ID from table1) X1
join (select concat(t1.course,t1.person) as X, t2.RegNumber 
      from table1 t1
      join table2 t2 on t1.ID = t2.ID) X2 
on X1.X = X2.X
where X1.ID not in (select ID from table2)