How to insert into the table if the record does not exist [duplicate]

Question

How to insert into the table if the record does not exist [duplicate]

Navigation

#1 by (3 votes)

2

How to insert into the table if the record does not exist, for example the table_materia_materia, has the following information:

+----+-------+---------+
| ID | turma | materia |
+----+-------+---------+
| 1  |  1    |  1      |
| 2  |  1    |  2      |
| 3  |  2    |  2      |

If you try to insert material 1 with class 1 it will check if it has, if you do not give the insert, if it has not it did not insert

mysql sql

asked by anonymous 15.05.2018 / 22:47

1 answer

How to Identify Screen Capture ternary condition or if and else with PHP

score 3 · Accepted Answer

You can use SELECT instead of VALUES in INSERT :

INSERT INTO turma_materia(ID, turma, materia)
SELECT 1, 1, 1
 WHERE NOT EXISTS (SELECT 1
                     FROM turma_materia
                    WHERE ID = 1
                      AND turma = 1
                      AND materia = 1);

If you want to set values only once, you can use subquery in FROM :

INSERT INTO turma_materia(ID, turma, materia)
SELECT x.ID, x.turma, x.materia
  FROM (SELECT 1 AS ID,
               1 AS turma,
               1 AS materia) x
 WHERE NOT EXISTS (SELECT 1
                     FROM turma_materia tm
                    WHERE tm.ID = x.ID
                      AND tm.turma = x.turma
                      AND tm.materia = x.materia);