Solved

Mysql insert/update with multiple keys

Posted on 2011-03-20
3
382 Views
Last Modified: 2012-05-11
Hi

I am looking for an easy way to do a mysql insert/replace based on whether the data exists or not.

I have a table with 3 keys, Staff ID, DateRef, Line Ref. There is no single unique key, rather the 3 keys make the record unique.

Can I use multiple keys in REPLACE INTO or ON DUPLICATE KEY UPDATE?
Or do I have to do the SELECT - IF FOUND UPDATE, OTHERWISE INSERT.

Thanks
0
Comment
Question by:gorrie67
  • 2
3 Comments
 
LVL 40

Expert Comment

by:Sharath
ID: 35178325
If your primary key is defined on three columns, then you can use "ON DUPLICATE KEY UPDATE" clause.
Did you refer this URL for more details.
http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html
0
 

Author Comment

by:gorrie67
ID: 35184476
Thanks Sharath
I did read the manual, but it made no sense to me when it comes to using 3 columns as the primary key.
An example would be good!
ie  A,B, C fields make up the primary key. D, E, F, G are the remaining fields to be updated.
0
 
LVL 40

Accepted Solution

by:
Sharath earned 125 total points
ID: 35184604
Did you really try that? Here is an example which I have run on my machine. Check that "ON DUPLICATE KEY", the record is updated.
create table PrimTable(a int,b int, c int, d int, e int, f int, primary key (a,b,c));

insert into PrimTable  values (1,2,3,4,5,6);
select * from PrimTable;
+---+---+---+------+------+------+
| a | b | c | d    | e    | f    |
+---+---+---+------+------+------+
| 1 | 2 | 3 |    4 |    5 |    6 |
+---+---+---+------+------+------+
1 row in set (0.00 sec)

insert into PrimTable  values (1,2,3,7,8,9) on duplicate key update d = values(d),e = values(e),f = values(f);
select * from PrimTable;
+---+---+---+------+------+------+
| a | b | c | d    | e    | f    |
+---+---+---+------+------+------+
| 1 | 2 | 3 |    7 |    8 |    9 |
+---+---+---+------+------+------+
1 row in set (0.00 sec)

Open in new window

0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
mysql ide 10 39
unable to insert record into a table 2 29
Upgrade MySQL 5.5 to MySQL 5.6 on Windows 13 25
Number of hours between date in DB and now 8 17
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question