mathurin68 mathurin68 - 6 months ago 15
MySQL Question

Mysql insert column and data from one table based on EXISTS

Not sure why this won't work, trying to insert column(from table) and data(not from any table) from one table into another IF it isn't already there.

INSERT INTO event_tags ( event_id, tag_id )
VALUES (SELECT events.id, 2 FROM events WHERE events.info = 'HybridAnalysis')
WHERE NOT EXISTS (SELECT events.id=event_tags.event_id AND event_tags.tag_id=2);


Tables

Events
id Info
1 ThreatExpert
2 HybridAnalysis
3 ThreatExpert
4 HybridAnalysis

Event_tags
event_ID tag_id
3 1
4 2


(eventsID is not primary)

Event_tags(table) should end up looking like this -

Event_tags
event_id tag_id
3 1
4 2
1 1
2 2


This is my error - ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT events.id, 3 FROM events WHERE events.info = 'HybridAnalysis') WHERE NOT' at line 2

Answer

Can you try this?

INSERT INTO event_tags ( event_id, tag_id ) 
SELECT  events.id, 2 
FROM events AS E
WHERE E.info = 'HybridAnalysis'
AND NOT EXISTS 
(SELECT 
  1 
FROM  event_tags AS ET 
WHERE E.id=ET.event_id AND ET.tag_id=3
);