Home How can i update a table using the output from a inner join query performed on 2 other tables?

Questions

How can i update a table using the output from a inner join query performed on 2 other tables?

March 31, 2023

So the above two tables are the ones that I perform my inner join on and insert those values in a new table.
The values in the "detected" table gets updated through a face detection code, while the values in the table "students" is pre-fed

DDL FOR TABLE ‘students’

DDL FOR TABLE ‘detected’

DDL FOR TABLE ‘tybscit3yr’

insert into bscit3yr
select students.ROLLNO, students.SNAME, detected.TIMING, detected.RDATE
from detected
inner join students on detected.SNAME = students.SNAME
where students.DEPARTMENT = "BSCIT" and students.ACADEMICYEAR = "TY"

The problem is I don’t want duplicate values in my new table-"bscit3yr", since it runs the inner join query multiple times it shows me the error for duplicate entries

mysql.connector.errors.IntegrityError: 1062 (23000): Duplicate entry ‘2021505’ for key ‘ROLLNO_UNIQUE’.

This is the error that I get when the output of the inner join already exists in my "bscit3yr" table

>Solution :

In your query you will have to eliminate ROLLNO that are already in table bscit3yr

insert into bscit3yr
select students.ROLLNO, students.SNAME, detected.TIMING, detected.RDATE
from detected
inner join students on detected.SNAME = students.SNAME
where students.DEPARTMENT = "BSCIT" and students.ACADEMICYEAR = "TY" 
and students.ROLLNO NOT IN (select ROLLNO from bscit3yr)