phpknight Posted November 8, 2007 Share Posted November 8, 2007 Hi, I would like to know if there is any easy way (one step) in mysql to insert something if it does not already exist in the table provided that the query does not include a delete or replace command since I do not allow these on the server. The only way I can think would be to do a select, followed by an insert, but I can easily see how that might not work given certain conditions. Quote Link to comment Share on other sites More sharing options...
roopurt18 Posted November 8, 2007 Share Posted November 8, 2007 Add a unique constraint on whatever columns. Then you can insert until your heart's content but the DB will only allow a single unique entry. Quote Link to comment Share on other sites More sharing options...
phpknight Posted November 8, 2007 Author Share Posted November 8, 2007 I knew I was forgetting something. The other one is that I do not want the query to fail or give an error, and I think that one would. So, I guess what I am looking for is some kind of IF NOT EXISTS...DO THIS. Quote Link to comment Share on other sites More sharing options...
trq Posted November 8, 2007 Share Posted November 8, 2007 You'll want to look at the REPLACE INTO syntax. Quote Link to comment Share on other sites More sharing options...
phpknight Posted November 8, 2007 Author Share Posted November 8, 2007 Is there any way to do something like that without replace/delete permissions? I am trying to keep the database very safe, so I do not actually allow deletions. Quote Link to comment Share on other sites More sharing options...
fenway Posted November 8, 2007 Share Posted November 8, 2007 I knew I was forgetting something. The other one is that I do not want the query to fail or give an error, and I think that one would. So, I guess what I am looking for is some kind of IF NOT EXISTS...DO THIS. Not exactly... you can use INSERT IGNORE. Quote Link to comment Share on other sites More sharing options...
phpknight Posted November 9, 2007 Author Share Posted November 9, 2007 Hi all, I have been thinking about this problem and going through the docs. I believe I might need this type of query: INSERT ... ON DUPLICATE KEY UPDATE Hopefully it does the job. Quote Link to comment Share on other sites More sharing options...
roopurt18 Posted November 9, 2007 Share Posted November 9, 2007 Check your MySQL version. You must be 4.1 or higher I believe. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.