• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 706
  • Last Modified:

How can I run update query from perl to update existing fields in column?

I am facing problem while trying to execute update query from perl which also  insert new row with same data instead of updating only existing fields

my $diff=$dbh->prepare("UPDATE data SET diff=(time2 - time1) where username='testuser'");
$diff-> execute();

I want this script to update only existing data and not to insert new rows
0
saini_er
Asked:
saini_er
  • 4
  • 4
  • 3
  • +1
2 Solutions
 
K VDatabase ConsultantCommented:
this is only update query... being executed..
If u want to stop insert query, comment out the execution of insert query by adding a hash in front of that.

eg.
#my $ins = $dbh->prepare("SOME INSERT QUERY");
#$ins->execute();
0
 
UmeshMySQL Principle Technical Support EngineerCommented:
Try this..
$stmt = "UPDATE data SET diff=(time2 - time1) where username='testuser'";
 
# Prepare and execute the SQL query
$sth = $$dbh->prepare($$stmt) 
    || die "prepare: $$stmt: $DBI::errstr";
$sth->execute || die "execute: $$stmt: $DBI::errstr";

Open in new window

0
 
saini_erAuthor Commented:
I dont have any insert query in my code

it is just this query when executed in sql query browser just updates the field and incase executed from perl also insert the field
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
Adam314Commented:
Do you mean it inserts a row (record) or field?  If diff is NULL, and you want to keep it NULL, use this sql:
my $diff=$dbh->prepare("UPDATE data SET diff=(time2 - time1) where username='testuser' AND diff IS NOT NULL");
0
 
saini_erAuthor Commented:
Is there any query in sql which update all the columns in case  given user is already existing in database

for ex...if I already have 'testuser' in database and If I try to 'insert' same user again , it should update the existing user corresponding columns

I think it can be done using duplicate key but not sure
0
 
K VDatabase ConsultantCommented:
0
 
UmeshMySQL Principle Technical Support EngineerCommented:
>>Is there any query in sql which update all the columns in case  given user is already existing in database

>>for ex...if I already have 'testuser' in database and If I try to 'insert' same user again , it should update the existing user corresponding columns

You can try REPLACE statement

http://dev.mysql.com/doc/refman/5.0/en/replace.html
0
 
UmeshMySQL Principle Technical Support EngineerCommented:
Oops.. not refreshed..
0
 
K VDatabase ConsultantCommented:
saini_er, Your initial question is still confusing, what is getting inserted !!
0
 
saini_erAuthor Commented:
ghost_k8
Sorry for confusion.....my question is , I want to write a insert query(pls note it is little different from orignal question) which can check if the guven username already exist in database or not and incase it exists, it should just update  the corresponding field for that username for given values otherwsie should insert new row with that username and corresponding values
0
 
K VDatabase ConsultantCommented:
In simple ways you can go with a perl script only....

      my $chkSql = "select count(*) from table where username=<username's value>;";
      my $sth_chk=$dbh->prepare($chkSql);
      $sth_chk->execute();
      my $count = $sth_chk->fetchrow_array();
      print ">>".$count;
      if ($count == 0)
      {
            my $strInsert="INSERT QUERY GOES HERE";
            my $sth_Insert=$dbh->prepare($strInsert);
      }
       else
       {
          // <UPDATE QUERY EXECUTION PART GOES HERE>
       }
0
 
Adam314Commented:
In MySQL you can use REPLACE (instead of UPDATE or INSERT).  Here is some perl code to demonstrate this:
my $sth_InsertOrUpdate=$dbh->prepare('REPLACE INTO data (username, diff) VALUES (?, ?)');
 
#Set $username and $diff however you want
$username = 'testuser';
$diff = 123;
 
#This will insert of $username does not exist, or update if $username does exist
$sth_InsertOrUpdate->execute($username, $diff);

Open in new window

0
 
saini_erAuthor Commented:
I am using this query
 and somehow it is inserting new row instead of updating existing one
my $user=$dbh->prepare('REPLACE INTO data(username,password) VALUES (?, ?)');
$user -> execute($username,$pass);
0
 
Adam314Commented:
That means that $username for username and $pass for password are not a duplicate record.  Is username set as the primary key for the table?  If not, that would be the problem.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now