Insert/update with values from other tables

Question

I have this bd. I want to do an Insert/update the table misses_licao. But I don’t have any id, just the numbers and names. How do I make an Insert/update to relate to another table?

Dim sql As String = "insert into Faltas_Licao values ()"

Answer 1

I didn’t quite understand your question because it is update in the title and context you use Insert, but come on, you can use select to do this as follows.

Declare @disciplinas table
(
    Id_Disciplina int,
    Nome varchar(50),
    Descricao varchar(200),
    Aluno_Id int
)

insert into @disciplinas values(1,'sql','mod 1',1)

Declare @Alunos table
(
    Id_Aluno int,
    Descricao varchar(200),
    Id_Turma int,
    Nome varchar(50)
    -- ......
)
insert into @Alunos values(1,'.....',1, 'joão')
Declare @Licao table
(
    Id_Turma int,
    Id_Disciplina int,
    Licao int
    -- ......
)
insert into @Licao values(1,1,1)
Declare @Turma table
(
    Id_Turma int,
    Nome varchar(50)
    -- ......
)
insert into @Turma values(1,'teste 1')

Declare @Faltas_Licao table
(
    Id_Turma int,
    Id_Disciplina int,
    Licao int,
    Tipo varchar(50),
    Descricao varchar(200),
    Aluno_Id int
)

insert into @Faltas_Licao 

select t.Id_Turma, l.Id_Disciplina,l.Licao, 'falta', 'falta por atraso', a.Id_Aluno
from @Turma t
join @Licao l
on l.Id_Turma = t.Id_Turma
join @disciplinas d
on d.Id_Disciplina = l.Id_Disciplina
join @Alunos a
on a.Id_Aluno = d.Aluno_Id
-- where ...?? 


--ou update
update fl 
set fl.Id_Turma = t.Id_Turma, 
fl.Id_Disciplina = l.Id_Disciplina,
fl.Licao = l.Licao,
fl.Tipo = 'falta',
fl.Descricao = 'outro motivo', 
fl.Aluno_Id = a.Id_Aluno
from @Faltas_Licao fl
join @Turma t
on fl.Id_Turma = t.Id_Turma
join @Licao l
on l.Id_Turma = t.Id_Turma
join @disciplinas d
on d.Id_Disciplina = l.Id_Disciplina
join @Alunos a
on a.Id_Aluno = d.Aluno_Id
-- where ...?? 

select * from @Faltas_Licao