Conditional lowering of value

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Conditional lowering of value

Cecil Westerhof-5
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Conditional lowering of value

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Conditional lowering of value

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:[hidden email]] On Behalf Of Richard Damon
Sent: Friday, January 04, 2019 6:43 AM
To: [hidden email]
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Conditional lowering of value

Keith Medcalf
In reply to this post by Cecil Westerhof-5

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users