MySQL Update syntax is different from Insert syntax.

1
for INSERT, you can group all the (column names), then all the (values) together, WHERE...

for UPDATE SET, you have to do col_name=value, col_name2=val2, etc. WHERE...

both can use WHERE.




2
SELECT * FROM `table_a` WHERE `license_size` = '6'  AND `extref` = 'CA4B8EC17B8F3833E04400144F3A7847';
SELECT DISTINCT `extref`, `id`, `license_size` FROM `table_a`;

SELECT DISTINCT `extref`, `id`, `license_size` FROM `table_a` WHERE `id`='1' AND `license_size`='10';



3
update
UPDATE `table_a` SET `extref`=1234, `id`=4567, `license_size`=123 WHERE `id`='1' AND `license_size`='10';



update back
UPDATE `table_a` SET
`extref`='CA707A913A2404ECE04400144F3A7847', `id`=1, `license_size`=10
WHERE `id`='4567';

won't find and won't work:
UPDATE `table_a` SET `extref`=1234, `id`=4567, `license_size`=123 WHERE `id`='1' AND `license_size`='999';



4 composed by Python script:

UPDATE `table_name_a` SET  extref=%s, license_size=%s, id=%s  WHERE id = '1' AND extref = 'CA707A913A2404ECE04400144F3A7847' AND license_size = '10'
['1234', 999, 4567]

5 the Python script:


def update_into_table(conn, table, dct_cols_values, where):
    # Get dct keys and values into two lists, in the same order
    lst_columns = []
    lst_values = []
    for k in dct_cols_values:
        lst_columns.append(k)
        lst_values.append(dct_cols_values[k])

    # Form the_sql
    str_fragment_begin = "UPDATE `%(table)s` SET " % dict(table=table)
    lst_fragment_set = ["%s=%s" % (col_name,"%s") for col_name in lst_columns]
    str_fragment_set = ", ".join(lst_fragment_set)

    str_where_begin = " WHERE "
    lst_where = []
    for tup in where:
        lst_where.append("%s = '%s'" % tup)
    str_where = " AND ".join(lst_where)
    str_fragment_where = str_where_begin + str_where

    str_full           = "%s %s %s" % (str_fragment_begin, str_fragment_set, str_fragment_where)

    # Construct and close the cursor, return number of rows inserted
    print str_full
    print lst_values

if __name__ == '__main__':
    dct = dict(
        extref='1234',
        id=4567,
        license_size=999,
    )
    where = [
        ('id', 1),
        ('extref', 'CA707A913A2404ECE04400144F3A7847'),
        ('license_size', 10),
    ]
    update_into_table(None, 'table_name_a', dct, where)