CODEWITHSUNDEEP
sqlite
yaylitzis
yaylitzis

Reputation: 5544

Update a column after a check

I have a table with columns product, version. I added a new column valid which is BOOLEAN and what I want is to add TRUE to the product with the maximum version and FALSE to the rest. For example:

Before

product1 - 1
product1 - 2
product1 - 3

After

product1 - 1 - 0
product1 - 2 - 0 
product1 - 3 - 1

I can do this with prepared statements with java but I would prefer add the column from the SQLite console. Can I do this with SQLite?

Upvotes: 1

Views: 42

Answers (1)

CL.
CL.

Reputation: 180182

You want to get 1 in exactly those rows where no other row with the same product and a larger version exists:

UPDATE MyLittleTable
SET valid = NOT EXISTS (SELECT *
                        FROM MyLittleTable AS T2
                        WHERE T2.product = MyLittleTable.product
                          AND T2.version > MyLittleTable.version);

Upvotes: 1

Related Questions