Solved

Mysql insert/update with multiple keys

Posted on 2011-03-20
3
380 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
PHP Undefined Index in HTML Form? 2 31
How to use md5 hashing 3 25
Php pie charts 3 26
PHPStorm debugging issues 1 20
Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
These days socially coordinated efforts have turned into a critical requirement for enterprises.
The viewer will learn how to count occurrences of each item in an array.
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.

919 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now