Update SQL Database based on matched ID in Dataframe

user_11942282 0
Viewed 399

I have the dataframe below with the respective values and would like to update my SQL Database Server if the ID matches with my dataframe

df dataframe

ID VALUE
123 9
456 11

SQL Database Server, table1

ID VALUE
456 62
623 41
123 3
563 67

After updating, I want my SQL Database Server to look like this where you'll notice that ID 123 & 456 has been given a new value based on my dataframe.

ID VALUE
456 11
623 41
123 9
563 67

Anyone knows how I could utilise this in my query when executing?

query = DELETE/UPDATE table table1 where ID = ID IN DATAFRAME

conn.execute(query)
dataframe sql-server sql python
2 Answers

You can create a parameter list(df_list) along with a DML statement, and arrange the order of columns due to the appearance within the statement. In this case those two arguments(id and value) should be reversely ordered such as

cur=con.cursor()
sql = "UPDATE [table1] SET [value] = ? WHERE [id] = ?"
cols = df.columns.tolist()
df_list = df[cols[-1:] + cols[:-1]].values.tolist()                                                         
cur.executemany(sql,df_list)
cur.close()
con.commit()
con.close()
user_58413060

You can simply make corelated query as follows:

update table1 t1
   set t1.value = (select df.value from df where df.id = t1.id)
where exists (select 1 from df where df.id = t1.id);

OR use Inner join in update as follows:

UPDATE T
SET T.value = d.value -- , another column updates here 
FROM table1 as t
INNER JOIN df as d ON t.id = d.id;
user_115656290
Related