SQL Server stored procedure: UPDATE with lowest value, else INSERT if there's already a value -


migrating 20k rows or @ once, hopefully. been away sql on 10 years , struggling.

i may going entirely wrong. need update table1.value2 lowest value table2.value1, unless there's value2. if latter, need insert row value table1.value1.

table1.value1 lowest value of each id row. value2 needs next lowest.

current table1:

id1, 123, [empty] id4, 111, [empty]

current table2:

id1, 224 id1, 331 id4, 210 id4, 551

table1 - desired state:

id1, 123, 224 id1, 331, [empty] id4, 111, 210 id4, 551, [empty]

table2 - desired state:

[empty]

here's tried , update section works correctly. insert never works. think coded myself corner.

create procedure dbo.broken     --declare variables     declare @id int,             @value1 int,             @value2 int,             @tmpvalue int      --declare counter     declare @counter int     set @counter = 1      --declare cursor query     declare ctable1 cursor read_only         select id,value1, value2         table1       open ctable1      --fetch variables     fetch next ctable1 @id, @value1, @value2      --loop      while @@fetch_status = 0     begin         if (@value2 = '' or @value2 null)         begin             --update table1.value2 next lowest table1.value2 > table1.value1             set @tmpvalue = (select min(value1) table2  value1 > @value1 , id = @id)              update table1              set value2 = @tmpvalue                  id = @id              --delete affected row table2             delete table2              value1 = @tmpvalue , id = @id;         end         else         begin             --insert row in table1 id, value1 table2              set @id = (select min(id) table2)             set @tmpvalue = (select min(value1) table2 id = @id)              insert table1(id, value1, value2)              values (@id, @tmpvalue, '')              delete table2              id = @id         end          --fetch next variables         fetch next ctable1 @id, @value1, @value2     end      --close cursor table     close ctable1     deallocate ctable1

there 20 thousand rows, i'm hoping stored procedure accomplish all. it's one-off, i'm not super concerned overhead.

the basic query needed update pretty clear:

update table1 set value2 = (     select min(t2.value1)      table2 t2     t2.value1 > table1.value1        , t2.id = table1.id     )

i perform insert this:

insert table1 (id, value1, value2) select id,     value1,     [empty] table2 t2 left join table1 t1     on t2.id = t1.id     , t2.value1 = t1.value1 left join table1 t1a     on t2.id = t1a.id     , t2.value1 = t1a.value2 t1.id null     , t1a.id null  delete table2

note meets stated requirements, may not meet actual requirements - comment @jeroenmostert covers number of issues.


Comments

Post a Comment

Popular posts from this blog

Is there a better way to structure post methods in Class Based Views -

i'm using class based views in django , never specify url in ajax calls , omit action parameter in form because know these requests go through post method of class based view. notice view - profile page make many requests depending on user updating on page - post method become convoluted , if/else mess example: def post(self, request, *args, **kwargs): # handle user changing available date if request.post.get('availabledate') != none: self.updateavailabledate(request) return jsonresponse({'result':'success'}) if request.post.get('newprojectname') != none: creative_user = creativeuserprofile.objects.get(id = request.user.id) project = project.create_project(creative_user, request.post['newprojectname']) project.save() return jsonresponse({'projectid': project.id }) if request.post.get('projectimage') != none , request.files['file']: proj...
Read more

performance - Why is XCHG reg, reg a 3 micro-op instruction on modern Intel architectures? -

i'm doing micro-optimization on performance critical part of code , came across sequence of instructions (in at&t syntax): add %rax, %rbx mov %rdx, %rax mov %rbx, %rdx i thought had use case xchg allow me shave instruction , write: add %rbx, %rax xchg %rax, %rdx however, dimay found agner fog's instruction tables , xchg 3 micro-op instruction 2 cycle latency on sandy bridge, ivy bridge, broadwell, haswell , skylake. 3 whole micro-ops , 2 cycles of latency! 3 micro-ops throws off 4-1-1-1 cadence , 2 cycle latency makes worse original in best case since last 2 instructions in original might execute in parallel. now... cpu might breaking instruction micro-ops equivalent to: mov %rax, %tmp mov %rdx, %rax mov %tmp, %rdx where tmp anonymous internal register , suppose last 2 micro-ops run in parallel latency 2 cycles. given register renaming occurs on these micro-architectures, though, doesn't make sense me done way. why wouldn't register renamer ...
Read more

c# - Asp.net web api : redirect unauthorized requst to forbidden page -

im trying redirect unauthorized request forbidden page instead i'm getting forbidden page in response body , how can fix ? here's startup class : app.createperowincontext(storecontext.create); app.createperowincontext<applicationusermanager>(applicationusermanager.create); app.createperowincontext<applicationsigninmanager>(applicationsigninmanager.create); app.usecookieauthentication(new cookieauthenticationoptions { authenticationtype = defaultauthenticationtypes.applicationcookie, expiretimespan = timespan.fromdays(30), }); app.useexternalsignincookie(defaultauthenticationtypes.externalcookie); app.usetwofactorsignincookie(defaultauthenticationtypes.twofactorcookie, timespan.fromminutes(5)); app.usetwofactorrememberbrowsercookie(defaultauthenticationtypes.twofactorrememberbrowsercookie); the method im trying reach : [httpget] [authorize(roles = "admin")] public string getcurrentusername() { return userman...
Read more