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

What is happening when Matlab is starting a "parallel pool"? -

running parallel cpu processes in matlab starts command parpool() according documentation , function: [creates] special job on pool of workers, , [connects] matlab client parallel pool. this function takes bit of time execute, on order of 30 seconds. in other multi-cpu paradigms openmp, parallel execution seems totally transparent -- i've never noticed behavior analogous matlab (granted i'm not experienced parallel programming). so, happening between time parpool() called , when finishes executing? takes long? parallel computing toolbox enables run matlab code in parallel using several different paradigms (e.g. jobs , tasks, parfor , spmd , parfeval , batch processing), , run either locally (parallelised across cores in local machine) or remotely (parallelised across machines in cluster - either 1 own, or 1 in cloud). in of these cases, code run on matlab workers , copies of matlab without interactive desktop. if you're intending run on remo...
Read more

php - Cannot override Laravel Spark authentication with own implementation -

during login process of laravel spark need request token of remote api. api stores exact same users , passwords laravel spark application. therefore need username , non hashed password of user during authentication process. i thought overriding authenticated method solution problem. in routes/web.php override post /login endpoint , pointing endpoint own logincontroller: <?php namespace app\http\controllers\auth; use illuminate\support\facades\log; use illuminate\http\request; use laravel\spark\http\controllers\auth\logincontroller sparklogincontroller; class logincontroller extends sparklogincontroller { /** * create new login controller instance. * * @return void */ public function __construct() { parent::__construct(); } /** * handle successful authentication attempt. * * @param request $request * @param \illuminate\contracts\auth\authenticatable $user * @return response */ public fu...
Read more