Thread: the column in Update
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
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 -- Martijn van Oosterhout <kleptog@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.
Attachment
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
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/
John Fabiani <jfabiani@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