sqlite update each row of a column with the result of a select (again) -



need add column table fill computed values select (from orignal table itself) this

create table yo (ad integer primary key, pa integer, pd integer); insert yo values   (1,1,1),(2,1,3),(3,1,4),(4,3,5),(5,4,2),(6,3,8),(7,1,9),(8,6,7),(9,3,6); .header on .mode column yo; select * yo; ad          pa          pd ----------  ----------  ---------- 1           1           1 2           1           3 3           1           4 4           3           5 5           4           2 6           3           8 7           1           9 8           6           7 9           3           6

the 'ad' col kinda address, 'pa' col parent address, 'pd' col kinda id or value row. i'd add col named 'pp' 'parent id' computed this

select yo2.pd yo yo1 join yo yo2 on yo2.ad=yo1.pa; pd ---------- 1 1 1 4 5 4 1 8 4

so i'd setup 'pp' whit output tried must miss somethings

alter table yo add column pp integer; update yo set pp =   (select yo2.pd pp yo yo1 join yo yo2 on yo2.ad=yo1.pa);  ad    pa   pd   pp ----  ---  ---  ----  1     1    1    1 2     1    3    1 3     1    4    1 4     3    5    1 5     4    2    1 6     3    8    1 7     1    9    1 8     6    7    1 9     3    6    1

does want achieve doable, othre link on similar proble don't understand answers, , found tiny case this

create table t1 (a int, b int); insert t1 values (1,0),(2,0); create table t2 (a int, c int); insert t2 values (1,101),(2,102); update t1 set b = (select c t2 t2.a = t1.a) t1.b = 0;

this 1 update correctly why select differ later one?
thanx advise
phi


i found way achieve want using temp table this

create temp table ziv   select yo1.ad,yo2.pd pp yo yo1 join yo yo2 on yo2.ad=yo1.pa update yo set pp=(select temp.ziv.pp temp.ziv temp.ziv.ad=yo.ad) select * yo ad  pa   pd  pp --- ---  --- --- 1   1    1   1 2   1    3   1 3   1    4   1 4   3    5   4 5   4    2   5 6   3    8   4 7   1    9   1 8   6    7   8 9   3    6   4

now wonder can done 1 update (no temp table) thanx in advance
cheers
phi


Comments

Post a Comment

Popular posts from this blog

javascript - Google App Script ContentService downloadAsFile not working -

i have web app developed using google app script htmlservice , html form, populating excel sheet in google drive using spreadsheetapp . , 1 section calling contentservice download data excel file. function doget(e) { // read excel sheet //getappfile(); // render application html template return htmlservice.createtemplatefromfile('index').evaluate() .settitle('go smart') .setsandboxmode(htmlservice.sandboxmode.iframe); } function downloaddoublequatecsvfile() { var sheetid = propertiesservice.getscriptproperties().getproperty('sheetid'); var ss = spreadsheetapp.openbyid(sheetid).getactivesheet(); //var ss = spreadsheetapp.getactivespreadsheet().getactivesheet(); var maxcolumn = ss.getlastcolumn(); var maxrow = ss.getlastrow(); var data = ss.getrange(1, 1, maxrow, maxcolumn).getvalues(); if (data.length > 1) { var csv = ""; (var row = 0; row < data.length; row++) { (var c...
Read more

javascript - Function overwritting -

is there way overwrite function or event handling in function? using addeventlistener, can have many event handlers on element want var test=document.getelementbyid("div"); test.addeventlistener("click",function(){alert("im first");},false); test.addeventlistener("click",function(){alert("im second");},false); // alerts "im first" , after "im second" but if want overwrite function/event example based on client width example this function one() { alert("first"); } function two() { alert("second"); } window.onresize = function() { var test = document.getelementbyid("div"); if (window.innerwidth > 500) { test.onclick = 1 } else { test.onclick = two; } } is possible in javascript? in case use effective little known approach. addeventlistener can accept object property handleevent event handler. in case it's easy overwr...
Read more

php - Find a regex to take part of Email -

i have following emails need extract part from: bestellnummer xxx: 1 von xxx, 1er pack ------------- anfang der nachricht ------------- foo bar baz foo bar baz // <<<<< need text here ------------- ende der nachricht ------------- ------------- anfang der nachricht ------------- foo bar baz foo bar baz ------------- ende der nachricht ------------- there 0 unlimited occurences of ------------- anfang der nachricht ------------- ------------- ende der nachricht ------------- and i'm able extract first part regex: $re = "/------------- .*? -------------.?(.*?).?------------- .*? -------------/s"; but, i'm quite new on learning regex, i'm pretty sure there must better regex extract part (foo bar baz foo bar baz) of text between ------------- anfang der nachricht ------------- ------------- ende der nachricht ------------- as can in different languages, i'm using .? to match between hyphens. i...
Read more