Gaming World Forums

General Category => Technology and Programming => Topic started by: Mince Wobley on April 15, 2011, 06:06:57 pm

Title: Mysql INSERT ON DUPLICATE KEY UPDATE
Post by: Mince Wobley on April 15, 2011, 06:06:57 pm

Pretend I have a table like id, name, address


And I want to insert something into it, then now the table is like


1, john, john's address


Is there an way to, when I try to add another john to that table with a different address, instead making the table like


1, john, john's address
2, john, john's new address


it becomes like


1, john, john's new address  ? Is that what insert on duplicate key update is for?

Title: Mysql INSERT ON DUPLICATE KEY UPDATE
Post by: dada on April 15, 2011, 06:24:09 pm

You need to do something like:

Code:[Select]

insert into `table` (
   name,
   address
) values (
   'john',
   'john\'s new address'
) on duplicate key update `name` = values(`name`), `address` = values(`address`);


Didn't test this though (and also it's MySQL specific)


edit: of course best way to do this is to name `name` the primary and ditch the ID.

Title: Mysql INSERT ON DUPLICATE KEY UPDATE
Post by: Mince Wobley on April 15, 2011, 06:55:44 pm

Thanks dada, I love you

Title: Mysql INSERT ON DUPLICATE KEY UPDATE
Post by: Mince Wobley on April 15, 2011, 06:59:43 pm

But like, what if there is an ID I cannot ditch?

Title: Mysql INSERT ON DUPLICATE KEY UPDATE
Post by: dada on April 15, 2011, 07:02:29 pm

Er, I'm not sure but what if you make name a unique key and then just not pass the ID in the insert query?

Title: Mysql INSERT ON DUPLICATE KEY UPDATE
Post by: Pulits on April 25, 2011, 09:46:17 pm

Normalization. Separate your tables into:

Employees (idE, name)
Addresses (idA, idE, address)

That way you'll only need to update the table addresses, the importance relies into having employers' id as a foreign key.

Title: Mysql INSERT ON DUPLICATE KEY UPDATE
Post by: dada on April 25, 2011, 09:53:14 pm

Yeah that's a good idea, but since he apparently can't get rid of the ID I guess he's working with some system that others rely on and can't be modified very easily. He should do it if possible though.

Title: Mysql INSERT ON DUPLICATE KEY UPDATE
Post by: Pulits on April 25, 2011, 10:13:06 pm

If that's the case:

UPDATE employees
SET adress = newadress
WHERE id = '1'

There could be more elaborated answers with TRIGGERS and STORED PROCEDURES, I suppose it depends on the complexity of your dB. I'd go with SP, since it can receive parameters ('id', in this case).

Title: Mysql INSERT ON DUPLICATE KEY UPDATE
Post by: Mince Wobley on April 26, 2011, 12:52:13 am

Thanks, I used insert into blah blah blah ignore passing the ID every time I update a record and it quite works