By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,933 Members | 1,675 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,933 IT Pros & Developers. It's quick & easy.

the column in Update

P: n/a
From the 7.4 docs:
A column can be referenced in the form

correlation.columnname

correlation is the name of a table (possibly qualified with a schema name),or
an alias for a table defined by means of a FROM clause, or one of the key
words NEW or OLD. (NEW and OLD can only appear in rewrite rules, while other
correlation names can be used in any SQL statement.) The correlation name and
separating dot may be omitted if the column name is unique across all the
tables being used in the current query. (See also Chapter 7.)

So then why does this not work:
Update tablename set tablename.columnName = 'somedata' where .....

John

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Probably because the bit after the SET is a "column-name" not a
reference to a column. There's no point qualifying it in any way since
the tablename is given as part of the UPDATE statement.

On Tue, Nov 09, 2004 at 06:33:55PM -0800, John Fabiani wrote:
From the 7.4 docs:
A column can be referenced in the form

correlation.columnname

correlation is the name of a table (possibly qualified with a schema name), or
an alias for a table defined by means of a FROM clause, or one of the key
words NEW or OLD. (NEW and OLD can only appear in rewrite rules, while other
correlation names can be used in any SQL statement.) The correlation nameand
separating dot may be omitted if the column name is unique across all the
tables being used in the current query. (See also Chapter 7.)

So then why does this not work:
Update tablename set tablename.columnName = 'somedata' where .....

John

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
--
Martijn van Oosterhout <kl*****@svana.org> http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
tool for doing 5% of the work and then sitting around waiting for someone
else to do the other 95% so you can sue them.


-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFBkfwlY5Twig3Ge+YRAnifAKCrgwGs9XoyL3dR4FAePG WAONK21QCgtT1x
s5bhBhcX3bDzP35VXFRljOI=
=hd0W
-----END PGP SIGNATURE-----

Nov 23 '05 #2

P: n/a
Thanks but in the doc's it uses the term 'columnname'. The real issue is the
tablename.columnname is supported in MySQL and I'm trying to support Postgres
and MySQL with a single code routine.
John
On Wednesday 10 November 2004 03:31, Martijn van Oosterhout wrote:
Probably because the bit after the SET is a "column-name" not a
reference to a column. There's no point qualifying it in any way since
the tablename is given as part of the UPDATE statement.

On Tue, Nov 09, 2004 at 06:33:55PM -0800, John Fabiani wrote:
From the 7.4 docs:
A column can be referenced in the form

correlation.columnname

correlation is the name of a table (possibly qualified with a schema
name), or an alias for a table defined by means of a FROM clause, or one
of the key words NEW or OLD. (NEW and OLD can only appear in rewrite
rules, while other correlation names can be used in any SQL statement.)
The correlation name and separating dot may be omitted if the column name
is unique across all the tables being used in the current query. (See
also Chapter 7.)

So then why does this not work:
Update tablename set tablename.columnName = 'somedata' where .....

John

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 23 '05 #3

P: n/a
John Fabiani wrote:
Thanks but in the doc's it uses the term 'columnname'. The real
issue is the tablename.columnname is supported in MySQL and I'm
trying to support Postgres and MySQL with a single code routine.


Surely MySQL would also support writing a column name without a table
name?

--
Peter Eisentraut
http://developer.postgresql.org/~petere/
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 23 '05 #4

P: n/a
John Fabiani <jf******@yolo.com> writes:
Thanks but in the doc's it uses the term 'columnname'. The real issue is the
tablename.columnname is supported in MySQL and I'm trying to support Postgres
and MySQL with a single code routine.


If you want portable code, I suggest conforming to the SQL-standard
syntax. The table name is disallowed there according to the spec.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 23 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.