-> http://www.postgresql.org/docs/current/static/sql-update.html
2009/4/24 Filip Rembiałkowski <plk.zuber@xxxxxxxxx>
2009/4/24 josep porres <jmporres@xxxxxxxxx>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 = ""
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.rembialkowski@xxxxxxxxx
http://filip.rembialkowski.net/
