CODEWITHSUNDEEP
pythonsqlite
Abijah
Abijah

Reputation: 546

Update a single sqlite row amongst those selected

I have a sqlite table that I want to update. I want to change the entry in Col1 on a single row that has Col2 and Col3 taking some specified value.

It is a link table, where many rows have the same combination of elements in Col2 and Col3. As a result If I run:

c.execute("""UPDATE Table1 SET Col1=(?)  
                WHERE Col2=(?) AND Col3=(?)
                """, (var1, var2, var3,))

it will cause a problem, because it will change every row where Col2 and Col3 take the specified values.

Notably every row of the table has a unique numerical entry in Col4 - so my intuition is that i could just choose the row with the lowest value in Col4 where Col2 and 3 mach their specified values, but after reading the documentation on UPDATE i am no clearer as how to do it.

Upvotes: 1

Views: 64

Answers (2)

forpas
forpas

Reputation: 164089

Since Col4 is unique all you have to do is find the row with the min Col4 where Col2 and Col3 are equal to your parameters and you can do it with:

SELECT MIN(Col4) FROM Table1 WHERE (Col2, Col3) = (?, ?)

So your code should be:

c.execute("""UPDATE Table1
             SET Col1 = ?
             WHERE Col4 = (SELECT MIN(Col4) FROM Table1 WHERE (Col2, Col3) = (?, ?)) 
                """, (var1, var2, var3,))

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521194

You may update using exists logic:

UPDATE Table1
SET Col1 = ?
WHERE Col2 = ? AND Col3 = ? AND
      NOT EXISTS (SELECT 1 FROM Table1 t1
                  WHERE t1.Col2 = Table1.Col2 AND t1.Col3 = Table1.Col3 AND
                        t1.Col4 < Table1.Col4);

The exists clause above asserts that for any matching record we cannot find another record with the same Col2 and Col3 values but a smaller Col4 value.

Upvotes: 1

Related Questions