Solved

Mysql insert/update with multiple keys

Posted on 2011-03-20
3
391 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 41

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 41

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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
This article discusses how to implement server side field validation and display customized error messages to the client.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to dynamically set the form action using jQuery.

635 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