Beefy Boxes and Bandwidth Generously Provided by pair Networks
Do you know where your variables are?

Update/insert record in database

by SamueD2 (Novice)
on Apr 11, 2003 at 18:16 UTC ( [id://249945] : perlquestion . print w/replies, xml ) Need Help??

SamueD2 has asked for the wisdom of the Perl Monks concerning the following question:

I wrote a script that reads a tab delimited file and assigns a Scalar to each field. It then inserts it into a table in my database. What I need to do is to take the value of one of the scalar ($Control) for each line in the file and check if it exists in my table. If it does then update the whole record with the new information from the file. If it does not then insert it into my table. Thanks

Title edit by tye

Replies are listed 'Best First'.
Re: Exists
by LameNerd (Hermit) on Apr 11, 2003 at 18:33 UTC
    Does the database you are using support stored procedures?
    If so you would probably want to do what you are talking about
    in a stored procedure that would be executed from your script. In PL/SQL:
    procedure ins_or_upd ( s varchar2 ... ) is begin update ... where column=s; if SQL%rowcount = 0 then insert ... end if; end;
      I have twenty five fields (scalars)that I am reading from the file per line . How do I pass the variables to the stored procedure in my perl script. Thanks
        That depends on the DBMS you are using. I know Oracle so I can only
        tell you how to go about this with PL/SQL.
        In PL/SQL your could have a stored function that could tell your script if a record exist or not so that your script could choose either to do a insert or an update. You could also just use a stored procedure like the one I mentioned earlier and just add your fields to the parameter list. You could also do everything from your script. Your script could do the update and if the row count is zero that means your script needs to do an insert.
Re: Exists
by graff (Chancellor) on Apr 12, 2003 at 03:04 UTC
    Not being an expert at PL/SQL myself -- and not knowing what sort of database you're talking about (what sort of database are you talking about? that might matter...) -- I'd consider two ways of handling this, depending on the size of the database:
    • if it's not very big (say, up to some thousands of rows), start by reading all the values of "$Control" that are currently present in the database, and storing these as keys of a hash -- e.g. if you "select" all values of that field into @Control, then  $hash{$_}++ for (@Control);
    • if it's a very big database, figure out a quick/easy query to look for a given value of $Control in your DB, and just do that for every input record you process.

    In either case, as you process your input file data, if the given value of $Control exists/is found, then execute an update, else execute an insert.

    If you need more details about how to do that, you should give more details about what you're using and what you've tried so far.

Re: Exists
by Mr. Muskrat (Canon) on Apr 11, 2003 at 18:21 UTC
    Sounds easy enough. What have you tried so far? What errors did you get? Can you help us help you?