Solved

How to use MySQL on duplicate key update with unique key?

Posted on 2013-11-20
4
545 Views
Last Modified: 2013-11-20
Hi,

I have a insert on duplicate key update query I want to run to check if there is a record in the sitemap, update it if it exists or insert a new record if it doesn't exist.

Table being queried table structure
In my case the potential duplicate key is a unique text field (url_sef), my query inserts a new record perfectly but does not update an existing one. Can anyone suggest any changes I could make to get this to work or perhaps using a different method all together?

INSERT INTO health3_acesef_sitemap 
(url_sef, published, sdate, frequency, priority, sparent, sorder) 
VALUES ('community-support-services/budgeting-a-financial-services/golden-bay-budget-service', 1, NOW(), 'weekly', '0.5', 0, 1000) ON DUPLICATE KEY UPDATE published=IF(url_sef='community-support-services/budgeting-a-financial-services/golden-bay-budget-service', published=1, published);

Open in new window

0
Comment
Question by:jwleys
[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
  • 2
4 Comments
 
LVL 35

Accepted Solution

by:
Terry Woods earned 500 total points
ID: 39664775
I haven't done this before myself, but the manual states you need a unique index or primary key on the field with which you want to avoid having duplicates (url_sef in your case). Do you have that at the moment?
0
 
LVL 35

Expert Comment

by:Terry Woods
ID: 39664794
Also, I think the value specified in your "if" in your query was incorrect. I think it should be:
INSERT INTO health3_acesef_sitemap 
(url_sef, published, sdate, frequency, priority, sparent, sorder) 
VALUES ('community-support-services/budgeting-a-financial-services/golden-bay-budget-service', 1, NOW(), 'weekly', '0.5', 0, 1000) ON DUPLICATE KEY UPDATE published=IF(url_sef='community-support-services/budgeting-a-financial-services/golden-bay-budget-service', 1, published);

Open in new window

0
 

Author Comment

by:jwleys
ID: 39664802
Proof of unique url_sef
Yes the url_sef is set to be a unique

(Storage engine is MyISAM)
0
 

Author Closing Comment

by:jwleys
ID: 39664843
Thanks you were right, url_sef needed to be set to be an index, it was set to be unique but it needed to be unique and an index. You were also right about the query, typo on my behalf.
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Trouble with <> 2 27
MySQL limit and not so limited 13 41
MySQL Finding Duplicates in a Normalized Database 6 30
Please explain Equi-join 3 15
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

726 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