CODEWITHSUNDEEP
mysqlinsertwhere-clause
Musikdoktor
Musikdoktor

Reputation: 144

Mysql - INSERT on TABLE 1 from SELECT on TABLE 2 with WHERE NOT EXIST on Table 1

Table1 has unique_id, ID_actor, ID_media, ID_program.

Each media may contain 1 or more programs, each programs may contain 1 or more actors and cannot be duplicated.

In another table (Table2) it has more info, sometimes in the title people record the name of the actor.. so i found this query to make the right reference to the media

INSERT INTO Table1 (ID_Actor,ID_Media,ID_Program) 
    (SELECT 123, ID_Media, ID_Program FROM TABLE2 WHERE Title LIKE '%ActorsName%')

This code works well but if already the actor was referenced to the program on the media, it will be duplicated.. so, since insert seems doesn't support WHERE don't know how to check first if the actor is already on table1, on that media and on that program comming from the SELECT query.

INSERT IF IS NOT PRESENT

    (SELECT ID_Actor,ID_Media,ID_Program from Table1 
WHERE ID_Actor = 123 
    AND ID_Media = (ID_media from SELECT QUERY) 
AND  ID_Program = (ID_Program From Select Query))

Upvotes: 0

Views: 971

Answers (1)

Uueerdo
Uueerdo

Reputation: 15941

Odd you missed the feature, since your question title has almost the exact syntax you need.

INSERT INTO Table1 (ID_Actor,ID_Media,ID_Program) 
SELECT DISTINCT 123, t2.ID_Media, t2.ID_Program 
FROM TABLE2 AS t2
WHERE t2.Title LIKE '%ActorsName%'
   AND NOT EXISTS (
      SELECT 1 
      FROM Table1 AS t1
      WHERE t1.ID_Actor = 123
         AND t1.ID_Media = t2.ID_Media
         AND t1.ID_Program = t2.ID_Program
);

Upvotes: 1

Related Questions