Insert New Row id to another table and also Insert record if not exists in table 2, to avoid duplicate entries

Question

Table:1

id	image	cat id
7	asdf	1
15	jhgfv	1
17	dfgh	2
19	rtyu	2
21	fghjk	2

Table:2

id	image	cat id	Column E	Column F	Column G	Column H	Column I

Here I had two tables and from table 1, I want to fetch all 3 column's data and insert them into Table 2

I want to Insert this data with the trigger in MySQL.

AND I WANT Table 2 LIKE ...

id	image	cat id	Column E	Column F	Column G	Column H	Column I
7	asdf	1	'Default Value'	'Default Value'	'Default Value'	'Default Value'	'Default Value'
15	jhgfv	1	'Default Value'	'Default Value'	'Default Value'	'Default Value'	'Default Value'
17	dfgh	2	'Default Value'	'Default Value'	'Default Value'	'Default Value'	'Default Value'
19	rtyu	2	'Default Value'	'Default Value'	'Default Value'	'Default Value'	'Default Value'
21	fghjk	2	'Default Value'	'Default Value'	'Default Value'	'Default Value'	'Default Value'

I TRIED :

INSERT INTO `table 2` (`id`, `cat_id`, `image`, `Column C`, `Column D`, `Column E`, `Column F`, `Column G`, `Column H`, `Column I`) VALUES
(NEW.ID, NEW.cat_id, NEW.image, '', '', '','', '', 'open', 'open', '', '', '', '', '', '', '', 0, '', 0, '', '', '', '', '', 0, '', 0);

HERE >>>>>>>>>   ''  <<<<< IS THE DEFAULT VALUE

But this gives and duplicate values.

To avoid duplicate value I also try this: MySQL: Insert record if not exists in table

INSERT INTO `table 2` (`id`, `cat_id`, `image`, `Column C`, `Column D`, `Column E`, `Column F`, `Column G`, `Column H`, `Column I`) VALUES
(NEW.ID, NEW.cat_id, NEW.image, '', '', '','', '', 'open', 'open', '', '', '', '', '', '', '', 0, '', 0, '', '', '', '', '', 0, '', 0);

HERE \>\>\>\>\>\>\>\>\>   ''  \<\<\<\<\< IS THE DEFAULT VALUE

To avoid duplicate value I also try this

so why didn't the linked question work for you? have you just tried adding a unique index? — nicael, Mar 29 '22 at 20:59
I want to insert new data remove duplicates with trigger in mysql — kkp, Mar 29 '22 at 21:04

score 0 · Answer 1 · answered Mar 29 '22 at 22:32

If id in table2 is a primary key or an unique key, you already can't add the same id twice but it will complain about duplicate if you insert it.

You can use INSERT IGNORE INTO to tell MySQL to simply ignore duplicate in this case.

PS: You should add your tables schemas in the question

Insert New Row id to another table and also Insert record if not exists in table 2, to avoid duplicate entries

Table:1

Table:2

AND I WANT Table 2 LIKE ...

1 Answers1