Re: [sqlite] updating using a value from another table

[dean gwilliam]

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

[dean gwilliam]

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

[ve3meo]

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

[Keith Medcalf]

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

[Igor Tandetnik]

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

[James K. Lowden]

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

[Kurt Welgehausen]

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

[dean gwilliam]

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

[dean gwilliam]

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

[dean gwilliam]

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

[dean gwilliam]

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

[dean gwilliam]

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

[Simon Slavin]

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

[Rob Richardson]

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

[dean gwilliam]

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

[Simon Slavin]

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

[Rob Richardson]

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

[dean gwilliam]

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