Thread: prepared query plan did not update
Good morning, I tried to use prepared query plan to update columns, but it did not update at all. PREPARE pname(varchar) AS UPDATE t1 SET col1 = false WHERE col1 AND col2 = '$1' ; EXECUTE pname( 'value' ) Could someone tell me where I did wrong please? Thanks alot!
-----Original Message----- From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Emi Lu Sent: Wednesday, September 17, 2008 2:55 PM To: pgsql-sql@postgresql.org Subject: [SQL] prepared query plan did not update Good morning, I tried to use prepared query plan to update columns, but it did not update at all. PREPARE pname(varchar) AS UPDATE t1 SET col1 = false WHERE col1 AND col2 = '$1' ; EXECUTE pname( 'value' ) Could someone tell me where I did wrong please? Thanks alot! -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql WHERE col1 AND col2 = '$1' ; Are you looking for both: col1 and col2 - to be equal to '$1'? Then it should be: WHERE col1 = '$1' AND col2 = '$1';
> I tried to use prepared query plan to update columns, but it did not > update at all. > > PREPARE pname(varchar) AS > UPDATE t1 > SET col1 = false > WHERE col1 AND > col2 = '$1' ; > EXECUTE pname( 'value' ) > Could someone tell me where I did wrong please? > > > > > > WHERE col1 AND > col2 = '$1' ; > Are you looking for both: col1 and col2 - to be equal to '$1'? > Then it should be: > WHERE col1 = '$1' AND > col2 = '$1'; Not exactly, col1 is boolean value WHERE col1 = true AND col2 = '$1';
On Wed, 17 Sep 2008, Emi Lu wrote: > Good morning, > > I tried to use prepared query plan to update columns, but it did not > update at all. > > PREPARE pname(varchar) AS > UPDATE t1 > SET col1 = false > WHERE col1 AND > col2 = '$1' ; I don't think you want those quotes in the second part of the where clause. I'm pretty sure that means you're comparing against the literal string with a dollar sign and one rather than the value given at execute time for $1.
Stephan Szabo wrote: > On Wed, 17 Sep 2008, Emi Lu wrote: > >> Good morning, >> >> I tried to use prepared query plan to update columns, but it did not >> update at all. >> >> PREPARE pname(varchar) AS >> UPDATE t1 >> SET col1 = false >> WHERE col1 AND >> col2 = '$1' ; > > I don't think you want those quotes in the second part of the where > clause. I'm pretty sure that means you're comparing against the literal > string with a dollar sign and one rather than the value given at execute > time for $1. Do you mean: PREPARE pname(varchar) AS UPDATE t1 SET col1 = false WHERE col1 AND col2 = $1 ; But still does not work? Strange, right?
On Wed, 17 Sep 2008, Emi Lu wrote: > Stephan Szabo wrote: > > On Wed, 17 Sep 2008, Emi Lu wrote: > > > >> Good morning, > >> > >> I tried to use prepared query plan to update columns, but it did not > >> update at all. > >> > >> PREPARE pname(varchar) AS > >> UPDATE t1 > >> SET col1 = false > >> WHERE col1 AND > >> col2 = '$1' ; > > > > I don't think you want those quotes in the second part of the where > > clause. I'm pretty sure that means you're comparing against the literal > > string with a dollar sign and one rather than the value given at execute > > time for $1. > > Do you mean: > > PREPARE pname(varchar) AS > UPDATE t1 > SET col1 = false > WHERE col1 AND > col2 = $1 ; > > But still does not work? Strange, right? I think we'll need to see a complete example with table definitions and sample data because it does appear to work for me in simple tests.