Mike, have you looked into Perl, and Perl's DBI module? You can do something like this... (example only, not tested code):
-- use DBI; use strict; use warnings; # Get arguements from command line my $ether_address = $ARGV[0]; my $location = $ARGV[1]; # Database connection info $database = "yourdatabase"; $hostname = "localhost"; $user = "youruser"; $password = "yourpassword"; # Connect to database $dsn = "DBI:mysql:database=$database;host=$hostname"; $dbh = DBI->connect($dsn, $user, $password) or die("$@"); # Prepare queries my $select_query = "SELECT COUNT(*) WHERE ethernet_address = $ether_address"; my $update_query = "UPDATE hardware_assets SET location=$location WHERE ethernet_address = '$ether_address'"; my $insert_query = "INSERT hardware_assets (ethernet_address, location) VALUES ($ether_address, $location)"; # Make statement handles my $select_sth = $dbh->prepare($select_query); my $update_sth = $dbh->prepare($update_query); my $insert_sth = $dbh->prepare($insert_query); # Query for rows $rv = $select_sth->execute || die("Could not execute $select_query: $!\n"); $count = $select_sth->fetchall_arrayref([0]); if ($count > 0) { my $update_sth->execute || die("Could not execute $update_query: $!\n"); } else { my $insert_sth->execute || die("Could not execute $insert_query: $!\n"); } -- Good luck, Joshua Thomas Network Operations Engineer PowerOne Media, Inc. tel: 518-687-6143 [EMAIL PROTECTED] --- Ninety percent of this game is half mental. - Yogi Berra --- > -----Original Message----- > From: Mike Tuller [mailto:[EMAIL PROTECTED] > Sent: Thursday, January 22, 2004 3:04 PM > To: MySql List > Subject: Replace to update records > > > I have a shell script that will insert information about > systems I have into > a MySql database. I wanted to have it so that the script > could run daily, > and just update the records if a record for the particular system was > already in the database. This could make the script complex > because I would > have to check to see if the record exists by matching the > ethernet address. > If it does, then it would update the record, if it doesn't, > it would add the > record. > > The problem I have is that I want to have an auto-increment > ID number that I > can have display on a web page, where you would click on the link that > displays the ID number of the computer to display details. > > If I use replace, when I update the record, it also updates > the ID, so what > was 4 is now 5. I don't want that to change. > > Here is what I currently have. > > /usr/local/mysql/bin/mysql --user=$username --password=$password > --host=$server cetechnology -e \ > "REPLACE INTO hardware_assets (ethernet_address, operating_system, > boot_volume, computer_type, number_of_cpus, cpu_type, total_memory, > bus_speed, \ > cpu_speed, L2_cache_size, serial_number, ip_address, > network_name, > script_version, date_processed, asset_tag_number, department, > location, > room_number) \ > VALUES \ > ('$ethernet_address', '$operating_system', '$boot_volume', > '$computer_type', '$number_of_cpus', '$cpu_type', '$total_memory', > '$bus_speed', \ > '$cpu_speed', '$L2_cache_size', '$serial_number', > '$ip_address', > '$network_name', '$script_version', '$date_processed', > '$asset_tag_number', > \ > '$department', '$location', '$room_number')"; > > > I am thinking it would be better to have the script search > for all records > that match a certain ethernet address. If a record exists, > then update the > record, and if one does not exist, then insert a record. > > I understand how to select, insert, and update individually, > but I am not > sure how to how to put it all together to do what I want this to do. > Something like this: > > SELECT * WHERE ethernet_address = $ethernet_address > (if the number of results does not = 0) > UPDATE hardware_assets SET location='my location' WHERE > ethernet_address='$ethernet_address > Else > INSERT hardware_assets (ethernet_address, location) VALUES > ($ethernet_address, $location) > > Could someone help me finish these statements or show me a > better way of > doing this? > > > Mike Tuller > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]