From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "hjenkins" <hjenkins(at)uvic(dot)ca> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: combining semi-duplicate rows |
Date: | 2007-12-18 21:28:51 |
Message-ID: | 5209.1198013331@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
"hjenkins" <hjenkins(at)uvic(dot)ca> writes:
> So I tried:
> UPDATE schema1.datatable SET schema1.datatable.field1 =
> schema2.datatable.field1 FROM schema2.datatable
> WHERE schema2.datatable.keyfield = schema1.datatable.keyfield
> AND schema1.datatable.field1 = None;
> Which is suboptimal because I'd need a command for each field, but it
> would be a start. However, the schema names are not recognised. I get
> error messages to the effect that "cross-database references are not
> implemented" or "relation "schema1/2" does not exist.
The target column of a SET clause can't be qualified with the relation
name; it would introduce ambiguity in the case of composite-type fields,
and it's useless anyway since the target relation was already given.
Your example works for me (syntactically at least) as
regression=# UPDATE schema1.datatable SET field1 =
schema2.datatable.field1 FROM schema2.datatable
WHERE schema2.datatable.keyfield = schema1.datatable.keyfield
AND schema1.datatable.field1 = 'None';
UPDATE 0
Personally, though, I'd use some aliases to improve readability and
forestall the onset of carpal tunnel syndrome:
regression=# UPDATE schema1.datatable t SET field1 =
s.field1 FROM schema2.datatable s
WHERE s.keyfield = t.keyfield
AND t.field1 = 'None';
UPDATE 0
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Matt Magoffin | 2007-12-18 21:35:46 | Way to avoid expensive Recheck Cond in index lookup? |
Previous Message | Merlin Moncure | 2007-12-18 20:37:36 | Re: logging arguments to prepared statements? |