CODEWITHSUNDEEP
mysqlsqljdbc
Bill
Bill

Reputation: 21

MYSQL: Getting existing primary key when inserting record with duplicate unique key?

I've got a mysql database with a table that has both a auto-increment primary key and unique string valued key (a sha-1 hash).

If I try to add a record that has the same sha-1 hash as an existing record, I just want to get the primary key of the existing record. I can use something like "INSERT ... ON DUPLICATE KEY UPDATE" or "INSERT IGNORE" to prevent an exception when trying to insert a record with a existing hash value.

However, when that happens, I need to retrieve the primary key of the existing record. I can't find a way to do that with a single SQL statement. If it matters, my code is in Java and I'm using JDBC.

Alternatively, I can do it with two statements (either a query followed by an insertion if not found, or a insertion followed by a query if a duplicate key exists). But I presume a single statement would be more efficient.

Upvotes: 0

Views: 2009

Answers (2)

bluecollarcoder
bluecollarcoder

Reputation: 14399

Actually, INSERT...ON DUPLICATE KEY UPDATE is exactly the right statement to use in your situation. When you use ON DUPLICATE, if the insert happens without duplicate, JDBC returns count of 1 and the ID of the newly inserted row. If the action taken is an update due to duplicate, JDBC returns count of 2 and both the ID of the original row AND the newly generated ID, even though the new ID is never actually inserted into the table.

You can get the correct key by calling PreparedStatement.getGeneratedKeys(). The first key is pretty much always the one you are interested in. For this statement:

INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=3;

You can get the inserted or updated ID by calling:

Long key;
ResultSet keys = preparedStatement.getGeneratedKeys();
if (keys.next())
    key = keys.getLong("GENERATED_KEY");

Upvotes: 0

Sylvain Leroux
Sylvain Leroux

Reputation: 51990

If I try to add a record that has the same sha-1 hash as an existing record, I just want to get the primary key of the existing record. I can use something like "INSERT ... ON DUPLICATE KEY UPDATE" or "INSERT IGNORE" to prevent an exception when trying to insert a record with a existing hash value.

If you have an UNIQUE index on a column, no matter what you tried, the RDMS will not allow duplicates in that column (except for the NULL value).

As you said, there is solution to prevent "error" if this appends. Probably INSERT IGNORE in your case.

Anyway, INSERT and UPDATE modify the database. MySQL never return values for these statements. The only way to read your DB is to use a SELECT statement.

Here the "workaround" is simple, since you have an UNIQUE column:

INSERT IGNORE INTO tbl (pk, sha_key) VALUES ( ... ), ( ... );
SELECT pk, sha_key FROM tbl WHERE sha_key IN ( ... );
--                                             ^^^
--             Here the list of the sha1 keys you *tried* to insert

Upvotes: 2

Related Questions