[SQL] multiple sql update w/ major time issues
I have a sql update statement that is dealing with hundreds of thousands of records. It takes hours and hours to complete (if it does complete and not take down the server). Here is how I have it right now: update aud_member_ext_attributes b set EXTVALUE217 = (select a.MTD from gl_totals a where a.category = 'tankrent' and a.CUST_NO = b.EXTVALUE101 and a.DIST_NO = b.EXTVALUE102 and a.SUB_NO = b.EXTVALUE105 and a.FUEL_TYPE = b.EXTVALUE123); update aud_member_ext_attributes b set EXTVALUE223 = (select a.YTD from gl_totals a where a.category = 'tankrent' and a.CUST_NO = b.EXTVALUE101 and a.DIST_NO = b.EXTVALUE102 and a.SUB_NO = b.EXTVALUE105 and a.FUEL_TYPE = b.EXTVALUE123); update aud_member_ext_attributes b set EXTVALUE229 = (select a.R12 from gl_totals a where a.category = 'tankrent' and a.CUST_NO = b.EXTVALUE101 and a.DIST_NO = b.EXTVALUE102 and a.SUB_NO = b.EXTVALUE105 and a.FUEL_TYPE = b.EXTVALUE123); There are 3 "extvaluexxx" that are set for each "category" for a total of 9 categories. This makes a grand total of 27 update statements. Any suggestions? It would be much appreciated! ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[SQL] Table Sorting and Limit Question
Currently we have a table with a sequence number( id ) as a primary key, a date field which is indexed and several other columns. The user interface allows the user to sort the data by date and limits the result set to 100 rows. The question is: The user interface needs the capability to sort the table by date but pull out the hundred row set that contains id say...542 for example. What would be the best way to do this taking into account this table is several hundred thousand rows? Dawn Hollingsworth Principal Engineer AirDefense, Inc. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Table Sorting and Limit Question
Since the id is a sequence type it should be unique. In this case it also happens to be my primary key into this table which will also make it unique. So the query below would only return 1 row. The part I'm missing is the offset number of the limit. Is there any way to figure out which offset into the query would contain the id I'm interested in. Dawn Hollingsworth Principal Engineer AirDefense, Inc. On Thu, 2002-08-08 at 21:42, Ludwig Lim wrote: > > --- Dawn Hollingsworth <[EMAIL PROTECTED]> wrote: > > > > > > Currently we have a table with a sequence number( id > > ) as a primary key, > > a date field which is indexed and several other > > columns. The user > > interface allows the user to sort the data by date > > and limits the result > > set to 100 rows. > > > > The question is: > > The user interface needs the capability to sort the > > table by date but > > pull out the hundred row set that contains id > > say...542 for example. > > > > What would be the best way to do this taking into > > account this table is > > several hundred thousand rows? > > try also to index the id. > > Try : > Select * > from > where id= > order by date > limit > > In this case you is 100 since you want to > return at most 100 rows. > > hope that helps. > > ludwig. > > > __ > Do You Yahoo!? > HotJobs - Search Thousands of New Jobs > http://www.hotjobs.com > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] how to do a find and replace
Hello- I'm using postgres 7.4 I have a column of data with the wrong prefix for many items. The wrong entries are entered ' /0/v.myimage.jpg' While the correct ones are ' /0/myimage.jpg' I need to remove all the 'v.' characters from this column. I'm able to do a SELECT * FROM myTable WHERE location = '%/0/v.%' I'm just confused about how I should write code to update the selected items to remove the 'v.' Would I use substring? An example would be much appreciated. thank you Dawn ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] how to do a find and replace
Hello- I have a column of data with the wrong prefix for many items. The wrong entries are entered ' /0/v.myimage.jpg' While the correct ones are ' /0/myimage.jpg' I need to remove all the 'v.' characters from this column. I'm able to do a SELECT * FROM myTable WHERE location = '%/0/v.%' I'm just confused about how I should write code to update the selected items to remove the 'v.' Would I use substring? An example would be much appreciated. thank you Dawn ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] how to do a find and replace
I used: update media_instance set location=replace(location,'v.','') where location like '%/0/v.%' and that did work- thank you very much. it seems to me that the replace function is the same as translate()- no? On 17-Nov-05, at 3:55 PM, Bricklen Anderson wrote: Dawn Buie wrote: Hello- I'm using postgres 7.4 I have a column of data with the wrong prefix for many items. The wrong entries are entered ' /0/v.myimage.jpg' While the correct ones are ' /0/myimage.jpg' I need to remove all the 'v.' characters from this column. I'm able to do a SELECT * FROM myTable WHERE location = '%/0/v.%' I'm just confused about how I should write code to update the selected items to remove the 'v.' Would I use substring? An example would be much appreciated. thank you Dawn How about update myTable set location=replace(location,'v.',''); -- ___ This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, use or copying of this e-mail or the information it contains by other than an intended recipient is unauthorized. If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately. ___ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
