Results 1 to 6 of 6
  1. #1
    Join Date
    May 2004
    Posts
    105

    Unanswered: Having major problems with my insert query logic

    I have a perl program that is looping through a hash of a hash. I need to Update any existing records but also insert any new records in the table using collected data in the hash.

    Life would be very simple if it was possible to use a Where Clause in an Insert statement but not does not work.

    Here is some example code from my program:
    sub Test{
    foreach my $table(keys %$HoH){
    foreach my $field(keys %{$HoH->{$table}}){
    if($table eq "CPU"){
    my $CPUstatement = "INSERT INTO CPU(CPUNumber, Name, MaxClockSpeed, SystemNetName)
    Values ('$field',
    '$HoH->{CPU}{$field}{Name}',
    '$HoH->{CPU}{$field}{MaxClockSpeed}' ,
    '$HoH->{Host}{SystemNetName}')";
    print "$CPUstatement\n";
    if ($db->Sql($CPUstatement))
    {
    print "Error on SQL Statement\n";
    Win32::ODBC:umpError();
    }
    else
    {
    print "successful\n";
    }
    }
    }


    }
    }

    Thanks,
    Laura

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm assuming that your hash values are printing as expected. The construction looks strange (but it could be fine) to me.

    Is the CPUNumber the primary key for the CPU table? If so, you could use something like:
    Code:
    my $CPUstatement = "IF EXISTS (SELECT * FROM CPU WHERE CPUNUMBER = '$field')
       THEN UPDATE CPU
          SET Name = '$HoH->{CPU}{$field}{Name}'
    ,         MaxClockSpeed = '$HoH->{CPU}{$field}{MaxClockSpeed}'
    ,         SystemNetName = '$HoH->{Host}{SystemNetName}'
       WHERE  CPUNumber = '$field'
       ELSE INSERT INTO CPU(CPUNumber, Name, MaxClockSpeed, SystemNetName)
          Values (
             '$field'
    ,        '$HoH->{CPU}{$field}{Name}'
    ,        '$HoH->{CPU}{$field}{MaxClockSpeed}'
    ,        '$HoH->{Host}{SystemNetName}')";
    -PatP

  3. #3
    Join Date
    May 2004
    Posts
    105
    I tried your code and I am getting an error -> Incorrect Systax near the keyword 'THEN'

    What do you suppose that means? I copied and pasted the code as is.

    Thanks,
    Laura

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    That error means that I don't proofread very well I was composing as I typed, and simply got ahead of myself then didn't clean up afterwards. Just remove the word THEN from that statement. Sorry.

    -PatP

  5. #5
    Join Date
    May 2004
    Posts
    105
    oh wow. That's so cool, it worked.

    Thanks Pat for your help. I learn new things everyday.

    -Laura

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I just love it when I can make a lovely lady happy!

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •