How to Bulk Update MySQL Data with One Query

How to bulk update mysql data with one query?

Yes you can do it using this query:

UPDATE a 
SET fruit = (CASE id WHEN 1 THEN 'apple'
                     WHEN 2 THEN 'orange'
                     WHEN 3 THEN 'peach'
             END)
WHERE id IN(1,2 ,3);

Bulk update mysql with where statement

The easiest solution in your case is to use ON DUPLICATE KEY UPDATE construction. It works really fast, and does the job in easy way.

INSERT into `table` (id, fruit)
    VALUES (1, 'apple'), (2, 'orange'), (3, 'peach')
    ON DUPLICATE KEY UPDATE fruit = VALUES(fruit);

or to use CASE construction

UPDATE table
SET column2 = (CASE column1 WHEN 1 THEN 'val1'
                 WHEN 2 THEN 'val2'
                 WHEN 3 THEN 'val3'
         END)
WHERE column1 IN(1, 2 ,3);

Bulk update MySQL table with data concatenated from another table

You can do this in a single query by making use of SUBSTRING_INDEX():

UPDATE posts p
LEFT JOIN postmeta pm
    ON p.ID = pm.post_id
SET p.post_title = CONCAT(SUBSTRING_INDEX(p.post_title, 'appearance', 1), pm.meta_value)
WHERE pm.meta_key = '_place_id'

Taking SUBSTRING_INDEX('John Doe appearance 102', 'appearance', 1) would return everything before the occurrence of appearance, which is John Doe (with a space at the end). Then, we can simply concatenate on the meta_value.

How to bulk update mysql database without having duplicates?

You can UPDATE IGNORE:

UPDATE IGNORE user_relationships 
SET institution_id='{$parent_id}' 
WHERE
    relationship IN ('was_working_at', 'is_working_at', 'is_trained_at') AND
    institution_id = {$child_id};

With the IGNORE modifier, the update statement does not abort even if errors occur during the update. Rows for which duplicate-key conflicts occur on a unique key value are not updated. Rows updated to values that would cause data conversion errors are updated to the closest valid values instead.

But after this update you must decide what should be dun with not updated rows. May be you need to remove them using:

DELETE FROM user_relationships 
WHERE
    relationship IN ('was_working_at', 'is_working_at', 'is_trained_at') AND
    institution_id = {$child_id};

MySQL bulk update a new table based on results of another table

You can try below -

    UPDATE NEWTABLE INNER JOIN
        (
           select max(case when title='HELLO' then text end) as hello,
           max(case when title='TEST' then text end) as test,
           max(case when title not in ('HELLO','TEST') then text end) as other
           from MAINTABLE group by parent_id
       )A ON NEWTABLE.parent_id= A.parent_id
       SET HELLO= A.hello,TEST=A.test,OTHER= A.other

Bulk update MySql with python

I don't think mysqldb has a way of handling multiple UPDATE queries at one time.

But you can use an INSERT query with ON DUPLICATE KEY UPDATE condition at the end.

I written the following example for ease of use and readability.

import MySQLdb

def update_many(data_list=None, mysql_table=None):
    """
    Updates a mysql table with the data provided. If the key is not unique, the
    data will be inserted into the table.

    The dictionaries must have all the same keys due to how the query is built.

    Param:
        data_list (List):
            A list of dictionaries where the keys are the mysql table
            column names, and the values are the update values
        mysql_table (String):
            The mysql table to be updated.
    """

    # Connection and Cursor
    conn = MySQLdb.connect('localhost', 'jeff', 'atwood', 'stackoverflow')
    cur = conn.cursor()

    query = ""
    values = []

    for data_dict in data_list:

        if not query:
            columns = ', '.join('`{0}`'.format(k) for k in data_dict)
            duplicates = ', '.join('{0}=VALUES({0})'.format(k) for k in data_dict)
            place_holders = ', '.join('%s'.format(k) for k in data_dict)
            query = "INSERT INTO {0} ({1}) VALUES ({2})".format(mysql_table, columns, place_holders)
            query = "{0} ON DUPLICATE KEY UPDATE {1}".format(query, duplicates)

        v = data_dict.values()
        values.append(v)

    try:
        cur.executemany(query, values)
    except MySQLdb.Error, e:
        try:
            print"MySQL Error [%d]: %s" % (e.args[0], e.args[1])
        except IndexError:
            print "MySQL Error: %s" % str(e)

        conn.rollback()
        return False

    conn.commit()
    cur.close()
    conn.close()

Explanation of one liners

columns = ', '.join('`{}`'.format(k) for k in data_dict)

is the same as

column_list = []
for k in data_dict:
    column_list.append(k)
columns = ", ".join(columns)

Here's an example of usage

test_data_list = []
test_data_list.append( {'id' : 1, 'name' : 'Marco', 'articles' : 1 } )
test_data_list.append( {'id' : 2, 'name' : 'Keshaw', 'articles' : 8 } )
test_data_list.append( {'id' : 3, 'name' : 'Wes', 'articles' : 0 } )

update_many(data_list=test_data_list, mysql_table='writers')

Query output

INSERT INTO writers (`articles`, `id`, `name`) VALUES (%s, %s, %s) ON DUPLICATE KEY UPDATE articles=VALUES(articles), id=VALUES(id), name=VALUES(name)

Values output

[[1, 1, 'Marco'], [8, 2, 'Keshaw'], [0, 3, 'Wes']]

SQL - Update multiple records in one query

Try either multi-table update syntax

UPDATE config t1 JOIN config t2
    ON t1.config_name = 'name1' AND t2.config_name = 'name2'
   SET t1.config_value = 'value',
       t2.config_value = 'value2';

Here is a SQLFiddle demo

or conditional update

UPDATE config
   SET config_value = CASE config_name 
                      WHEN 'name1' THEN 'value' 
                      WHEN 'name2' THEN 'value2' 
                      ELSE config_value
                      END
 WHERE config_name IN('name1', 'name2');

Here is a SQLFiddle demo

---

Related Topics