-1

I know about ON DUPLICATE USE KEY clauses. But I can not use it since I want to update and insert on non-unique column.

I have table1 and table2. I want to create a trigger on table1.

Pseudocode:

IF id_code for corresponding id_name from table1 is present in table2
then update record in table 2
else record in table2.

For Ex. 

table1 has column1 id_code, column2 id_name
table2 has column1 id_code, column2 status

IF id_code for corresponding id_name from table1 is present in table2
UPDATE status column in table2.
ELSE insert id-code in table2
vzwick
  • 10,424
  • 5
  • 39
  • 60
Priyanshu
  • 845
  • 6
  • 12
  • Please refer this http://stackoverflow.com/questions/4205181/insert-into-a-mysql-table-or-update-if-exists – Prabu Guna Mar 01 '16 at 14:02
  • *"I want to create trigger"* -- take a look at some examples: http://dev.mysql.com/doc/refman/5.7/en/trigger-syntax.html – axiac Mar 01 '16 at 14:12
  • Create a trigger, then use insert ... on duplicate key update ... within it. – Shadow Mar 01 '16 at 14:12

1 Answers1

0

Best way would probably be to use a conditional statement, since as you said you are checking for a non unique value and thus cannot use ON DUPLICATE KEY:

IF EXISTS (SELECT id_code FROM table2 WHERE id_code = 'code from table1') THEN
    UPDATE table2 SET status = 'new status' WHERE id_code = 'code from table1';
ELSE
    INSERT INTO table2 (id_code, status) VALUES ('code from table1', 'new status');
END IF;

The only caveat here is that control structures are only valid in stored procedures, so you will need to put this in a stored procedure or a trigger.

ElGavilan
  • 5,747
  • 16
  • 24
  • 35