CODEWITHSUNDEEP
mysqlsql-update
Raky
Raky

Reputation: 892

MySQL Update multiple records with One Update Statement

I have a dynamically generated UPDATE Statements from which looks like this:

UPDATE `maildespatch` SET `RL_TktNumber`= Null ,`SxRemote_IP`= '::1' ,`SxRemotename`= 'Raks' WHERE `Table_ID` = 1 , 
UPDATE `maildespatch` SET `RL_TktNumber`= Null ,`SxRemote_IP`= '::1' ,`SxRemotename`= 'Raks' WHERE `Table_ID` = 2 , 
UPDATE `maildespatch` SET `RL_TktNumber`= Null ,`SxRemote_IP`= '::1' ,`SxRemotename`= 'Raks' WHERE `Table_ID` = 5 , 
UPDATE `maildespatch` SET `RL_TktNumber`= Null,`SxRemote_IP`= '::1' ,`SxRemotename`= 'Raks' WHERE `Table_ID` = '68'

The data comes from the user-input on HTML Table Fields based on which these statements are generated dynamically through ajax() call. Is there a way to save all this data to be saved/Updated in MySQL more generically? I do not want to create a temporary MySQL Table to save data and then from update the Main Table.

Upvotes: 0

Views: 1142

Answers (3)

Mindaugas Bernatavičius
Mindaugas Bernatavičius

Reputation: 3909

A more generic approach would be calling a stored procedure (a.k.a user defined function - UDF) in mysql and passing the necessary parameters.

Stored procedures are reusable piece of code on your database (like functions in your backend code) that can encapsulate a piece of logic. Take a look at MYSQL documentation for stored procedures here.

Using them, you can be as generic as you want - for example have defaults for values that are passed most often (like your SxRemote_IP = '::1' ,SxRemotename = 'Raks')so you don't have to send them over the network and omit them in your front end code.

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520898

You could use a CASE expression:

UPDATE maildespatch
SET RL_TktNumber = CASE WHEN Table_ID = '1' THEN '11'
                        WHEN Table_ID = '2' THEN NULL
                        WHEN Table_ID = '5' THEN '33'
                        WHEN Table_ID = '68' THEN NULL END,
    SxRemote_IP = '::1',
    SxRemotename = 'Raks'
WHERE
    Table_ID = IN('1', '2', '5', '68');

Note that if Table_ID be an integer column, then rightfully you should be comparing against integers, e.g. the WHERE clause should look like this:

WHERE Table_ID = IN(1, 2, 5, 68)

Upvotes: 0

slon
slon

Reputation: 1030

You can run those 4 queries as one like below:

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

    $sql = "UPDATE `maildespatch` SET `RL_TktNumber`= '11',`SxRemote_IP`= '::1' ,`SxRemotename`= 'Raks'  WHERE `Table_ID` = '1'; 
            UPDATE `maildespatch` SET `RL_TktNumber`= 'Null',`SxRemote_IP`= '::1' ,`SxRemotename`= 'Raks'  WHERE `Table_ID` = '2'; 
            UPDATE `maildespatch` SET `RL_TktNumber`= '33',`SxRemote_IP`= '::1' ,`SxRemotename`= 'Raks'  WHERE `Table_ID` = '5';
            UPDATE `maildespatch` SET `RL_TktNumber`= 'Null',`SxRemote_IP`= '::1' ,`SxRemotename`= 'Raks'  WHERE `Table_ID` = '68'";

if ($conn->query($sql) === TRUE) {
    echo "Record updated successfully";
} else {
    echo "Error updating record: " . $conn->error;
}

$conn->close();
?>

or you can do loop if you are passing data as array:

foreach ($alldata as $data)
{
    //your queries will go here
}

Upvotes: 1

Related Questions