Re: update error

2009/4/24 josep porres <jmporres(at)gmail(dot)com>

> Hello,
>
> I am migrating a database to pg and modifying the app.
> In that process I found an error and i don't see the reason.
>
> It's happening when executing the next update sentence
> Do you see what's wrong?

table name / alias is not allowed in SET section.

http://www.postgresql.org/docs/current/static/sql-update.html

> Do not include the table's name in the specification of a target column —
for example, UPDATE tab SET tab.col = 1 is invalid.

>
>
> The server is pg8.3.7 for w32
> ________________________________________________________________
>
> update talls as t
> set t.estat=5, t.d_pag=now(), t.ts_update=now()
> where
> t.estat in (0,1) and t.data = '02/04/2009'
> and t.clau not in ( select p.clau from pendents p
> where (t.data - p.data) >= 90 )
> ________________________________________________________
> ERROR: column "t" of relation "talls" does not exist
> LINE 3: set t.estat=5, t.d_pag=now(), t.ts_update=now()
> ^
>
> ********** Error **********
>
> ERROR: column "t" of relation "talls" does not exist
> estat SQL: 42703
> Caràcter: 25
> _________________________________________________________
>
> the table definitions are these:
>
> CREATE TABLE TALLS (
> DATA DATE NOT NULL,
> CLAU VARCHAR(12) NOT NULL,
> CLAU_ABO VARCHAR(12),
> LLIBRE INTEGER,
> FULLA NUMERIC(10) DEFAULT 0,
> NOM VARCHAR(40),
> ADRECA VARCHAR(40),
> NUMERCOMPT VARCHAR(15),
> LECACT NUMERIC(10) DEFAULT 0,
> LECTURA NUMERIC(10) DEFAULT 0,
> REFORMA NUMERIC(1) DEFAULT 0,
> MOTIU NUMERIC(1) DEFAULT 0,
> NOTES VARCHAR(100),
> D_TALL DATE,
> D_PAG DATE,
> D_RECON DATE,
> TS_UPDATE TIMESTAMP,
> ESTAT NUMERIC(2) DEFAULT 0,
> CPARAT NUMERIC(1) DEFAULT 0,
> NOUCOMPT VARCHAR(15),
> REINCID NUMERIC(1) DEFAULT 0,
> CPOSTAL NUMERIC(5),
> CONSTRAINT PK_TALLS PRIMARY KEY (DATA, CLAU));
>
> CREATE TABLE PENDENTS (
> CLAU VARCHAR(12),
> DATA DATE,
> TITULAR VARCHAR(40),
> BANC VARCHAR(4),
> SUCURSAL VARCHAR(4),
> COMPTE VARCHAR(10),
> IMPORT NUMERIC(10,2),
> L1 VARCHAR(40) DEFAULT '',
> L2 VARCHAR(40) DEFAULT '',
> L3 VARCHAR(40) DEFAULT '',
> L4 VARCHAR(40) DEFAULT '',
> L5 VARCHAR(40) DEFAULT '',
> L6 VARCHAR(40) DEFAULT '',
> L7 VARCHAR(40) DEFAULT '',
> L8 VARCHAR(40) DEFAULT '',
> L9 VARCHAR(40) DEFAULT '',
> L10 VARCHAR(40) DEFAULT '',
> L11 VARCHAR(40) DEFAULT '',
> L12 VARCHAR(40) DEFAULT '',
> L13 VARCHAR(40) DEFAULT '',
> L14 VARCHAR(40) DEFAULT '',
> L15 VARCHAR(40) DEFAULT '',
> L16 VARCHAR(40) DEFAULT '',
> NOM VARCHAR(40),
> AIGUA NUMERIC(10,2) DEFAULT 0,
> QS NUMERIC(10,2) DEFAULT 0,
> LLOGUER NUMERIC(10,2) DEFAULT 0,
> CANON NUMERIC(10,2) DEFAULT 0,
> MATERIALS NUMERIC(10,2) DEFAULT 0,
> IVA7 NUMERIC(10,2) DEFAULT 0,
> DESPESES NUMERIC(10,2) DEFAULT 0,
> FIANCA NUMERIC(10,2) DEFAULT 0,
> DRET NUMERIC(10,2) DEFAULT 0,
> DATACOBRAMENT DATE,
> CLAVEGUERAM NUMERIC(10,2) DEFAULT 0,
> QSCLAVEGUERAM NUMERIC(10,2) DEFAULT 0,
> DOCUMENT VARCHAR(10),
> DEVOLUCIO NUMERIC(1) DEFAULT 0,
> ESTAT VARCHAR(1),
> CARTA NUMERIC(1) DEFAULT 0,
> ESPECIAL NUMERIC(1) DEFAULT 0,
> CONSTRAINT PK_PENDENTS PRIMARY KEY (DOCUMENT));
>
>

--
Filip Rembiałkowski
JID,mailto:filip(dot)rembialkowski(at)gmail(dot)com
http://filip.rembialkowski.net/