From: | Oliver Elphick <olly(at)lfix(dot)co(dot)uk> |
---|---|
To: | Jake Matchett <Jake(dot)Matchett(at)nottingham(dot)ac(dot)uk> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: UPDATE across tables |
Date: | 2002-08-07 15:31:11 |
Message-ID: | 1028734272.31338.41.camel@linda |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Chad Thompson | 2002-08-07 15:31:37 | Database Structure |
Previous Message | Jake Matchett | 2002-08-07 11:09:14 | UPDATE across tables |