Click here to Skip to main content
14,689,292 members
Home » Discussions » Database
   

Database

 
GeneralRe: Debug a procedure in Oracle which feeds into crystal report Pin
Member 1447460731-May-19 6:49
MemberMember 1447460731-May-19 6:49 
GeneralRe: Debug a procedure in Oracle which feeds into crystal report Pin
Qingyong Yu16-Jul-19 19:51
MemberQingyong Yu16-Jul-19 19:51 
NewsMessage Closed Pin
28-May-19 6:51
MemberMember 1447108128-May-19 6:51 
QuestionCentOS6.9 (MySql v5.7.22) use mysql C API mysql_real_query cause the memory always growing Pin
normga23-May-19 4:13
Membernormga23-May-19 4:13 
AnswerRe: CentOS6.9 (MySql v5.7.22) use mysql C API mysql_real_query cause the memory always growing Pin
jschell23-May-19 7:41
Memberjschell23-May-19 7:41 
GeneralRe: CentOS6.9 (MySql v5.7.22) use mysql C API mysql_real_query cause the memory always growing Pin
normga23-May-19 10:27
Membernormga23-May-19 10:27 
GeneralRe: CentOS6.9 (MySql v5.7.22) use mysql C API mysql_real_query cause the memory always growing Pin
jschell2-Jun-19 9:25
Memberjschell2-Jun-19 9:25 
QuestionBest way to batchprocess a large update Pin
Jörgen Andersson23-May-19 3:09
professionalJörgen Andersson23-May-19 3:09 
So I need to regularly update a table with data from another table.
The problem is that if I update the normal way I get a table lock on the target table for half an hour, which is frowned upon by the users. So I need to run the update in batches.
The other problem is that the ID sequence is having gaps in it. Larger gaps than the batch size.
At the moment I have this solution:
DECLARE
        @LastID int = 0,
        @NextID int,
        @RC int = 1;
    
WHILE (@RC > 0)
BEGIN
    SELECT  TOP 5000
            @NextID = s.id
    FROM    Source s
    WHERE   s.id> @LastID
    ORDER BY s.id
    ;
    UPDATE  t
        SET  ------
    FROM    Source s
    JOIN    Target t ON t.id = s.id
    WHERE   s.id > @LastID 
        AND s.id <= @NextID 
    ;
    SET @RC = @@ROWCOUNT;
    SET @LastID = @NextID ;
END

Which works just fine, but using two selects is getting under my skin.

Any better suggestions for how to do it?
Wrong is evil and must be defeated. - Jeff Ello

AnswerRe: Best way to batchprocess a large update Pin
Richard Deeming23-May-19 8:38
mveRichard Deeming23-May-19 8:38 
GeneralRe: Best way to batchprocess a large update Pin
Jörgen Andersson23-May-19 8:52
professionalJörgen Andersson23-May-19 8:52 
GeneralRe: Best way to batchprocess a large update Pin
Jörgen Andersson23-May-19 23:08
professionalJörgen Andersson23-May-19 23:08 
GeneralRe: Best way to batchprocess a large update Pin
Richard Deeming24-May-19 1:44
mveRichard Deeming24-May-19 1:44 
GeneralRe: Best way to batchprocess a large update Pin
Jörgen Andersson24-May-19 2:45
professionalJörgen Andersson24-May-19 2:45 
GeneralRe: Best way to batchprocess a large update Pin
Jörgen Andersson27-May-19 0:17
professionalJörgen Andersson27-May-19 0:17 
AnswerRe: Best way to batchprocess a large update Pin
Eddy Vluggen24-May-19 1:04
professionalEddy Vluggen24-May-19 1:04 
GeneralRe: Best way to batchprocess a large update Pin
Jörgen Andersson24-May-19 2:24
professionalJörgen Andersson24-May-19 2:24 
AnswerRe: Best way to batchprocess a large update Pin
#realJSOP29-May-19 1:43
mva#realJSOP29-May-19 1:43 
GeneralRe: Best way to batchprocess a large update Pin
Richard Deeming29-May-19 1:52
mveRichard Deeming29-May-19 1:52 
GeneralRe: Best way to batchprocess a large update Pin
#realJSOP29-May-19 2:06
mva#realJSOP29-May-19 2:06 
GeneralRe: Best way to batchprocess a large update Pin
Richard Deeming29-May-19 2:12
mveRichard Deeming29-May-19 2:12 
GeneralRe: Best way to batchprocess a large update Pin
#realJSOP29-May-19 2:20
mva#realJSOP29-May-19 2:20 
GeneralRe: Best way to batchprocess a large update Pin
Jörgen Andersson29-May-19 3:59
professionalJörgen Andersson29-May-19 3:59 
GeneralRe: Best way to batchprocess a large update Pin
#realJSOP29-May-19 6:13
mva#realJSOP29-May-19 6:13 
AnswerRe: Best way to batchprocess a large update Pin
#realJSOP29-May-19 2:14
mva#realJSOP29-May-19 2:14 
GeneralRe: Best way to batchprocess a large update Pin
Jörgen Andersson29-May-19 4:01
professionalJörgen Andersson29-May-19 4:01 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.