• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 552
  • Last Modified:

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

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
Jeremy Leys
Asked:
Jeremy Leys
  • 2
  • 2
1 Solution
 
Terry WoodsIT GuruCommented:
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
 
Terry WoodsIT GuruCommented:
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
 
Jeremy LeysTechnical LeadAuthor Commented:
Proof of unique url_sef
Yes the url_sef is set to be a unique

(Storage engine is MyISAM)
0
 
Jeremy LeysTechnical LeadAuthor Commented:
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

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