Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 402
  • Last Modified:

Mysql insert/update with multiple keys

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
gorrie67
Asked:
gorrie67
  • 2
1 Solution
 
SharathData EngineerCommented:
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
 
gorrie67Author Commented:
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
 
SharathData EngineerCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now