Beefy Boxes and Bandwidth Generously Provided by pair Networks
The stupid question is the question not asked
 
PerlMonks  

deleting using DBI from a PostgreSQL table

by indapa (Monk)
on Nov 08, 2003 at 00:28 UTC ( #305484=perlquestion: print w/replies, xml ) Need Help??

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

Hi, I am new to DBI and tried reading the docs on this, but I can't seem to figure out where my syntax error is. I want to remove a row from a PostgreSQL database based on a key I am parsing from an XML file. The snippet below.
# connect to mesh db my $dbh = DBI->connect( "dbi:Pg:dbname=foobar", "foo") || die "couldn't open db \n"; # prepare statement, $dtable is the table name I am passing in as a pa +rameter my $rm = $dbh->prepare("DELETE FROM $dtable (pubmed_ui) VALUES (?)"); # when I parse from xml file my $pmid = ($root->descendants('PMID'))[0]->text; print "PMID: $pmid\n"; $rm->execute($pmid);
I get the following error when I run it.
DBD::Pg::st execute failed: ERROR: parser: parse error at or near "(" + at character 20 at ../../bin/remove_disease.pl line 55. # line 55 is my execute statement but I think my syntax is wrong in my + prepare statement
Any ideas what I'm doing wrong? Thanks for the help.

Replies are listed 'Best First'.
Re: deleting using DBI from PostgresSQL table
by Roger (Parson) on Nov 08, 2003 at 00:30 UTC
    I see that your SQL is incorrect, you are using the syntax for INSERT to do DELETE. The SQL for DELETE should be:

    "DELETE FROM $dtable WHERE pubmed_ui = ?"


    Just a piece of information that Oracle has an excellent online SQL reference on the OTN (oracle tech network) site. You need to sign up as a member to access the reference, but the sign up is free (and you get two free CD's of the latest Oracle database release).

    I know you are not using Oracle, but its SQL reference is really good.


    Update: Crossed-out comment on the choice of online SQL reference.

      PostgreSQL has online documentaion at http://www.postgresql.org/docs/.
        Thanks for the reponses. I should have RTFM a little more closely because indeed my sql syntax was for an insert, not a delte.
        Ummm, I had a look at the documentation of postgresSQL you mentioned, the quality of the SQL documentation doesn't even come close to the Oracle SQL documentation though. I was merely giving a good doco site for SQL language. Postgres, Oracle or not. :-)

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://305484]
Approved by Roger
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others cooling their heels in the Monastery: (2)
As of 2019-12-08 16:55 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found

    Notices?