Re: UPDATE across tables

On Wed, 2002-08-07 at 12:01, Jake Matchett wrote:
> I need to update the primary key field in two tables sharing this field,
> I can't update separately because I get an integrity violation message
> that tells me the field is still being referenced from the other table,
> fair enough but is there any way of doing a joined/linked/double update
> to the primary key of both tables simultaneously? Something like: UPDATE
> table1.fieldx, table2.fieldx SET fieldx='yyyyy' WHERE fieldx='zzzzz';

The foreign key reference must be deferrable. Like this:

CREATE TABLE b (id CHAR(1) PRIMARY KEY REFERENCES a DEFERRABLE, ...);

For the update, do both in one transaction, with deferred constraints:
SET CONSTRAINTS ALL DEFERRED;
BEGIN;
UPDATE table1 ...
UPDATE table2...
END;

--
Oliver Elphick Oliver(dot)Elphick(at)lfix(dot)co(dot)uk
Isle of Wight, UK
http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"If ye be reproached for the name of Christ, happy are
ye; for the spirit of glory and of God resteth upon
you; on their part He is spoken evil of, but on your
part He is glorified." I Peter 4:14