MYSQL _ Replace?

Question

I have 3 tables.

Table A
id (key)	name	date	tel
1	Adam	2021/05/30	5552345
2	Bronwyn	2021/04/21	5557890
3	Peet	2021/03/12	5555677

Table B
id (key)	hair	eyes	tel
1	blond	blond	5552345
2	brown	green	5557890
3	red	green	5555677

Table C
id (key)	name	tel	shirt	hair	eyes
1	Adam	5552345	blue	blond	blond
2	Bronwyn	5557890	red	brown	green

Now if I need to add in Table C Peet with shirt = green, I can just do a insert and use inner joins to get data from Table A and Table B

Table C
id (key)	name	tel	shirt	hair	eyes
3	Peet	5555677	green	red	green

But I am not sure if the Peet information is in the table or not, so it should be a update or replace statement?

Can you use inner join with replace?

Answer 1

Like i said in the comment, you need a UNIQUE constraint so that Mysql knows when it has to update the row

With UNIQUE(name) every time MySQL finds a Peet in the insert statement it will update all other olumns tel, shirt, hair, eyes with the provided new data

CREATE TABLE TableC (
  `id` INTEGER AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(7),
  `tel` INTEGER,
  `shirt` VARCHAR(10),
  `hair` VARCHAR(10),
  `eyes` VARCHAR(10),
  UNIQUE(`name`)
);

INSERT INTO TableC
  (`id`, `name`, `tel`, `shirt`, `hair`, `eyes`)
VALUES
  ('1', 'Adam', '5552345', 'blue', 'blond', 'blond'),
  ('2', 'Bronwyn', '5557890', 'red', 'brown', 'green');

INSERT INTO TableC (`name`, `tel`, `shirt`, `hair`, `eyes`)
VALUEs ('Peet',   5555677,    'green',    'red' , 'green')
ON DUPLICATE KEY UPDATE `tel` = VALUES(`tel`) , `shirt`= VALUES(`shirt`)
, `hair` = VALUES(`hair`) , `eyes` = VALUES(`eyes`)

INSERT INTO TableC (`name`, `tel`, `shirt`, `hair`, `eyes`)
VALUEs ('Peet',   5555677,    'red',  'red' , 'green')
ON DUPLICATE KEY UPDATE `tel` =VALUES(`tel`) , `shirt`= VALUES(`shirt`)
, `hair` = VALUES(`hair`) , `eyes` = VALUES(`eyes`)

SELECT * FROM TableC

id | name    |     tel | shirt | hair  | eyes 
-: | :------ | ------: | :---- | :---- | :----
 1 | Adam    | 5552345 | blue  | blond | blond
 2 | Bronwyn | 5557890 | red   | brown | green
 3 | Peet    | 5555677 | red   | red   | green

db<>fiddle here