2

I am using pgadmin for PostgreSQL (9.1) and I have this query which takes too long to run

update tableA a
set owner1_surname = (select owner_surname from owners_distinct b where a.owner1= b.owner),
owner1_othername   = (select owner_othername from owners_distinct b where a.owner1= b.owner),
owner2_surname     = (select owner_surname from owners_distinct b where a.owner2= b.owner),
owner2_othername   = (select owner_othername from owners_distinct b where a.owner2= b.owner),
owner3_surname     = (select owner_surname from owners_distinct b where a.owner3= b.owner),
owner3_othername   = (select owner_othername from owners_distinct b where a.owner3= b.owner)

Instead of having to retrieve the values from owners_distinct table again and again, is it possible to retrieve the columns owner, owner_surname and owner_othername just once using SELECT and then do UPDATE on tableA's columns based on the check?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
CCGooner
  • 137
  • 2
  • 10

2 Answers2

1

This is trickier than I first thought, since you want to join the same table multiple times, and the only connection is the updated table itself:

UPDATE table_a a
    SET owner1_surname = b1.owner_surname
    ,owner1_othername  = b1.owner_othername
    ,owner2_surname    = b2.owner_surname
    ,owner2_othername  = b2.owner_othername
    ,owner3_surname    = b3.owner_surname
    ,owner3_othername  = b3.owner_othername
FROM   table_a x
LEFT   JOIN owners_distinct b1 ON b1.b.owner = x.owner1
LEFT   JOIN owners_distinct b2 ON b2.b.owner = x.owner2 
LEFT   JOIN owners_distinct b2 ON b3.b.owner = x.owner3
WHERE  x.table_a_id = a.table_a_id

Where table_a_id is the primary key of table_a. Normally you don't have to join the table another time, but in this situation you need it for the join before you can link to the updated table.

I use LEFT JOIN, in order to prevent the whole update for a row from failing if one of the three owners cannot be found in owners_distinct.

Database design

Are you sure you need all the redundant data in table_a? The canonical way in a normalized schema would be to only store the foreign keys (owner1, owner2, owner3), and fetch details of the name on demand with a JOIN in a SELECT. Remove all those columns you are updating altogether. Of course, there are always exceptions to the rule ...

No unique key?

This shouldn't happen to begin with. You should add a surrogate primary key like:

ALTER TABLE table_a ADD table_a_id serial PRIMARY KEY;

More about that in the related answer:
Do I need a primary key for my table, which has a UNIQUE (composite 4-columns), one of which can be NULL?

Solution without unique key

Anyway, here is a way to make this update regardless of any unique column:

UPDATE table_a a
    SET owner1_surname = b1.owner_surname
    ,owner1_othername  = b1.owner_othername
    ,owner2_surname    = b2.owner_surname
    ,owner2_othername  = b2.owner_othername
    ,owner3_surname    = b3.owner_surname
    ,owner3_othername  = b3.owner_othername
FROM   (SELECT DISTINCT owner1, owner2, owner3 FROM table_a) x
LEFT   JOIN owners_distinct b1 ON b1.b.owner = x.owner1
LEFT   JOIN owners_distinct b2 ON b2.b.owner = x.owner2 
LEFT   JOIN owners_distinct b2 ON b3.b.owner = x.owner3
WHERE  x.owner1 = a.owner1
AND    x.owner2 = a.owner2
AND    x.owner3 = a.owner3;

The point is: we only need each combination of (owner1, owner2, owner3) once.

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • 2
    @CCGooner: *Every* table should have a primary key. This one doesn't? Either way, any unique combination of columns can do the job. The whole row if need be. Just avoid columns that can be `NULL`. – Erwin Brandstetter Mar 26 '13 at 02:31
-1

SELECT owner, owner_surname and owner_othername FROM table_a

UPDATE table_a a

 SET owner1_surname = b1.owner_surname
,owner1_othername  = b1.owner_othername
,owner2_surname    = b2.owner_surname
,owner2_othername  = b2.owner_othername
,owner3_surname    = b3.owner_surname
,owner3_othername  = b3.owner_othername
StepUp
  • 36,391
  • 15
  • 88
  • 148
  • I want owner1_surname, owner1_othername to be updated only if table_a.owner1=owners_distinct.owner and same for owner2surname, owner2othername and owner3surname, owner3othername – CCGooner Mar 26 '13 at 02:38
  • before this code try to put in this condition: IF table_a.owner1=owners_distinct.owner and same for owner2surname, owner2othername and owner3surname, owner3othername – StepUp Mar 26 '13 at 02:49