0

Currently I have data that it must has 1 title and 1 content, and randomly number (or none) link group (linktext and link ) , (as the it is dynamic generated) E.g. LinkText1 , Link1 , LinkText2, Link2....and so on. So, one example data is:

title:"abc"
content:"test"
linktext1:"yahoo"
link1:"http://yahoo.com"

The problem is if I would like to update the data in minimum step. The approach I can think of is:

  1. update the title and content
  2. select its id
  3. Base on the id to select the related linktext, link
  4. If it exist, update
  5. If not exist , insert
  6. If before exist , but now not exist, delete

I would like to do this in more elegant way. Are there any combine SQL or which way can I minimum the query ? Thanks

Updated : Data Structure:

Table: news
id
title
content

Table: news_link
id
title
url
news_id

news and news_link is 1=m relation.

news_id is the foreign key of news , thanks

user782104
  • 13,233
  • 55
  • 172
  • 312
  • 1
    We need a whole lot more information. Is the user changing this information? If you updated the title and content in step 1 you don't need step 2 because you probably already had the id to do the update. Is the table with the title and content in a one to many relationship with the link table? If so why not delete all the links and reinsert them or you could so a insert on duplicate key update. More info is needed. – Casey Oct 09 '13 at 02:57
  • 1
    http://stackoverflow.com/questions/4205181/insert-to-table-or-update-if-exists-mysql that should be helpful it explains REPLACE INTO and INSERT .. ON DUPLICATE KEY UPDATE, but both require a unique key to look for. – Bryan Oct 09 '13 at 02:58
  • updated table structure, Thanks for helping – user782104 Oct 09 '13 at 03:04
  • I have read the link,thanks. Do I need to select the key first? – user782104 Oct 09 '13 at 12:35

0 Answers0