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]
