Objective: Verify the existence of an ID string in table1; if yes, insert data into table2, else skip.
I have two tables in a MariaDB db :
table1
| id int(11) auto_increment | myIDString text |
|---|---|
| 1001 | 1234 |
table2
| id int(11) auto_increment | myIDString text | string1 char(32) | string2 char(32) |
|---|---|---|---|
| 1000 | 0123 | Some text 1 | Some text 2 |
Obviously, they are not similar but common for both is a column named myIDString. As data is being processed, table1 is populated first and I get a certain value in myIDString (here 1234).
Further on in the processing I get data variables to feed into table2.string1 and table2.string2.
If at all possible, I try to create a one line Insert like
if exists (select myIDString from table1 where myIDSTring = 1234) then
insert into table2(myIDString, string1, string2) values(1234, $myString1,$myString2);
But so far no luck. I've read posts concerning similar problem and those offered no solution to this. Optimally, I would want a oneliner solution, but secondly, in case I need a trigger to handle this, how would one such look like?
CodePudding user response：
See INSERT .. SELECT
If the value 1234 for MyIDString doesn't exist, nothing will be inserted:
INSERT INTO table2 (myIDString, string1, string2)
SELECT 1234, $myString1, $myString2 FROM table1
WHERE myIDSTring = 1234;
CodePudding user response：
I think that the best solution for my issue is to use a trigger.
DELIMITER // CREATE TRIGGER threadIDcheck BEFORE INSERT ON myID2 FOR EACH ROW BEGIN IF NEW.threadID1 NOT IN (select threadID1 from myID1) THEN SIGNAL SQLSTATE '45001' SET MESSAGE_TEXT = 'That myIDString does not exist in table1'; END IF; END; // DELIMITER ; DELIMITER // CREATE TRIGGER threadIDcheck BEFORE INSERT ON myID2 FOR EACH ROW BEGIN IF NEW.threadID1 NOT IN (select threadID1 from myID1) THEN SIGNAL SQLSTATE '45001' SET MESSAGE_TEXT = 'ThreadID does not exist in table1'; END IF; END; // DELIMITER ; DELIMITER //
CREATE TRIGGER threadIDcheck
BEFORE INSERT ON table2
FOR EACH ROW
BEGIN
IF NEW.myIDString NOT IN (select myIDString from table1) THEN
SIGNAL SQLSTATE '45001' SET MESSAGE_TEXT = 'That myIDString value does not exist in table1';
END IF;
END; //
DELIMITER ;
When I do a simple insert into table2(myIDString) values(1111); and that value is not in table1, then the insert fails gracefully.
Should anyone have some other solution, please feel free to add it in here :).