CODEWITHSUNDEEP
pythonsqldatabasesqlite
Scrappy Coco
Scrappy Coco

Reputation: 116

Update multiple rows in sqlite3 with same ID

I want to update cum replace multiple rows of a table having the same ID. The raw_table look like this:

mem_id       + brand         +      type
A              xyz                  123
A              qwe                  423
A              qwe                  210
A              zxc                  145
B              xyz                  789
B              qwe                  564
C              qwe                  124
C              zxc                  326

and I want to update cum replace all the rows of spcific mem_id 'A'- with the values of a list of tuples which look like this:

lists = [('A', 'asd', '435'), ('A', 'wky', '420'), ('A', 'jko', '311')]

i have implemented the below code but it throws me a sqlite3 operational error:

conn = sqlite3.connect('database/u_data.vita')
mycursor = conn.cursor()
mycursor.executemany("UPDATE raw_table SET(brand=?, type=?) WHERE mem_id=?",lists,A)
conn.commit()
conn.close()

i want the output to be look like this:

mem_id       + brand         +      type
A              asd                  435
A              wky                  420
A              jko                  311
B              xyz                  789
B              qwe                  564
C              qwe                  124
C              zxc                  326

Thanks. Any insight will be helpful!

Upvotes: 1

Views: 625

Answers (1)

forpas
forpas

Reputation: 164089

Since there is not a 1 to 1 relationship between the existing rows of the table and the new rows, you don't need to update the table, but delete the existing rows with mem_id = 'A' and insert the new rows from the list:

lists = [('A', 'asd', '435'), ('A', 'wky', '420'), ('A', 'jko', '311')]
conn = sqlite3.connect('database/u_data.vita')
mycursor = conn.cursor()
mycursor.execute("DELETE FROM raw_table WHERE mem_id = 'A'")
mycursor.executemany("INSERT INTO raw_table(mem_id, brand, type) VALUES (?, ?, ?)", lists)
conn.commit()
conn.close()

Upvotes: 1

Related Questions