How do I update records only if I find a duplicate or otherwise insert data?

Question

In the code below there is a hash which contains records with fields like name, pid, type and time1. pid and name are repetitive fields which contain duplicates.

I duplicate found update the fields which need modification else insert, here name and pid have duplicates (repetitive fields).

The rest are unique. Also I have a unique field while creating the table Serial no. How should I go on? I have done only an insertion in this code. I dont know how to store the retrieved record into an array using Perl. Please guide me.

for my $test11 (sort keys %seen) {
    my $test1 = $seen{$test11}{'name'};
    my $test2 = $seen{$test11}{'pid'};
    my $test3 = $seen{$test11}{'type'};
    my $test4 = $seen{$test11}{'time1'};

    print "$test11\t$test1$test2$test3$test4\n";

    $db_handle = &getdb_handle;
    $sth       = $dbh->prepare("Select username,pid,sno from svn_log1");
    $sth->execute() or die "SQL Error: $DBI::errstr\n";
    my $ref = $sth->fetchall_arrayref();
    print "hai";
    print "****$ref";
    $sth = $dbh->prepare("INSERT INTO svn_log1 values('$sno','$test11','$test1','$test4','$test2','$test3')");
    $sth->execute() or die "SQL Error: $DBI::errstr\n";
}

Answer 1

I think what you're trying to say is that you don't want to try to insert some data if you already have that name/pid combination in the database, but I can't tell, so I can't help you out there.

However, here are a few things which can clear up your code. First, choose sensible variable names. Second, always, always, always use placeholders in your SQL statements to protect them:

for my $test11 ( sort keys %seen ) {
    my $name  = $seen{$test11}{'name'};
    my $pid   = $seen{$test11}{'pid'};
    my $type  = $seen{$test11}{'type'};
    my $time1 = $seen{$test11}{'time1'};

    my $dbh = getdb_handle();
    my $sth = $dbh->prepare("Select username,pid,sno from svn_log1");
    $sth->execute() or die "SQL Error: $DBI::errstr\n";
    my $ref = $sth->fetchall_arrayref();
    # XXX why are we fetching this data and throwing it away?

    $sth = $dbh->prepare("INSERT INTO svn_log1 values(?,?,?,?,?,?)");
    $sth->execute( $sno, $test11, $name, $time1, $pid, $type )
      or die "SQL Error: $DBI::errstr\n";
}

Assuming that you want to not insert something into the database if "$name" and "$pid" are there (and some cleanup to avoid preparing the same SQL over and over):

my $dbh = getdb_handle();
my $seen_sth   = $dbh->prepare( "Select 1 from svn_log1 where username = ? and pid = ?");

# This really needs to be "INSERT INTO svnlog1 (@columns) VALUES (@placeholders)
my $insert_sth = $dbh->prepare("INSERT INTO svn_log1 values(?,?,?,?,?,?)");
for my $test11 ( sort keys %seen ) {
    my $name  = $seen{$test11}{'name'};
    my $pid   = $seen{$test11}{'pid'};
    my $type  = $seen{$test11}{'type'};
    my $time1 = $seen{$test11}{'time1'};

    $seen_sth->execute($name, $pid) or die "SQL Error: $DBI::errstr\n";
    my @seen = $seen_sth->fetchrow_array;
    next if $seen[0];

    $insert_sth->execute( $sno, $test11, $name, $time1, $pid, $type )
      or die "SQL Error: $DBI::errstr\n";
}

That's not quite the way I would write this, but it's fairly clear. I suspect it's not really exactly what you want, but I hope it gets you closer to a solution.

Answer 2

What database is this?

My feeling is that you want an UPDATE or INSERT query, more commonly known as an UPSERT query.

If this is PostgreSQL you can create an upsert function to handle what you need. See the comments for a decent example. Otherwise, search Stack Overflow for "upsert" and you should find what you need.

Answer 3

You want to insert some data, but if it exists, then update the existing row?

How do you test that the data already exists in the database? Are you using username and pid?

If so, you may like the change the structure of your database: ALTER TABLE svn_log1 ADD UNIQUE (username, pid);

This create a composite, and unique index on username and pid. This means that every username/pid combination must be unique.

This allows you to do the following:

INSERT INTO svn_log1 (username, pid, ...) VALUES (?, ?, ...) ON DUPLICATE KEY UPDATE time = NOW();