up vote 1 down vote favorite

Joins are the one thing I could never get my head around, ie, which type to use and how they actually work. I need to write a query and I know I will need a join for it.

The database is postgres.

Basic table set up is like this (some details redacted)

rates (
rates_id Primary Key, Auto Increment
state String, can either be 'current' or 'history'
)

rates_records (
rates_records_id Primary key, auto increment
rates_id = integer
column_1,
column_2
)

Every rates_records entry has rates_id set to a value that exists in the rates table

I want to update every rates_records row and modify the column_1 and column_2 data where the associated rates state = 'current'

How would I do this? Thanks

share|improve this question
77% accept rate
feedback

2 Answers

up vote 2 down vote

An alternative to Stefan's answer.

update rates_records 
   set column_1 = 'foo', 
       column_2 = 'bar'
from rates 
  where rates.rates_id = rates_records.rates_id
    and rates.state = 'current';

More details and examples are in the manul: http://www.postgresql.org/docs/current/static/sql-update.html

Which format you prefer is basically a matter of taste. I don't believe one is better or faster than the other (Stefan's answer being standard SQL so it's portable across DBMS).

share|improve this answer
Actually, besides being shorter, this should be substantially faster than the IN variant, where you also have a redundant join operation. It's the way to go. – Erwin Brandstetter Jan 15 at 1:42
@ErwinBrandstetter: are you sure the IN variant would be faster? Postgres is not bad at optimizing those things. – a_horse_with_no_name Jan 15 at 7:39
IN is slower, not faster. While IN is generally the slowest option, the redundant JOIN is the more important factor here. – Erwin Brandstetter Jan 15 at 16:09
feedback
up vote 0 down vote 
update rates_records set column_1 = 'this', column_2 = 'that'
where rates_records_id 
in (select rates_records_id from rates_records rr 
join rates r on rr.rates_id = r.rates_id where state = 'current')
share|improve this answer
feedback

Your Answer

 
or
required, but never shown
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.