Insert in a table only when the registration does not exist

Question

Insert in a table only when the registration does not exist

Navigation

#1 by (1 votes)

0

I have a table in MySql and in it I want to enter data from another table located in a different database, the data is inserted correctly if I execute the following query:

    INSERT INTO courses_members (course_id, user_id, added, validity, active, suma, fecha_solicitud)
SELECT sc.id AS course_id, sm.id AS user_id, DATE_FORMAT(NOW(), '%Y/%m/%d') AS added,
'0000-00-00' AS validity, 'Activo' AS active, 'si' AS suma, '0000-00-00' AS fecha_solicitud 
FROM chamilo.course cc 
INNER JOIN sm4rtuniversity.courses sc ON cc.title=sc.name 
INNER JOIN chamilo.skill_rel_user sru ON sru.course_id=cc.id
INNER JOIN chamilo.user cu ON cu.id=sru.user_id 
INNER JOIN sm4rtuniversity.members sm ON sm.email=cu.email;

But when wanting to validate that they are inserted only if that record does not exist in the destination table, no result is inserted.

    INSERT INTO courses_members (course_id, user_id, added, validity, active, suma, fecha_solicitud)
SELECT sc.id AS course_id, sm.id AS user_id, DATE_FORMAT(NOW(), '%Y/%m/%d') AS added,
'0000-00-00' AS validity, 'Activo' AS active, 'si' AS suma, '0000-00-00' AS fecha_solicitud 
FROM chamilo.course cc 
INNER JOIN sm4rtuniversity.courses sc ON cc.title=sc.name 
INNER JOIN chamilo.skill_rel_user sru ON sru.course_id=cc.id
INNER JOIN chamilo.user cu ON cu.id=sru.user_id 
INNER JOIN sm4rtuniversity.members sm ON sm.email=cu.email
#INNER JOIN chamilo.course_rel_user cre ON cre.c_id=cc.id
WHERE sc.id NOT IN(SELECT course_id FROM sm4rtuniversity.courses_members)
AND sm.id NOT IN(SELECT user_id FROM sm4rtuniversity.courses_members);

The query does not throw any errors but no results are inserted.

mysql sql

asked by Guillermo Ricardo Spindola Bri 28.03.2018 в 19:47

source

1 answer

Remove top margin with a constraint in Top Layout Render two lists by commas in a bootstrap element by js

score 1 · Accepted Answer

I imagine that the key to your table is the combination of the columns course_id, user_id . This means the combination of both values in a row can not be repeated.

What you are filtering, instead, is that the course does not exist before and that the user does not exist before. So, for example, it is enough that your table has a user for each course so that your filter does not allow inserting any other row.

What you have to do is see that the combination of those rows does not exist before:

INSERT INTO courses_members (course_id, user_id, added, validity, active, suma, fecha_solicitud)
SELECT  sc.id AS course_id, 
        sm.id AS user_id, 
        DATE_FORMAT(NOW(), '%Y/%m/%d') AS added,
        '0000-00-00' AS validity, 
        'Activo' AS active, 
        'si' AS suma, 
        '0000-00-00' AS fecha_solicitud 
FROM chamilo.course cc 
INNER JOIN sm4rtuniversity.courses sc 
    ON cc.title=sc.name 
INNER JOIN chamilo.skill_rel_user sru 
    ON sru.course_id=cc.id
INNER JOIN chamilo.user cu 
    ON cu.id=sru.user_id 
INNER JOIN sm4rtuniversity.members sm 
    ON sm.email=cu.email
INNER JOIN chamilo.course_rel_user cre 
    ON cre.c_id=cc.id
WHERE NOT EXISTS(SELECT 1 FROM sm4rtuniversity.courses_members
                 WHERE course_id = sc.id
                 AND user_id = sm.id)
;