"hjenkins" <hjenkins@uvic.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