0

I need to insert new row into table foo. But before insert those data, I need to check there was already inserted a row for the respective user name. If there has been already inserted, then I need to update the current data with the new data.

I know to do this using PHP if condition. But I love to do this using MySQL functions/statements by just a one line. Please can anyone help me?

For the example, kindly use the following statement. It should be updated.

$in = "insert into foo(username, text) values('user-x', 'user-x-text')";
Mysql_query($in);

When searching for similar questions, I got this post: Similar question with an answer. But I was struggle to use that solution since I don't know, the process occur by that code snippet will get down the server resources like speed etc. Because this script will run about 20 times per user.

Thank you.

Community
  • 1
  • 1
Janaka R Rajapaksha
  • 3,585
  • 1
  • 25
  • 28
  • related: http://stackoverflow.com/questions/1218905/how-do-i-update-if-exists-insert-if-not-aka-upsert-or-merge-in-mysql – talkol Jan 30 '13 at 15:46
  • @talkol: Okay, is it use all the columns that indicated to detect there is being insert a duplicate one? I need to be it duplicate if only the username exist. But in my table, username is not a primary key and it is only an index key. – Janaka R Rajapaksha Jan 30 '13 at 15:57
  • And it shouldn't get as a duplicate if the same text detected. – Janaka R Rajapaksha Jan 30 '13 at 16:11
  • I think if username is UNIQUE, it doesn't have to be a primary key for on duplicate to work – talkol Jan 30 '13 at 16:15
  • also, take a look at the mysql-specific REPLACE INTO (http://www.mysqltutorial.org/mysql-replace.aspx) – talkol Jan 30 '13 at 16:18
  • @talkol replace into is the thing exactly I was looking for. I knew there should be a shortcut for this. Thanks talkol. I don't know how to rate up your comment. Anyway your comment was solved my problem. – Janaka R Rajapaksha Jan 30 '13 at 16:33
  • I don't think REPLACE INTO is the best solution (http://stackoverflow.com/questions/9168928/what-are-practical-differences-between-replace-and-insert-on-duplicate-ke and http://code.openark.org/blog/mysql/replace-into-think-twice). I think you can make it work with ON DUPLICATE KEY, and then accept my answer ;) – talkol Jan 30 '13 at 16:36
  • Yes, you are right. According to liks that you passed, 'replace into' deletes the row completely. But I thought it will delete only mentioned column data in the statement. That is why I told my problem solved. – Janaka R Rajapaksha Jan 30 '13 at 16:54

1 Answers1

1

I think INSERT ... ON DUPLICATE KEY UPDATE should be able to work

Make username a UNIQUE index, it doesn't have to be a primary key

If I'm not mistaken, DUPLICATE KEY will run only when you have a collision in any of the columns you supply that is either a primary key or unique index. In your case, text column is neither so it will be ignored for collisions.

INSERT... ON DUPLICATE KEY UPDATE works on unique indexes as confirmed by the MySql docs

talkol
  • 12,564
  • 11
  • 54
  • 64
  • Okay, I can set that key to be unique. Then should I use where clause after the update values? In future, I need to add more column to this table. Even after I add those, I still need to allow it to be duplicate if and only if the username is detected. – Janaka R Rajapaksha Jan 30 '13 at 16:57
  • That's the code I had in mind: INSERT INTO foo (username,text) VALUES ('user-x','user-x-text') ON DUPLICATE KEY UPDATE text='user-x-text'; – talkol Jan 30 '13 at 17:01
  • You can add as many columns as you want and it will only look at username as long as in the list of columns in the statement: foo(username,text) the only column that is either primary key or unique is only username – talkol Jan 30 '13 at 17:03