Databases
StackOverflow2013
Postsdbo
POUpdate with limit and offset applied to joined table
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POUpdate with limit and offset applied to joined table
    text
    copied!<p>I have an UPDATE with an INNER JOIN. My overall question is how (if it is possible at all) to set LIMIT and OFFSET to that joined table.</p> <p>Example query without limit and offset:</p> <pre><code>UPDATE t2 SET t2.some_col = t1.some_col FROM table_1 t1 INNER JOIN table_2 t2 ON t1.other_col = t2.other_col </code></pre> <p>And how to rebuild this query to get only first 1000000, 1000000 - 2000000, 2000000 - 3000000, ... etc. records from t2.</p> <p>Exact scenery:</p> <p>My task is to rebuild very large tables with hash indexes (char(32)) to bigint indexes. Example tables: </p> <pre><code>URLS: PAGE_VIEWS: id char(32) urlId char(32) other_columns referrerUrlId char(32) intUrlId bigint (added and filled) other_columns intUrlId bigint (needs to update) intReferrerUrlId bigint (needs to update) </code></pre> <p>First table is about 200 mln records, second over 1 bln. I update this tables in packs. The update job wouldn't be difficult if I could use WHERE urls.intUrlId BETWEEN ... but I can't. Sometimes JOIN return on example 500000 records for single pack but many times it returns 0 so it update 0 records but join in such big tables costs quite a lot of time. So I need equal packs limited by page_views table not urls table. Page_views table has no column I can base WHERE clause so I need limit this table by TOP and ROW_NUMBER() clauses but I dunno how. (I'm quite new in MsSQL, I used to work on MySQL and PostgreSql databases which has LIMIT and OFFSET clauses.</p> <p>For any answer I would appreciate info about cost of this solution because someone would appreciate any LIMIT - OFFSET solution but not me. I already have query which update what I need. But it use intUrlId from urls table and it is slow. I need faster solution. Server version 2008.</p> <p>BTW. Don't ask me who the hell based database on char indexes :-) Now it become a problem and multi TBs database needs to be rebuilded. </p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload