Re: [sqlite] got selected items ok but can't update using them

[e-mail mgbg25171]

Igor, Michael
This syntax "create table copy as select * from input.t1;" is very new to
me but certainly does "the business" in conjuntion with "attach"
SQLIte Manager doesn't know what "dump" is
Irrespective...thank you very much for your help.


On 20 February 2013 13:56, Michael Black <mdblac...@yahoo.com> wrote:

> Sqlite3 run without any arguments
> But did I discover a bug too?
> Why doesn't .dump allow input.t1 to be shown?
>
> SQLite version 3.7.14.1 2012-10-04 19:37:12
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> attach database "blah.db" as input;
> sqlite> create table input.t1(n);
> sqlite> insert into input.t1 values(1);
> sqlite> insert into input.t1 values(2);
> sqlite> create table copy as select * from input.t1;
> sqlite> .dump
> PRAGMA foreign_keys=OFF;
> BEGIN TRANSACTION;
> CREATE TABLE copy(n);
> INSERT INTO "copy" VALUES(1);
> INSERT INTO "copy" VALUES(2);
> COMMIT;
> sqlite> .dump input.t1
> PRAGMA foreign_keys=OFF;
> BEGIN TRANSACTION;
> COMMIT;
> sqlite> select * from t1;
> 1
> 2
>
>
> -Original Message-
> From: sqlite-users-boun...@sqlite.org
> [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of e-mail mgbg25171
> Sent: Wednesday, February 20, 2013 7:46 AM
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] got selected items ok but can't update using them
>
> Igor, Michael
> Thank you very much for your advice re how to use Update with compound
> queries
>
> I'm currently trying to copy the rows of stmnts with a blank itm field to a
> temporary table.
> I don't think virtual tables are implemented in my programming language's
> interface and so wanted to
> copy the stmnts table from the real database where itm='' to a table in a
> :memory: database.
> I appreciate that the statement is normally
> insert into tbl values (fld1 integer, fld2 text);
> but how do I fill a table in a :memory: db with the contents of another in
> a real database?
> I'll keep looking into it.
>
> ___
> 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] got selected items ok but can't update using them

[Igor Tandetnik]

On 2/20/2013 8:46 AM, e-mail mgbg25171 wrote:

but how do I fill a table in a :memory: db with the contents of another in
a real database?


http://sqlite.org/lang_attach.html

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] got selected items ok but can't update using them

[Michael Black]

You need the selects in each = expression to replace the variables there.
Something like this if I translated your query correctly.

UPDATE stmnts SET itm=(SELECT itm FROM std_itms where ID = std_id) where
ID=(SELECT alias_id from alias_itms);

-Original Message-
From: sqlite-users-boun...@sqlite.org
[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of e-mail mgbg25171
Sent: Wednesday, February 20, 2013 6:46 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] got selected items ok but can't update using them

Thanks Simon...
Your's works a treat
Re your question about my error

Here's my query

UPDATE stmnts SET itm=n where ID=i
(
SELECT alias_id i,
(SELECT orig_itm FROM stmnts where ID = alias_id) o,
(SELECT itm FROM std_itms where ID = std_id) n
FROM
(SELECT std_id, alias_id FROM alias_itms)
)



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] got selected items ok but can't update using them

[Igor Tandetnik]

On 2/20/2013 7:45 AM, e-mail mgbg25171 wrote:

Here's my query

UPDATE stmnts SET itm=n where ID=i
(
SELECT alias_id i,
(SELECT orig_itm FROM stmnts where ID = alias_id) o,
(SELECT itm FROM std_itms where ID = std_id) n
FROM
(SELECT std_id, alias_id FROM alias_itms)
)


This doesn't even remotely resemble a syntactically valid UPDATE statement.
--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] got selected items ok but can't update using them

[Igor Tandetnik]

On 2/20/2013 6:36 AM, e-mail mgbg25171 wrote:

SELECT i,o,n FROM
(
SELECT alias_id i,
(SELECT orig_itm FROM stmnts where ID = alias_id) o,
(SELECT itm FROM std_itms where ID = std_id) n
FROM
(SELECT std_id, alias_id FROM alias_itms)
)


This can be written simpler as

select alias_id i, orig_itm o, std_itms.itm n
from alias_itms join stmnts on (alias_id = stmnts.ID)
  join std_itms on (std_id = std_itms.ID);

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] got selected items ok but can't update using them

