1

On insert I am catching the unique constraint mysql_errno() 1062.

This works fine but I want to find the existing row to re-instate or modify it.

Is there are method to obtain the row id on insert fail? I tried mysql_insert_id() but realised that would only return the row I'm inserting (or failed to insert) therefore, I get 0.

Is there no option but to issue another mysql_query and simply perform a select on the duplicate value?

I just want to make sure there is no better, quicker, more economical way to do this.

Improve this question
1
  • No other way but to SELECT first. Commented May 3, 2010 at 5:16

2 Answers 2

Reset to default
2

If you are attempting to insert a row if new or update existing values then REPLACE INTO is what you need. Also consider INSERT ... ON DUPLICATE KEY UPDATE Syntax if there are constraints involved as REPLACE INTO will DELETE and then INSERT.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Doesn't REPLACE INTO delete the existing row? I want to see the row and contents before overwriting, or at least give the user the option to modify it.
It does - if you need to retrieve the row if it already exists then I can see no other option apart from using a SELECT statement.
0

You'd have to check the table for any "unique" keys (SHOW CREATE TABLE will list them all), and then query the table for the associated values in the insert query you'd attempted. So if you're inserting fields A,B,C,D and B,C have the unique key on them, then:

SELECT id, B, C FROM table WHERE B=$B and C=$C;
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.