Beefy Boxes and Bandwidth Generously Provided by pair Networks
Don't ask to ask, just ask
 
PerlMonks  

Re: return primary key if duplicate entry exists?

by CountZero (Bishop)
on Jan 24, 2016 at 09:36 UTC ( [id://1153501]=note: print w/replies, xml ) Need Help??


in reply to return primary key if duplicate entry exists?

That SQL will not warn you about duplicate keys.

From the MySQL docs:

If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, MySQL performs an UPDATE of the old row.
In other words, the database will delete the existing entry and replace it with the data in the UPDATE statement.

If you wish to check if the key already exists then you simply issue a SELECT statement on that key and see if it returns any result.

CountZero

A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James

My blog: Imperial Deltronics

Replies are listed 'Best First'.
Re^2: return primary key if duplicate entry exists?
by diyaz (Beadle) on Jan 24, 2016 at 15:44 UTC
    I see. I just assumed if SQL is already looking for a duplicate then maybe it can also just give me the id/key of the row. Seems inefficient to have that as separate steps. Since they have last_insert_id, I assumed they would have a function similar.
      actually couldn't I allow it to replace the duplicate with the duplicate and that way last_insert_id will return that row id for me?

        Here's a working example. It does return the id either of the new record or the existing record. Unfortunately it also appears to change the auto-increment value with every update (even when no new records are inserted) so id's may not be continuous.

        From the mysql docs :  In general, you should try to avoid using an ON DUPLICATE KEY UPDATE clause on tables with multiple unique indexes.

        #!\C:\Strawberry\perl\bin\perl use strict; use warnings; use DBI; # CREATE TABLE test2 ( # id int(11) NOT NULL AUTO_INCREMENT, # f1 varchar(45) DEFAULT NULL, # PRIMARY KEY (id), # UNIQUE KEY f1_UNIQUE (f1) # ) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8; my $table = 'test2'; my $col = 'f1'; # unique key my $entry = $ARGV[0] || 1; #PERL DBI CONNECT my $dbh = dbh(); my $sql = sprintf ' INSERT INTO %s (%s) VALUES (?) ON DUPLICATE KEY UPDATE id = LAST_INSERT_ID(id)', $dbh->quote_identifier($table), $dbh->quote_identifier($col) ; my $sth = $dbh->prepare($sql); my $rv = $sth->execute($entry); print $rv.' '.$dbh->last_insert_id(undef,undef,$table,$col); sub dbh{ my $database = "test"; my $user = "user"; my $pw = "password"; my $dsn = "dbi:mysql:$database:localhost:3306"; my $dbh = DBI->connect($dsn, $user, $pw, { RaiseError=>1, AutoCommit=>1 } ); return $dbh; }
        poj
        Actually, if you perform an INSERT which results in a duplicate key error, then by definition you must know the key already. How else could you have inserted that record otherwise?

        The last_insert_id is only useful with auto-incrementing keys (and even then I find it rarely needed), but in any case auto-incrementing keys should never give a duplicate key error.

        CountZero

        A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James

        My blog: Imperial Deltronics

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: note [id://1153501]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others admiring the Monastery: (4)
As of 2024-03-28 17:15 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found