mysql - INSERT ON DUPLICATE KEY with INNER JOIN -

given t.id, a.id, t1.name , t2.name, how add or update t1_has_t2.data?

i can update if there record.

update t1_has_t2 inner join t1 on t1.id=t1_has_t2.t1_id inner join t2 on t2.id=t1_has_t2.t2_id set t1_has_t2.data=123 t1.name="foo" , t1.t_id=333 , t2.name="bar" , t2.t_id=333; 

how can insert if record doesn't exist?

edit. following? seems waste include t in join.

insert t1_has_t2(t1_id,t2_id,data) select t1.id, t2.id, 123 t inner join t1 on t1.t_id=t.id inner join t2 on t2.t_id=t.id t1.name="foo" , t1.t_id=333 , t2.name="bar" , t2.t_id=333 on duplicate key set t1_has_t2.data=123; 

edit2. ah, maybe now. join t1 , t2 each other through shared t.id?

insert t1_has_t2(t1_id,t2_id,data) select t1.id, t2.id, 123 t1 inner join t2 on t2.t_id=t1.t_id t1.name="foo" , t1.t_id=333 , t2.name="bar" , t2.t_id=333 on duplicate key update t1_has_t2.data=123; 

-- mysql script generated mysql workbench -- 08/08/16 07:40:04 -- model: new model    version: 1.0 set @old_unique_checks=@@unique_checks, unique_checks=0; set @old_foreign_key_checks=@@foreign_key_checks, foreign_key_checks=0; set @old_sql_mode=@@sql_mode, sql_mode='traditional,allow_invalid_dates';  -- ----------------------------------------------------- -- schema mydb -- ----------------------------------------------------- create schema if not exists `mydb` default character set utf8 collate utf8_general_ci ; use `mydb` ;  -- ----------------------------------------------------- -- table `mydb`.`accounts` -- ----------------------------------------------------- create table if not exists `mydb`.`accounts` (   `id` int not null auto_increment,   primary key (`id`)) engine = innodb;   -- ----------------------------------------------------- -- table `mydb`.`t` -- ----------------------------------------------------- create table if not exists `mydb`.`t` (   `id` int not null auto_increment,   `accounts_id` int not null,   primary key (`id`, `accounts_id`),   index `fk_t_accounts_idx` (`accounts_id` asc),   constraint `fk_t_accounts`     foreign key (`accounts_id`)     references `mydb`.`accounts` (`id`)     on delete no action     on update no action) engine = innodb;   -- ----------------------------------------------------- -- table `mydb`.`t1` -- ----------------------------------------------------- create table if not exists `mydb`.`t1` (   `id` int not null auto_increment,   `t_id` int not null,   `t_accounts_id` int not null,   `name` varchar(45) null,   primary key (`id`),   index `fk_t1_t1_idx` (`t_id` asc, `t_accounts_id` asc),   unique index `un1` (`t_id` asc, `name` asc),   constraint `fk_t1_t1`     foreign key (`t_id` , `t_accounts_id`)     references `mydb`.`t` (`id` , `accounts_id`)     on delete no action     on update no action) engine = innodb;   -- ----------------------------------------------------- -- table `mydb`.`t2` -- ----------------------------------------------------- create table if not exists `mydb`.`t2` (   `id` int not null auto_increment,   `t_id` int not null,   `t_accounts_id` int not null,   `name` varchar(45) null,   primary key (`id`),   index `fk_t2_t1_idx` (`t_id` asc, `t_accounts_id` asc),   unique index `un2` (`t_id` asc, `name` asc),   constraint `fk_t2_t1`     foreign key (`t_id` , `t_accounts_id`)     references `mydb`.`t` (`id` , `accounts_id`)     on delete no action     on update no action) engine = innodb;   -- ----------------------------------------------------- -- table `mydb`.`t1_has_t2` -- ----------------------------------------------------- create table if not exists `mydb`.`t1_has_t2` (   `t1_id` int not null,   `t2_id` int not null,   `data` varchar(45) null,   primary key (`t1_id`, `t2_id`),   index `fk_t1_has_t2_t21_idx` (`t2_id` asc),   index `fk_t1_has_t2_t11_idx` (`t1_id` asc),   constraint `fk_t1_has_t2_t11`     foreign key (`t1_id`)     references `mydb`.`t1` (`id`)     on delete no action     on update no action,   constraint `fk_t1_has_t2_t21`     foreign key (`t2_id`)     references `mydb`.`t2` (`id`)     on delete no action     on update no action) engine = innodb;   set sql_mode=@old_sql_mode; set foreign_key_checks=@old_foreign_key_checks; set unique_checks=@old_unique_checks; 

works fine if understand want insert on duplicate key update (iodku).

data load:

insert accounts(id) values (null); -- id = 1 insert t(accounts_id) values (1); -- id = 1  insert t1(t_id,t_accounts_id,name) values (1,1,'n1'); -- id=1 insert t1(t_id,t_accounts_id,name) values (1,1,'n2'); -- id=2 insert t2(t_id,t_accounts_id,name) values (1,1,'n1'); -- id=1    insert t2(t_id,t_accounts_id,name) values (1,1,'n2'); -- id=2  insert t1_has_t2(t1_id,t2_id,data) values(1,1,'one_one'); -- success insert t1_has_t2(t1_id,t2_id,data) values(1,77,'x'); -- error 1452 expected insert t1_has_t2(t1_id,t2_id,data) values(77,1,'x'); -- error 1452 expected insert t1_has_t2(t1_id,t2_id,data) values(1,2,'one_two'); -- success insert t1_has_t2(t1_id,t2_id,data) values(2,1,'two_one'); -- success insert t1_has_t2(t1_id,t2_id,data) values(2,2,'two_two'); -- success 

your query:

update t1_has_t2 inner join t1 on t1.id=t1_has_t2.t1_id inner join t2 on t2.id=t1_has_t2.t2_id set t1_has_t2.data='i string' t1.name="n1" , t1.t_id=1 , t2.name="n1" , t2.t_id=1; 

iodku:

insert t1_has_t2(t1_id,t2_id,data) values(2,2,'two_two_version002') on duplicate key update data='anchovies'; 

see results:

select * t1_has_t2; +-------+-------+---------------+ | t1_id | t2_id | data          | +-------+-------+---------------+ |     1 |     1 | string | |     1 |     2 | one_two       | |     2 |     1 | two_one       | |     2 |     2 | anchovies     | +-------+-------+---------------+ 

you can into

insert ignore t1_has_t2(t1_id,t2_id,data) [something]; 

which succeeds or fails silently design.