Insert or update data in mysql using a perl script

--0016e6d99b9c3e7c07049dab9676
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=
d
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



-----Table_1-----------

LOCATION_ID (AUTO-INCREMENT) LOCATION
SITE NAME



1
1234 4
syd

2
1235 4
brux

3
1236 5 temp_1



-----Table_2-----------

ASSIGN_ID (AUTO-INCREMENT) LOCATION_ID (FOREIGN KEY)
ASSIGN





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 =
1
and need to be entered into Table 2 with the LOCATION_ID that corresponds t=
o
them (either recognizing them with the same =93Table1.NAME=94 if there is o=
ne
(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,
SITE)).




Now, in the perl script I am looping through my data file and creating thes=
e
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=
n
my new data?)

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



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=
d
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_$=
id"



*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!

--0016e6d99b9c3e7c07049dab9676--