Re: [GENERAL] How to efficiently update many records at once
От | Beth Strohmayer |
---|---|
Тема | Re: [GENERAL] How to efficiently update many records at once |
Дата | |
Msg-id | 4.2.0.58.19991118154606.00a52e00@pop обсуждение исходный текст |
Ответ на | How to efficiently update many records at once (Martin Weinberg <weinberg@osprey.astro.umass.edu>) |
Ответы |
Re: [GENERAL] How to efficiently update many records at once
|
Список | pgsql-general |
At 03:07 PM 11/18/1999 , Martin Weinberg wrote: >I have two tables with different information indexed by a unique key. >I want to update the contents of one table if an entry exists in >a second table. > >Some playing with explain suggests that the optimum strategy using >UPDATE is: > >update table1 set x=1 from table2 where key in > (select key from table2 where table1.key=table2.key); > >This *does work* but can double the size of the database (until >the next vacuum). Is there an efficient way to do this in situ? > >The problem is that my database is 100GB and only have 132GB >of space. Martin, You could try using a simple Join clause: update table1 set x=1 from table2 where table2.key = table1.key; or the Exists clause: update table1 set x=1 from table2 where exists (select * from table2 where table2.key = table1.key); (In this one I'm not sure if the from table2 is needed in the update section.) Have not had a chance to test these, sorry! Hope they help. Beth :-) _______________________________________________ / Beth L Strohmayer / Software Engineer _____) / ITT Industries, Systems Division (_____|______________________ / @ Naval Research Laboratory, Code 5542 | \ \ 4555 Overlook Ave. SW | Phone: (202) 404-3798 \ \ Washington, DC 20375 | Fax: (202) 404-7942 \ \_________________________________________| / | strohmayer@itd.nrl.navy.mil / |____________________________/
В списке pgsql-general по дате отправления: