CODEWITHSUNDEEP
pythonsqlsqlite
Ralk
Ralk

Reputation: 461

Column comparison SQL and Python

I need to compare two columns from a SQLlite DataBase (.db) and if I found a coincidence modify a third column. I.e. :

 A B C 
-------
11 1  F
21 3  F
54 21 V
65 4  F
96 9  F
82 5  F
71 11 V
53 5  F

If any number of the column "B" is found in the column "A" the result in the column "C" will be "V".

Column "C" will be set to F for all rows by default before start the comparison. I'm using Python 2.7 and SQLlite 3 but I really don't know how to do this kind of comparison.

Upvotes: 0

Views: 1188

Answers (2)

Steven Rumbalski
Steven Rumbalski

Reputation: 45542

UPDATE mytable 
    SET C = 'V' 
WHERE B IN (SELECT A FROM mytable)

Here is a complete Python program to show that it works:

# from __future__ import print_function # uncomment import for Python 2.7
import sqlite3

connection = sqlite3.connect(':memory:')
cursor = connection.cursor()
cursor.execute('CREATE TABLE mytable (A INTEGER, B INTEGER, C TEXT)')

initial_AB_vals = [(11, 1), (21, 3), (54, 21), (65, 4), (96, 9), (82, 5), (71, 11), (53, 5)]
cursor.executemany("INSERT INTO mytable(A, B, C) VALUES (?, ?, 'F')", initial_AB_vals)

print('BEFORE UPDATE')
for row in cursor.execute('SELECT * FROM mytable'):
    print('    {:2d} {:2d} {}'.format(*row))

cursor.execute("UPDATE mytable SET C = 'V' WHERE B IN (SELECT A FROM mytable)") 

print()
print('AFTER UPDATE')
for row in cursor.execute('SELECT * FROM mytable'):
    print('    {:2d} {:2d} {}'.format(*row)) 

connection.commit()
connection.close()

Output

BEFORE UPDATE
    11  1 F
    21  3 F
    54 21 F
    65  4 F
    96  9 F
    82  5 F
    71 11 F
    53  5 F

AFTER UPDATE
    11  1 F
    21  3 F
    54 21 V
    65  4 F
    96  9 F
    82  5 F
    71 11 V
    53  5 F

Upvotes: 1

Esteban P.
Esteban P.

Reputation: 2809

you can update your values with an UPDATE ... WHERE EXISTS like:

 UPDATE mt
    SET mt.C = 'V'
   FROM mytable mt
  WHERE EXISTS (SELECT 1 FROM mytable mt2 WHERE mt.A = mt2.B)
;

Upvotes: 0

Related Questions