Databases
StackOverflow2013
Postsdbo
POHow do I update multiple records in a table with a single MySQL query?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow do I update multiple records in a table with a single MySQL query?
    primarykey
    data
    text
    <p>I would like to update multiple records in a MySQL table using a single query. Basically, this is a tasks table, which has assignments for different people on different dates. When these assignments are changed and submitted via the Online form there is a lot of POST data that gets submitted (pretty much all the pending assignments). I've written an algorithm that sorts through all this information and gets what I want out of it, but I'm stuck on writing the query to update the MySQL table:</p> <pre><code> // Find the modified records and save their information $update = 0; for ( $n = 0; $n &lt; $total_records; $n++ ) { if ( $_POST['update'.$n] == true ) { $updates_arr[$update] = array( intval($_POST['user_id'.$n]), intval($_POST['task'.$n]), $_POST['date'.$n] ); $update++; } } if ( $mysql_db = OpenDatabase() ) { $query = "UPDATE tasks_tbl"; if ( $updates_arr[0] ) { $query .= " SET task = ".$updates_arr[0][1]." WHERE user_id = ".$updates_arr[0][0]." AND date = ".$updates_arr[0][2]; } for ( $n = 1; $n &lt; $updates; $n++ ) { $query .= ", SET task = ".$updates_arr[$n][1]." WHERE user_id = ".$updates_arr[$n][0]." AND date = ".$updates_arr[$n][2]; } $result = mysql_query( $query, $mysql_db ); if ( $result ) { $page .= "&lt;p&gt;Success!&lt;/p&gt;\n\n"; } else { $page .= "&lt;p&gt;Error: ".mysql_error()."&lt;/p&gt;\n\n"; } } </code></pre> <p>This is the query that is generated:</p> <pre><code>UPDATE tasks_tbl SET task = 1 WHERE user_id = 16 AND date = 2010-05-05, SET task = 1 WHERE user_id = 17 AND date = 2222-02-22 </code></pre> <p>Any suggestions would be appreciated. Thanks.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USOMG Ponies
    1. USJim Fell
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COFor really large numbers of inserts, it may be beneficial to do a bulk INSERT into a temporary table, and then UPDATE the target table using values from the temporary table.
      singulars
      1. POHow do I update multiple records in a table with a single MySQL query?
      1. USFrank Farmer
    2. COwhy would you want to do it this way? you run the risk of not updating multiple items when only one of them fails. wouldn't it be better to trap individual transactions for failures so they can be displayed to the user?
      singulars
      1. POHow do I update multiple records in a table with a single MySQL query?
      1. USJeremy S
    3. COThere is more to the algorithm than I have shown here. All data is validated, prior to running the query, so there should never be an instance where invalid data could case the query to fail.
      singulars
      1. POHow do I update multiple records in a table with a single MySQL query?
      1. USJim Fell
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload