By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
-- Table1
CREATE TABLE Table1 (id INT,selection1 varchar(50),selection2 varchar(50),results varchar(1000));
INSERT INTO Table1 (id,selection1,selection2,results)
VALUES
(1,null,null,null),
(2,null,null,null),
(3,null,null,null);
select * from Table1
| id | selection1 | selection2 | results |
|---|---|---|---|
| 1 | null | null | null |
| 2 | null | null | null |
| 3 | null | null | null |
-- Table2
CREATE TABLE Table2 (stocks1 VARCHAR(50),stocks2 VARCHAR(50));
INSERT INTO Table2 (stocks1,stocks2)
VALUES
-- ('BRK','BRK'),
-- ('BRK','BRK');
('BRK','GOOG'),
('INTC','NKE'),
('TSLA','APPL'),
('APPL','NKE'),
('TSLA','FB'),
('NKE','BRK');
select * from Table2
| stocks1 | stocks2 |
|---|---|
| BRK | GOOG |
| INTC | NKE |
| TSLA | APPL |
| APPL | NKE |
| TSLA | FB |
| NKE | BRK |
CREATE TRIGGER trigger1
BEFORE UPDATE
ON Table1
FOR EACH ROW
BEGIN
set NEW.results =
(SELECT CONCAT('<br>',GROUP_CONCAT( val
SEPARATOR '<br>'))
FROM (SELECT stocks1 as val FROM
Table2
WHERE
NEW.selection2 = 'x'
UNION
SELECT stocks2 FROM
Table2
WHERE
NEW.selection2 = 'x') t1
);
end;
update Table1
set selection1 = 'x', selection2 = 'x'
where id = 1
select * from Table1
| id | selection1 | selection2 | results |
|---|---|---|---|
| 1 | x | x | <br>BRK<br>INTC<br>TSLA<br>APPL<br>NKE<br>GOOG<br>FB |
| 2 | null | null | null |
| 3 | null | null | null |