Insert or update data in mysql using a perl script

Insert or update data in mysql using a perl script

am 04.03.2011 18:36:32 von francesca casalino

Content-Type: text/plain; charset=windows-1252
Content-Transfer-Encoding: quoted-printable

Hi perl and mysql experts,

I am lost again into a problem that I do not know how to approach, and was
wondering whether you could direct me to the right path once more, telling
me if I am using the correct approach or I am totally off-path=85 I can fin=
only some solutions in the help pages but I am still confused about my
particular case=85

The database on mysql has various tables where each one is related to the
other through foreign keys, and



1234 4

1235 4

1236 5 temp_1



The data is in a file that contains information on the assignments (Table 2=
for certain locations (Table 1), but some are new locations and need to be
newly inserted into Table 1, while others already have an entry into Table =
and need to be entered into Table 2 with the LOCATION_ID that corresponds t=
them (either recognizing them with the same =93Table1.NAME=94 if there is o=
(but some new entries do not have a NAME) or with the same LOCATION AND
SITE). For this reason in mysql I created a UNIQUE KEY(LOCATION,

Now, in the perl script I am looping through my data file and creating thes=
variables for the columns in Table 1 and Table 2:

my $site =3D shift(@fields);

my $location =3D shift(@fields);

my $name =3D shift(@fields);

my $assign =3D shift(@fields);

I was thinking of trying these steps:

*1.* Create a query to look whether there is already an entry in the
existing database (but can I do this by checking whether the combination of
$location AND $site (my unique key) are the same as this same combination i=
my new data?)

$query =3D "SELECT location_id FROM table_1 where location=3D\'$location\' =

Maybe I need to use fetchrow_array.

*2.* Loop through the results of this query, and

*3.* If there is already an entry, then use the location_id and name entere=
in Table 1 (and if the name is not entered already in the database, then
enter this name) --- In this step I do not need to do anything in table_1,
but I need to select this location_id (and name if there is one), maybe
using a =93selectrow_array=94?

*4.* If there is not an entry, then insert the new data into Table_1 using
the =93name=94 if there is one, and if there is no name entering the new da=
ta in
Table_1 using a temporary name such as =93temp_1=94.

I think this step would be something like this:

$dbh->do(q{INSERT INTO table_1 (location, site, name) VALUES (?=
?, ?)}, undef, $location, $site, $name);

# get the primary key value of the inserted record of Table 1:

my $location_id =3D $dbh->selectrow_array (qq (select
last_insert_id() ));

And if there is no name, then maybe do:

update location where location_id =3D last_insert location_name =3D "temp_$=

*5.* Then enter the new data for Table2, using either the newly entered
location_id or the location_id that was fetched in step *3*=85

$dbh->do (qq (insert into table_2 (location_id, assign) values
(?,?) ), undef, $location_id, $assign);

I hope this wasn=92t too confusing...I think I am totally confused! If you
have any suggestions could you please advise?

Thank you very much again!