Bookmarks

Yahoo Gmail Google Facebook Delicious Twitter Reddit Stumpleupon Myspace Digg

Search queries

sqldatasource dal, wwwxxxenden, convert raid5 to raid 10 mdadm, apache force chunked, nrao wwwxxx, xxxxxdup, procmail change subject header, wwwXxx not20, Wwwxxx.doks sas, linux raid resync after reboot

Links

XODOX
Impressum

#1: Insert or update data in mysql using a perl script

Posted on 2011-03-04 18:36:32 by francesca casalino

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

Report this message