CODEWITHSUNDEEP
sqlsqlite
phreshsprout
phreshsprout

Reputation: 59

SQLite - How to pass a field value to a WHERE clause

My code snippet:

UPDATE "LookUpWrkflwTemp" SET SpiralUniverse = (
SELECT Count (*)
FROM SpiralUniverse 
WHERE Date = '2015-11-16') WHERE Date = '2015-11-16';

My goal is to find a Date value where the other fields in the row are null and pass that value to the above WHERE clause.

Sample data:

"SpiralUniverse"
rowid    Date       Symbol
1       2015-11-16   ACOR
2       2015-11-16   AAPL
3       2015-11-16   TSLA
4       2015-11-10   TSLA

"LookUpWrkflwTemp"
rowid    Date       SpiralUniverse
1       2015-11-10     1
2       2015-11-16     NULL

After UPDATE the result should be:

"LookUpWrkflwTemp"
rowid    Date       SpiralUniverse
1       2015-11-10     1
2       2015-11-16     3

There will only be one record in the above table where there is a date value and the other field values are null.

Upvotes: 1

Views: 40

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

I think you just need a correlated subquery:

UPDATE LookUpWrkflwTemp
    SET SpiralUniverse = (SELECT Count(*)
                          FROM SpiralUniverse su
                          WHERE su.Date = LookUpWrkflwTemp.Date
                         )
    WHERE SprialUniverse IS NULL;

Upvotes: 1

Related Questions