Solved

Mysql insert/update with multiple keys

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Part of the Global Positioning System A geocode (https://developers.google.com/maps/documentation/geocoding/) is the major subset of a GPS coordinate (http://en.wikipedia.org/wiki/Global_Positioning_System), the other parts being the altitude and t…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
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.

706 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

19 Experts available now in Live!

Get 1:1 Help Now