CODEWITHSUNDEEP
mysqlpdo
Jilco Tigchelaar
Jilco Tigchelaar

Reputation: 2149

PDO insert into with SELECT and ON DUPLICATE KEY UPDATE

I use the below query with succes, this query is only executed if a row in data_advertentie with the ID exists.

INSERT INTO data_contactgegevens (ID_advertentie, telefoonnummer, website)
SELECT ID_advertentie, :telefoonnummer, :website FROM data_advertenties
WHERE ID_advertentie = :ID_advertentie AND unieke_hash_plaatsen = :hash_plaatsen

Now i want to add a ON DUPLICATE KEY, so if the row exists in data_contactgegevens the row wil be updated.

Please help....

Upvotes: 2

Views: 161

Answers (1)

Barranka
Barranka

Reputation: 21047

Add the on duplicate key update piece at the end of your insert statement.

Example

create table a(
  id int not null auto_increment primary key,
  x varchar(10)
);

create table b(
  id int not null auto_increment primary key,
  y varchar(10)
);

insert into a(x) values ('a'), ('b'), ('c');

insert into b(y) values ('e'), ('d');

insert into a
  select * from b
  on duplicate key update x = y;

Result:

select * from a;

| ID | X |
|----|---|
|  1 | e |
|  2 | d |
|  3 | c |

Here's the SQL fiddle for this example.

Upvotes: 1

Related Questions