Skip site navigation (1) Skip section navigation (2)

BUG - possible UPDATE bug in multi-record?

From: Keith Gray <keith(at)heart(dot)com(dot)au>
To: ODBC <pgsql-odbc(at)postgresql(dot)org>
Subject: BUG - possible UPDATE bug in multi-record?
Date: 2001-10-18 00:57:29
Message-ID: 3BCE28F9.7AFBBF39@heart.com.au (view raw or flat)
Thread:
Lists: pgsql-odbc
Using this style of command through an SQL tool it 
decrements each row in the list by 1 cent.

UPDATE GeneralLedger 
SET CBalPer0 = CBalPer0 + (-0.01), 
PBalPer0 = PBalPer0 + (-0.01) 
WHERE AccountSequence IN (56,54,3,2)

(I only use the brackets to handle sign of amount
as in next example)


Trying to execute it through ODBC...

	Amount = -0.01
	AccountSequence = "(56,54,3,2)"
	SQL= "UPDATE GeneralLedger " & _
	     "SET CBalPer0 = CBalPer0 + (" & Amount & "), " & _
	     "PBalPer0 = PBalPer0 + (" & Amount & ") " & _
	     "WHERE AccountSequence IN " & AccountSequenceList

	myConnection.Execute(SQL)

...causes movements in other accounts and incorrect balances!

I am trying with 2 separate UPDATE statements...

	SQL= "UPDATE GeneralLedger " & _
	     "SET CBalPer0 = CBalPer0 + (" & Amount & "), " & _
	     "WHERE AccountSequence IN " & AccountSequenceList

	myConnection.Execute(SQL)

	SQL= "UPDATE GeneralLedger " & _
	     "SET PBalPer0 = PBalPer0 + (" & Amount & ") " & _
	     "WHERE AccountSequence IN " & AccountSequenceList

	myConnection.Execute(SQL)

...and yes this does work.

-- 
Keith Gray

Technical Development Manager
Heart Consulting Services P/L
mailto:keith(at)heart(dot)com(dot)au

Responses

pgsql-odbc by date

Next:From: Fábio SatoDate: 2001-10-18 05:53:52
Subject: ODBC Performance
Previous:From: Hiroshi InoueDate: 2001-10-18 00:43:46
Subject: Re: error message: the driver doesn't support the version of

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group