[e-mail mgbg25171]

Thanks Simon...
Your's works a treat
Re your question about my error

Here's my query

UPDATE stmnts SET itm=n where ID=i
(
SELECT alias_id i,
(SELECT orig_itm FROM stmnts where ID = alias_id) o,
(SELECT itm FROM std_itms where ID = std_id) n
FROM
(SELECT std_id, alias_id FROM alias_itms)
)

and here's my error

SQLiteManager: Likely SQL syntax error: UPDATE stmnts SET itm=n where ID=i
(
SELECT alias_id i,
(SELECT orig_itm FROM stmnts where ID = alias_id) o,
(SELECT itm FROM std_itms where ID = std_id) n
FROM
(SELECT std_id, alias_id FROM alias_itms)
)
 [ near "SELECT": syntax error ]
Exception Name: NS_ERROR_FAILURE
Exception Message: Component returned failure code: 0x80004005
(NS_ERROR_FAILURE) [mozIStorageConnection.createStatement]

Thanks for your help

On 20 February 2013 12:01, Simon Davies wrote:

> On 20 February 2013 11:36, e-mail mgbg25171 
> wrote:
> > After a bit of a struggle I've got the fields I want but I'm failing to
> use
> > them in an update operation.
> > This
> >
> > SELECT i,o,n FROM
> > (
> > SELECT alias_id i,
> > (SELECT orig_itm FROM stmnts where ID = alias_id) o,
> > (SELECT itm FROM std_itms where ID = std_id) n
> > FROM
> > (SELECT std_id, alias_id FROM alias_itms)
> > )
> >
> > works fine and gives me
> > i, o, n  where i, o, n equals id, old, new
> > 
> > 0, turnover, sales
> > 1, cost of sales, cogs
> >
> > now I want to say
> > update stmnts set itm = n where id = i
> > but I'm getting errors
>
> It would help to be clear what the errors are.
>
> >
> > Table fields
> > stmnts__id, orig_itm, itm
> > alias_itmsid, std_id, alias_id
> > std_itms_id, itm
> >
> > Any help much appreciated
>
> Maybe:
> update stmnts set itm=(select itm from std_itms s, alias_itms a on
> s.id=a.std_id where a.alias_id=stmnts.id);
>
> Regards,
> Simon
> ___
> 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] got selected items ok but can't update using them

[Simon Davies]

On 20 February 2013 11:36, e-mail mgbg25171  wrote:
> After a bit of a struggle I've got the fields I want but I'm failing to use
> them in an update operation.
> This
>
> SELECT i,o,n FROM
> (
> SELECT alias_id i,
> (SELECT orig_itm FROM stmnts where ID = alias_id) o,
> (SELECT itm FROM std_itms where ID = std_id) n
> FROM
> (SELECT std_id, alias_id FROM alias_itms)
> )
>
> works fine and gives me
> i, o, n  where i, o, n equals id, old, new
> 
> 0, turnover, sales
> 1, cost of sales, cogs
>
> now I want to say
> update stmnts set itm = n where id = i
> but I'm getting errors

It would help to be clear what the errors are.

>
> Table fields
> stmnts__id, orig_itm, itm
> alias_itmsid, std_id, alias_id
> std_itms_id, itm
>
> Any help much appreciated

Maybe:
update stmnts set itm=(select itm from std_itms s, alias_itms a on
s.id=a.std_id where a.alias_id=stmnts.id);

Regards,
Simon
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] got selected items ok but can't update using them

[e-mail mgbg25171]

After a bit of a struggle I've got the fields I want but I'm failing to use
them in an update operation.
This

SELECT i,o,n FROM
(
SELECT alias_id i,
(SELECT orig_itm FROM stmnts where ID = alias_id) o,
(SELECT itm FROM std_itms where ID = std_id) n
FROM
(SELECT std_id, alias_id FROM alias_itms)
)

works fine and gives me
i, o, n  where i, o, n equals id, old, new

0, turnover, sales
1, cost of sales, cogs

now I want to say
update stmnts set itm = n where id = i
but I'm getting errors

Table fields
stmnts__id, orig_itm, itm
alias_itmsid, std_id, alias_id
std_itms_id, itm

Any help much appreciated
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users