Re: [sqlite] updating using a value from another table
Kurt, Keith Thanks very much for your help. It's very much appreciated. Best Regards Dean ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] updating using a value from another table
JKL, Igor, Tom Thank you very much for the advice. It's very much appreciated and helps A LOT! Best Regards Dean ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] updating using a value from another table
Igor Tandetnik-2 wrote > Or alternatively, without a WHERE clause: > > update a set i = coalesce((select i from b where b.a = a.a), i); What I have used similar to this is: UPDATE a SET i = ifnull((select i from b where b.a = a.a), i); Tom -- View this message in context: http://sqlite.1065341.n5.nabble.com/updating-using-a-value-from-another-table-tp71588p71607.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] updating using a value from another table
Mayhaps, update itms set std_nm = (select std_nm from aliases where raw_nm=itms.raw_nw) where exists (select 1 from aliases where raw_nm=itms.raw_nw); which translates to english as: for each row in itms where there exists a row in aliases where aliases.raw_nm=itms.raw_nm set itms.raw_nm to aliases.std_nm where aliases.raw_nm=itms.raw_nm This UPDATE itms SET std_nm=(SELECT std_nm FROM aliases as x WHERE raw_nm = x.raw_nm) translates to english as: for each row in itms set itms.std_nm to the std_nm located in the first row of aliases where aliases.raw_nm=aliases.raw_nm >-Original Message- >From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- >boun...@sqlite.org] On Behalf Of dean gwilliam >Sent: Monday, 7 October, 2013 05:48 >To: sqlite-users@sqlite.org >Subject: [sqlite] updating using a value from another table > >Here it is >tbl itms (std_nm text, raw_nm text) >tbl aliases (std_nm text, raw_nm text) > >Id like to whip through table itms which has a blank col std_nm and fill >in as many fields as I can >by >reading each itms' raw_nm fld >finding any matching rec (with the same raw_nm value) in aliases >updating the std_nm fld in itms with corresponding std_nm value in the >matching fld in aliases > >Here's my miserable attempt >gDb eval "UPDATE itms SET std_nm=(SELECT std_nm FROM aliases as x WHERE >raw_nm = x.raw_nm);" > >Any help much appreciated >___ >sqlite-users mailing list >sqlite-users@sqlite.org >http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] updating using a value from another table
On 10/7/2013 7:41 PM, James K. Lowden wrote: On Mon, 07 Oct 2013 12:48:10 +0100 dean gwilliam wrote: Here's my miserable attempt gDb eval "UPDATE itms SET std_nm=(SELECT std_nm FROM aliases as x WHERE raw_nm = x.raw_nm);" Oh, so close! An update statement without a WHERE clause updates the whole table. In your case, any rows in itms not matching in aliases will result in a itms.std_nm becoming NULL. You need two subqueries: one to set the value, and the other to restrict the rows updated. sqlite> begin transaction; -- do it right this time sqlite> update a set i = (select i from b where b.a = a.a) where exists (select 1 from b where a.a = b.a); Or alternatively, without a WHERE clause: update a set i = coalesce((select i from b where b.a = a.a), i); -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] updating using a value from another table
On Mon, 07 Oct 2013 12:48:10 +0100 dean gwilliam wrote: > Here's my miserable attempt > gDb eval "UPDATE itms SET std_nm=(SELECT std_nm FROM aliases as x > WHERE raw_nm = x.raw_nm);" Oh, so close! An update statement without a WHERE clause updates the whole table. In your case, any rows in itms not matching in aliases will result in a itms.std_nm becoming NULL. You need two subqueries: one to set the value, and the other to restrict the rows updated. Here's a simpler example. -- table to update sqlite> create table a (a int, i int); sqlite> insert into a (a) values (1), (2); sqlite> insert into a values (3, 'three'); sqlite> select * from a; a i -- -- 1 2 3 three -- table to update from sqlite> create table b as select * from a where a < 3; sqlite> update b set i = 'one' where a = 1; sqlite> update b set i = 'two' where a = 2; sqlite> select * from b; a i -- -- 1 one 2 two sqlite> begin transaction; -- illustrate error sqlite> update a set i = (select i from b where b.a = a.a); sqlite> select * from a; a i -- -- 1 one 2 two 3 sqlite> rollback; -- oops sqlite> begin transaction; -- do it right this time sqlite> update a set i = (select i from b where b.a = a.a) where exists (select 1 from b where a.a = b.a); sqlite> select * from a; a i -- -- 1 one 2 two 3 three sqlite> commit; -- ta da On a side note, "items" is only one letter longer than "itms", and you can read one and not the othr. If you use whole words for your table and column names, you'll save yourself remembering what abbreviation you used, and of conflicting/inconsistent abbreviations. You'd be in good company. Brian Kernighan, on being asked what he'd change about Unix given the chance, supposedly said, "I'd add an 'e' to 'creat'" (refering to the creat(2) syscall). --jkl ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] updating using a value from another table
Simon Slavin wrote: > > On 7 Oct 2013, at 3:45pm, dean gwilliam wrote: > > > sqlite> UPDATE itms SET std_nm=(SELECT std_nm FROM aliases as x WHERE > > raw_nm = x > > .raw_nm); > > looking at it without the 'AS' ... > > UPDATE itms SET std_nm=(SELECT std_nm FROM aliases WHERE raw_nm = > aliases.raw_nm); > > I'm wondering whether you actually mean > > UPDATE itms SET std_nm=(SELECT std_nm FROM aliases WHERE raw_nm = > itms.raw_nm); > > or something like that. > > Simon. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users sqlite> create table t (k integer primary key, d integer); sqlite> insert into t (k) values (1); sqlite> insert into t (k) values (2); sqlite> insert into t (k) values (3); sqlite> create table t2 (k integer primary key, d integer); sqlite> insert into t2 (d) values (101); sqlite> insert into t2 (d) values (102); sqlite> select * from t; k d -- -- 1 <> 2 <> 3 <> sqlite> select * from t2; k d -- -- 1 101 2 102 sqlite> update t set d = (select d from t2 where t2.k = t.k); sqlite> select changes(); changes() -- 3 sqlite> select * from t; k d -- -- 1 101 2 102 3 <> sqlite> update t set d = null; sqlite> update t set d = 103 where k = 3; sqlite> select * from t; k d -- -- 1 <> 2 <> 3 103 sqlite> update t set d = (select d from t2 where t2.k = t.k); sqlite> select changes(); changes() -- 3 sqlite> select * from t; k d -- -- 1 101 2 102 3 <> sqlite> update t set d = null; sqlite> update t set d = 103 where k = 3; sqlite> select * from t; k d -- -- 1 <> 2 <> 3 103 sqlite> update t set d = (select d from t2 where t2.k = t.k) ...> where t.k in (select k from t2); sqlite> select changes(); changes() -- 2 sqlite> select * from t; k d -- -- 1 101 2 102 3 103 In the original post, the columns raw_nm and x.raw_nm are the same column, so the condition is always true. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] updating using a value from another table
It's working now I just needed to use a capital first character in alias.raw_nm. Now it's working in the big program too. Simon, Rob...thanks for your help/reassurance ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] updating using a value from another table
It's not working in my big program though so... I'll try to see what's different i.e. I'm getting nothing in itms.std_nm using the test program update query despite it working in my test program ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] updating using a value from another table
I seem to have cracked it with a cut-down example package require sqlite3 set gDb {} set db_fl_nm "[pwd]\\test.db" ;# :memory: proc mbx {x} { set answer [tk_messageBox -message $x -type yesno -icon question] switch -- $answer { yes {} no exit } } proc mfl {x} {global gOfl; puts $gOfl $x} proc tbl_app {tbl data} { set max_id [expr [gDb eval "select max(id) FROM $tbl;"]] if {$max_id == ""} {set max_id 0} set id [expr $max_id + 1] gDb eval "insert into $tbl values ( $id, $data )" } proc doit {db_pth} { global gDb sqlite3 gDb $db_pth gDb eval "drop table if exists itms" gDb eval "drop table if exists aliases" gDb eval "create table if not exists itms (id integer primary key, std_nm text, raw_nm text);" gDb eval "create table if not exists aliases (id integer primary key, std_nm text, raw_nm text);" tbl_app itms "null, 'aaa'" tbl_app itms "null, 'bbb'" tbl_app itms "null, 'ccc'" tbl_app aliases "'std_nm1', 'aaa'" tbl_app aliases "'std_nm2', 'bbb'" gDb eval "UPDATE itms SET std_nm= (SELECT std_nm FROM aliases WHERE itms.raw_nm = aliases.raw_nm);" #(SELECT std_nm FROM aliases WHERE itms.raw_nm = 'bbb');" gDb close } doit $db_fl_nm exec SQLiteSpy $db_fl_nm ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] updating using a value from another table
Thanks Simon I tried that but it writes nothing to itms.std_nm ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] updating using a value from another table
RobR Sorry for the delay...I needed to regenerate the database which took several minutes and processes after I messed it up. Here's the "completed" itms table. I don't understand why sales is written to the std_nm column in itms when each rows corresponding raw_nm field does not appear in aliases and particularly with a corresponding sales field. Hope I've made my self clear and thank you for your help SQLite version 3.8.0.2 2013-09-03 17:11:13 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> select * from aliases; 1|sales|sales 2|sales|turnover 3|sales|revenue 4|sales|revenues 5|cogs|cost_of_sales sqlite> select * from itms limit 10; 1|dummy|2005|inc|sales|for_the_year_ended_30_April|2005.0 2|dummy|2005|inc|sales|Turnover|150645.0 3|dummy|2005|inc|sales|Cost_of_sales|-6327.0 4|dummy|2005|inc|sales|Gross_profit|144318.0 5|dummy|2005|inc|sales|Selling_and_distribution_costs|-48106.0 6|dummy|2005|inc|sales|Research_and_development|-23407.0 7|dummy|2005|inc|sales|Amortisation_of_goodwill|-3769.0 8|dummy|2005|inc|sales|Share-based_compensation_payments|-3581.0 9|dummy|2005|inc|sales|Reorganisation_costs|-2302.0 10|dummy|2005|inc|sales|Other_administrative_expenses|-27229.0 sqlite> ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] updating using a value from another table
On 7 Oct 2013, at 3:45pm, dean gwilliam wrote: > sqlite> UPDATE itms SET std_nm=(SELECT std_nm FROM aliases as x WHERE raw_nm > = x > .raw_nm); looking at it without the 'AS' ... UPDATE itms SET std_nm=(SELECT std_nm FROM aliases WHERE raw_nm = aliases.raw_nm); I'm wondering whether you actually mean UPDATE itms SET std_nm=(SELECT std_nm FROM aliases WHERE raw_nm = itms.raw_nm); or something like that. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] updating using a value from another table
Thank you. Now, can you show us sample data from your tables before this query is run? RobR -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of dean gwilliam Sent: Monday, October 07, 2013 10:45 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] updating using a value from another table Simon, Rob Thanks very much for your responses. I downloaded and used the shell...it gives the same results i.e. the first std_itm fld in aliases irrespective of what the value of raw_fld is in itms SQLite version 3.8.0.2 2013-09-03 17:11:13 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> UPDATE itms SET std_nm=(SELECT std_nm FROM aliases as x WHERE raw_nm = x .raw_nm); sqlite> select std_nm from itms limit 10 ...> ; sales sales sales sales sales sales sales sales sales sales sqlite> ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] updating using a value from another table
Simon, Rob Thanks very much for your responses. I downloaded and used the shell...it gives the same results i.e. the first std_itm fld in aliases irrespective of what the value of raw_fld is in itms SQLite version 3.8.0.2 2013-09-03 17:11:13 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> UPDATE itms SET std_nm=(SELECT std_nm FROM aliases as x WHERE raw_nm = x .raw_nm); sqlite> select std_nm from itms limit 10 ...> ; sales sales sales sales sales sales sales sales sales sales sqlite> ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] updating using a value from another table
On 7 Oct 2013, at 1:59pm, Rob Richardson wrote: > gDb eval "UPDATE itms SET std_nm=(SELECT std_nm FROM aliases as x WHERE > raw_nm = x.raw_nm);" What happens if you use the SQLite shell tool to open your database and execute this command ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] updating using a value from another table
Your query looks good to me, which probably means I'm missing the same thing you are. What happens when you run this query? RobR -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of dean gwilliam Sent: Monday, October 07, 2013 7:48 AM To: sqlite-users@sqlite.org Subject: [sqlite] updating using a value from another table Here it is tbl itms (std_nm text, raw_nm text) tbl aliases (std_nm text, raw_nm text) Id like to whip through table itms which has a blank col std_nm and fill in as many fields as I can by reading each itms' raw_nm fld finding any matching rec (with the same raw_nm value) in aliases updating the std_nm fld in itms with corresponding std_nm value in the matching fld in aliases Here's my miserable attempt gDb eval "UPDATE itms SET std_nm=(SELECT std_nm FROM aliases as x WHERE raw_nm = x.raw_nm);" Any help much appreciated ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] updating using a value from another table
Here it is tbl itms (std_nm text, raw_nm text) tbl aliases (std_nm text, raw_nm text) Id like to whip through table itms which has a blank col std_nm and fill in as many fields as I can by reading each itms' raw_nm fld finding any matching rec (with the same raw_nm value) in aliases updating the std_nm fld in itms with corresponding std_nm value in the matching fld in aliases Here's my miserable attempt gDb eval "UPDATE itms SET std_nm=(SELECT std_nm FROM aliases as x WHERE raw_nm = x.raw_nm);" Any help much appreciated ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users