Skip site navigation (1) Skip section navigation (2)

Re: UPDATE across tables

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 (view raw or flat)
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 


In response to

pgsql-novice by date

Next:From: Chad ThompsonDate: 2002-08-07 15:31:37
Subject: Database Structure
Previous:From: Jake MatchettDate: 2002-08-07 11:09:14
Subject: UPDATE across tables

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group