Update multiple rows in sqlite3 with same ID

Question

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!

Delete existing rows, insert the new ones. And why is this tagged mysql? — Shawn
– Shawn, Commented Jun 11, 2021 at 8:15

forpas · Accepted Answer · 2021-06-11 13:56:59Z

1

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()

answered Jun 11, 2021 at 13:56

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Scrappy Coco Over a year ago

If there is 1 to 1 relationship wil it easier to update the table?

forpas Over a year ago

@ScrappyCoco not easier. You could do it with an UPDATE statement and a CASE expression which would check each of the current rows.

Collectives™ on Stack Overflow

Update multiple rows in sqlite3 with same ID

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related