Update a Table using a Python Dictionary keys and values for where Clause

Question

I wish to update a SQL Server table. Its a big table (with multiple items to be updated at one shot) and I have an issue in updating that by putting where statements manually. So I created a Python dictionary to use the key value pairs as set value for the value (if it exists) for where condition from the key. If the dictionary key is blank or null it sets the key as value or else it sets the value. (just to add both values are expected to be in the same column which I want to update).

So the problem in concrete is a follows:

Say I have created a dictionary as follows:

d = {'a': '', 'b':'x1', 'c': 'y1', 'd':'', 'e':'13f', 'f':'o'}

So say I have a table like SAMPLETABLE and I want to update a column SAMPLECOL of the table as follows:

  UPDATE SAMPLE TABLE 
  SET SAMPLECOL = 'a'
  WHERE SAMPLECOL = 'a'

  SET SAMPLECOL = 'x1'
  WHERE SAMPLECOL = 'b'

  SET SAMPLECOL = 'y1'
  WHERE SAMPLECOL = 'c'

  SET SAMPLECOL = 'd'
  WHERE SAMPLECOL = 'd'

  SET SAMPLECOL = '13f'
  WHERE SAMPLECOL = 'e'

  SET SAMPLECOL = 'o'
  WHERE SAMPLECOL = 'f'

So I tried in Python like this:

 for key, values in d.items():
 
    if len(d[key]) > 1:
      sql = """UPDATE SAMPLETABLE 
      SET SAMPLECOL = '{}' where id 
       ='{}'""".format(d[key],key)
    else:
        d[key] = key
        sql = """UPDATE SAMPLETABLE 
       SET SAMPLECOL = '{}' where id = '{}'""".format(d[key], 
       key)

But its not working as a single update statement what I want. Not sure where I am going wrong. Any help appreciated.

Answer 1

Python process:

def main():
    full_sql = """
    UPDATE SAMPLETABLE SET SAMPLECOL = 
    CASE 
    {}
    ELSE SAMPLECOL
    END;
    """
    d = {'a': '', 'b': 'x1', 'c': 'y1', 'd': '', 'e': '13f', 'f': 'o'}
    concat_list = []
    for key, values in d.items():
        if values:
            sql = f'WHEN SAMPLECOL = "{key}" THEN "{values}"'
        else:
            sql = f'WHEN SAMPLECOL = "{key}" THEN "{key}"'
        concat_list.append(sql)
    full_sql = full_sql.format(' '.join(concat_list))
    print(full_sql)

Hope it helps you