Postgres/SQL Join and Update

Question

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

Ihor Romanchenko · Accepted Answer · 2013-01-14 15:29:42Z

2

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).

edited Jan 14, 2013 at 15:29

Ihor Romanchenko

29k9 gold badges56 silver badges45 bronze badges

answered Jan 14, 2013 at 13:31

user330315

Sign up to request clarification or add additional context in comments.

3 Comments

Erwin Brandstetter Over a year ago

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.

user330315 Over a year ago

@ErwinBrandstetter: are you sure the IN variant would be faster? Postgres is not bad at optimizing those things.

Erwin Brandstetter Over a year ago

IN is slower, not faster. While IN is generally the slowest option, the redundant JOIN is the more important factor here.

Stefan Rasmusson · Accepted Answer · 2013-01-14 13:28:37Z

0

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')

answered Jan 14, 2013 at 13:28

Stefan Rasmusson

5,6133 gold badges25 silver badges48 bronze badges

Collectives™ on Stack Overflow

Postgres/SQL Join and Update

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related