update error

Started by josep porresalmost 17 years ago5 messagesgeneral

Jump to latest

josep porres

jmporres@gmail.com

almost 17 years ago

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?

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

plk.zuber@gmail.com

almost 17 years ago

In reply to: josep porres (#1)

Re: update error

2009/4/24 josep porres <jmporres@gmail.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.rembialkowski@gmail.com
http://filip.rembialkowski.net/

josep porres

jmporres@gmail.com

almost 17 years ago

In reply to: Filip Rembiałkowski (#2)

Re: update error

So, what's the point of the alias in the documentation?

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

2009/4/24 Filip Rembiałkowski <plk.zuber@gmail.com>

Show quoted text

2009/4/24 josep porres <jmporres@gmail.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.rembialkowski@gmail.com
http://filip.rembialkowski.net/

josep porres

jmporres@gmail.com

almost 17 years ago

In reply to: Filip Rembiałkowski (#2)

Re: update error

ok, it's working without the alias inside the SET

thanks

2009/4/24 Filip Rembiałkowski <plk.zuber@gmail.com>

Show quoted text

2009/4/24 josep porres <jmporres@gmail.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.rembialkowski@gmail.com
http://filip.rembialkowski.net/

Tom Lane

tgl@sss.pgh.pa.us

almost 17 years ago

In reply to: josep porres (#3)

Re: update error

josep porres <jmporres@gmail.com> writes:

So, what's the point of the alias in the documentation?

You might want the alias elsewhere in the command, in places where it's
actually possible to refer to more than one table.

regards, tom lane