Re: [sqlite] Conditional lowering of value

2019-01-04 Thread Keith Medcalf 

On Friday, 4 January, 2019 04:31, Cecil Westerhof wrote:

>I have the following query:

>SELECT MIN(totalUsed) - 1
>FROM   quotes
>WHERE  totalUsed <> 'notUsed'

>What I want is that when the SELECT returns a positive value every
>record where totalUsed <> 'notUsed' is lowered with the returned value. Is
>easy to accomplish in programming code, but I was wondering if this could be
>done with a SQL statement.

The English to SQL translator returned the following:

UPDATE quotes
   SET totalUsed = totalUsed - (select min(totalUsed) - 1
  from quotes
 where totalUsed <> 'notUsed')
WHERE ((select min(totalUsed) - 1
  from quotes
 where totalUsed <> 'notUsed')) > 0
   AND totalUsed <> 'notUsed';



---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.




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

Re: [sqlite] Conditional lowering of value

2019-01-04 Thread David Raymond 
He's currently getting a value one query, and using the value in the next, and 
is wondering if he can do it in 1 query instead.

You can use a CTE to load the value, then use that for the subtract.


SQLite version 3.26.0 2018-12-01 12:34:55
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table quotes (totalUsed);
QUERY PLAN
`--SEARCH TABLE sqlite_master USING INTEGER PRIMARY KEY (rowid=?)

sqlite> insert into quotes values (3), ('notUsed'), (7), (12), ('notUsed');
QUERY PLAN
`--SCAN 5 CONSTANT ROWS

sqlite> select * from quotes;
QUERY PLAN
`--SCAN TABLE quotes
totalUsed
3
notUsed
7
12
notUsed

sqlite> with foo as (select min(totalUsed) - 1 as subtractValue from quotes 
where totalUsed <> 'notUsed') update quotes set totalUsed = totalUsed - (select 
subtractValue from foo) where totalUsed <> 'notUsed';
QUERY PLAN
|--SCAN TABLE quotes
`--SCALAR SUBQUERY
   |--CO-ROUTINE 1
   |  `--SEARCH TABLE quotes
   `--SCAN SUBQUERY 1

sqlite> select * from quotes;
QUERY PLAN
`--SCAN TABLE quotes
totalUsed
1
notUsed
5
10
notUsed


-Original Message-
From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of Richard Damon
Sent: Friday, January 04, 2019 6:43 AM
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] Conditional lowering of value

On 1/4/19 6:30 AM, Cecil Westerhof wrote:
> I have the following query:
> SELECT MIN(totalUsed) - 1
> FROM   quotes
> WHERE  totalUsed <> 'notUsed'
>
> What I want is that when the SELECT returns a positive value every record
> where totalUsed <> 'notUsed' is lowered with the returned value. Is easy to
> accomplish in programming code, but I was wondering if this could be done
> with a SQL statement.
>
Look at the UPDATE query:

UPDATE quotes SET totalUsed = totalUsed - 1 WHERE totalUsed <> 'notUsed';


I will note that this database is obviously using SQLite's variation
from SQL of mixed type columns, as it appears that totalUsed is likely a
column that is normally numeric, but sometimes a string.

-- 
Richard Damon

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

Re: [sqlite] Conditional lowering of value

2019-01-04 Thread Richard Damon 
On 1/4/19 6:30 AM, Cecil Westerhof wrote:
> I have the following query:
> SELECT MIN(totalUsed) - 1
> FROM   quotes
> WHERE  totalUsed <> 'notUsed'
>
> What I want is that when the SELECT returns a positive value every record
> where totalUsed <> 'notUsed' is lowered with the returned value. Is easy to
> accomplish in programming code, but I was wondering if this could be done
> with a SQL statement.
>
Look at the UPDATE query:

UPDATE quotes SET totalUsed = totalUsed - 1 WHERE totalUsed <> 'notUsed';


I will note that this database is obviously using SQLite's variation
from SQL of mixed type columns, as it appears that totalUsed is likely a
column that is normally numeric, but sometimes a string.

-- 
Richard Damon

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

[sqlite] Conditional lowering of value

2019-01-04 Thread Cecil Westerhof 
I have the following query:
SELECT MIN(totalUsed) - 1
FROM   quotes
WHERE  totalUsed <> 'notUsed'

What I want is that when the SELECT returns a positive value every record
where totalUsed <> 'notUsed' is lowered with the returned value. Is easy to
accomplish in programming code, but I was wondering if this could be done
with a SQL statement.

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