1

This morning I asked this very similar question, and it was answered beautifully.

However, after reviewing the play, I see that my actual problem is slightly more complicated than what I described in that question. Basically, I have 3 Postgres tables:

[myschema].[animals]
--------------------
animal_id
animal_attrib_type_id (foreign key to [myschema].[animal_attrib_types])
animal_attrib_value_id (foreign key to [myschema].[animal_attrib_values])

[myschema].[animal_attrib_types]
--------------------------------
animal_attrib_type_id
animal_attrib_type_name

[myschema].[animal_attrib_values]
--------------------------------
animal_attrib_value_id
animal_attrib_value_name

So, I might have an animal record like so:

[myschema].[animals]
--------------------
animal_id = 458
animal_attrib_type_id = 38
animal_attrib_value_id = 23

And the corresponding animal_attrib_type (with id = 38) has the following values:

[myschema].[animal_attrib_types]
--------------------------------
animal_attrib_type_id = 38
animal_attrib_type_name = 'animals.should-make-noise'

And the corresponding animal_attrib_value (with id = 23) has the following values:

[myschema].[animal_attrib_values]
--------------------------------
animal_attrib_type_id = 23
animal_attrib_type_name = 'true'

So, the same animal record can have multiple type/value pairs. In this case the animal had an animal_attrib_type_name of "animals.should-make-noise" corresponding to an animal_attrib_value_name of "true".

At runtime, I will only have the animal_id (i.e, 458) and animal_attrib_type_id (i.e, 38). I need to be able to look up the appropriate animal_attrib_value_name corresponding to that given animal_id and animal_attrib_type_id only, and then update its value to some static text ('true' or 'false'); all from within the same UPDATE statement.

The answer in the above-referenced question was correct for the problem I stated, but since the same animal has 0+ type/value combos I actually need a slightly different SQL statement. Thanks in advance!

Community
  • 1
  • 1
IAmYourFaja
  • 55,468
  • 181
  • 466
  • 756
  • possible duplicate of [Postgres update with an inner join across 2 tables?](http://stackoverflow.com/questions/12600608/postgres-update-with-an-inner-join-across-2-tables) – Clodoaldo Neto Sep 26 '12 at 12:55
  • @Clodoaldo - please actually read my question! I reference that question in the first sentence above! I'm intentionally referencing it. Its also not a duplicate at all! Two different problems! – IAmYourFaja Sep 26 '12 at 13:01
  • Anybody who closes this as "an exact duplicate" is clearly not reading the very first sentence to this question! Nor looking at the list of linked questions to this one! – IAmYourFaja Sep 26 '12 at 13:02
  • @4herpsand7derpsago **change the title** or you'll get closed in a few seconds. So many people re-post near identical questions here that with 2 posts on the list by the same person with the same title it *will* get closed unless obviously different, ie diff. title; – Craig Ringer Sep 26 '12 at 13:05
  • @4herpsand7derpsago I did read both questions. In the first you made two versions of it. This one is the third version. Make your mind up and avoid creating a fourth version. – Clodoaldo Neto Sep 26 '12 at 13:10
  • If you know how to join two tables, you also know how to join 3 tables. –  Sep 26 '12 at 13:28
  • @Clodoaldo I have only asked two questions. Deciding to read old/obsolesced/edited versions of an earlier question, and then counting it as an entirely separate question sounds like a "you" problem. – IAmYourFaja Sep 26 '12 at 15:40
  • It is a problem to anyone, like me, who wrote an answer to the first version and saw it invalidated by the second version. If an answer to the first version does not fit the second version then they are indeed two questions. – Clodoaldo Neto Sep 26 '12 at 15:48

2 Answers2

7

Make use of the FROM clause in the PostgreSQL UPDATE command. This is usually cleaner and faster.

UPDATE animal_attrib_values av
SET    animal_attrib_value_name = 'true'
FROM   animals a 
WHERE  a.animal_id = 458 
AND    a.animal_attrib_type_id  = 38
AND    a.animal_attrib_value_id = av.animal_attrib_value_id;

Since we already know the animal_attrib_type_id we don't have to include the third table animal_attrib_types at all. We could join to it additionally if needed ...

Also, do not table-qualify SET items in an UPDATE. That's a syntax error. I quote the manual on said page:

Do not include the table's name in the specification of a target column — for example, UPDATE tab SET tab.col = 1 is invalid.

Bold emphasis mine.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
1

The below SQL should do what you are asking:

UPDATE animal_attrib_values aav
SET animal_attrib_value_name= 'true'
WHERE aav.animal_attrib_value_id = (
    SELECT a.animal_attrib_value_id 
    FROM animals a 
    WHERE a.animal_id = 458 
        AND a.animal_attrib_type_id  = 38
)
;
acatt
  • 487
  • 3
  • 10