Re: UPDATE table via ODBC fails.

UPDATE:

This must have something to do with the multiple table involvement.

I modified my 'insert' command to include the updated field on the
initial insert.

INSERT INTO tmp_rxpurge (rxnum,dt_wrt,dt_lastfill,b_purge)
SELECT "RXNUM","DATE",rf.dt_lastfill,'f'
FROM rx
LEFT JOIN (SELECT "RXNUM" AS rxnum, Max("DATE") as dt_lastfill
FROM transactions GROUP BY "RXNUM")
as rf on rf.rxnum=rx."RXNUM"

Then - created three additional update steps ... they all worked via the
ODBC connector...

1. UPDATE tmp_rxpurge SET dt_lastact=dt_wrt
2. UPDATE tmp_rxpurge SET dt_lastact=dt_lastfill where dt_lastfill IS
NOT NULL and dt_lastfill > dt_lastact
3. UPDATE tmp_rxpurge SET b_purge='t' where dt_lastact < '2010-01-01'

I'm still puzzled why the first method didn't work.

Ken

On 4/11/2012 3:01 PM, Ken Benson wrote:
> I have a fairly straightforward routine which uses an ODBC connection
> to do 4 steps:
>
> * DROP TABLE IF EXISTS tmp_rxpurge
> * CREATE TABLE tmp_rxpurge (rxnum VARCHAR(300),dt_wrt
> DATE,dt_lastfill DATE,dt_lastact DATE, b_purge BOOLEAN)
> * INSERT INTO tmp_rxpurge (rxnum,dt_wrt,b_purge) SELECT
> "RXNUM","DATE",'f' FROM rx
> * UPDATE tmp_rxpurge as t SET dt_lastfill = rf.dt_lastfill FROM
> (SELECT "RXNUM" AS rxnum, Max("DATE") as dt_lastfill FROM
> transactions GROUP BY "RXNUM") as rf WHERE rf.rxnum=t.rxnum
>
>
> The first three commands are executed perfectly as expected.
> The update fails to do anything.
> There is no error generated.
> I have enabled 'logging' in the ODBC connector - and I don't see
> anything out of the ordinary in the short-log. (The long-log - I have
> no idea what all that stuff means.)
>
> If I copy that last command into a psql window - it executes - and
> works fine.
>
> But - it will not work via the ODBC connector.
>
> What's going on?
>
> Ken