CODEWITHSUNDEEP
phpmysqlsqlsql-insert
user756659
user756659

Reputation: 3512

update (or insert) multiple rows in one query

I am trying to update multiple rows in a table at once (or insert them if they do not exist) given settings and an array of user id's.

An example of how I select all rows from the settings table for all users of specific computers of a specific account. This is the result set which I need to insert values in or update if they exist.

$stmt = $db->prepare("      
    SELECT settings.*
    FROM
        ( SELECT  account_id, computer_id
            FROM computers
            ORDER BY computer_id ASC LIMIT 0, ".$_SESSION['user']['licenses']."
        ) as c
        LEFT JOIN users
            on users.computer_id = c.computer_id
        LEFT JOIN accounts
            on accounts.account_id = c.account_id
        LEFT JOIN settings
            on settings.user_id = users.user_id
    WHERE accounts.account_id = ".$_SESSION['user']['account_id']."
");

What I am trying to do : I am trying to update/insert three columns (enabled, status, and user_id) in the settings table for only those user ids listed in the array. Enabled and status values will be the same for all, but the user_id will be different for each.

$users = array(12, 36, 43, 56, 76)

$binding = array( 'enabled' => 1, 'status' => 2, 'user_id' => from the array );

If my thought process is correct I can create a virtual table from my statement listed above then use ON DUPLICATE KEY UPDATE to insert/update on the results of that virtual table?

Is this possible? If so, example, tips, or point in the right direction? The way I have working involves a foreach loop for each user id in the given array so there will be x number of queries depending on its count. If I can cut that to only one that would be great!

UPDATE:

Ok, I am totally confused now... this is an attempt for defined values which is not working... and I still need a way to do so for every user_id in my array as mentioned above. Don't I need to 'save' my entire FROM clause AS a new reference as well?

$stmt = $db->prepare("
    INSERT INTO settings (user_id, enabled, status)
    VALUES (:user_id, :enabled, :alert_user)    
    SELECT user_id, enabled, status
    FROM
        ( SELECT  account_id, computer_id
            FROM computers
            ORDER BY computer_id ASC LIMIT 0, ".$_SESSION['user']['licenses']."
        ) as c
        LEFT JOIN users
            on users.computer_id = c.computer_id
        LEFT JOIN accounts
            on accounts.account_id = c.account_id
        LEFT JOIN settings
            on settings.user_id = users.user_id
        WHERE accounts.account_id = ".$_SESSION['user']['account_id']."
    ON DUPLICATE KEY UPDATE enabled = VALUES(enabled), status = VALUES(status)  
");

$binding = array(
    'enabled' => 1,
    'alert_user' => 4,
    'user_id' => 6
    );

$stmt->execute($binding);

Upvotes: 0

Views: 241

Answers (1)

Barmar
Barmar

Reputation: 780879

Yes, your thinking is correct. It should be something like this:

INSERT INTO settings (user_id, enabled, status)
SELECT user_id, enabled, status
FROM ... -- rest of your query here
ON DUPLICATE KEY UPDATE enabled = VALUES(enabled), status = VALUES(status)

VALUES(colname) in the ON DUPLICATE KEY UPDATE clause gets the value that would have been inserted if there hadn't been a dupliate.

OK, based on your comment, I think this may be what you want:

INSERT INTO settings (user_id, enabled, status)
SELECT :user_id, :enabled, :status
FROM ...
JOIN ...
JOIN ...
WHERE ...
ON DUPLICATE KEY UPDATE enabled = VALUES(enabled), status = VALUES(status)

If the join returns any rows, this will insert or update the specified row. If it doesn't find any rows, no insert/update will be done.

You should probably be using inner joins rather than left joins, if you don't want anything returned when there are no matches in the tables being joined with.

Upvotes: 1

Related Questions