How to update multiple rows in the same table of MySQL with PHP?

Question

If only one row with a distinct field is to be updated,I can use:

insert into tab(..) value(..) on duplicate key update ...

But now it's not the case,I need to update 4 rows inside the same table,which have its field "accountId" equal to $_SESSION['accountId'].

What I can get out of my mind at the moment is:

delete from tab where accountId = $_SESSION['accountId'],

then insert the new rows.

Which obviously is not the best solution.

Has someone a better idea about this?

Answer 1

Use the update just like that!

update tab set col1 = 'value' where accountId = $_SESSION['accountId']

Moreover, MySQL allows you to do an update with a join, if that makes your life a bit easier:

update
    tab t
    inner join accounts a on
        t.accountid = a.accountid
set
    t.col1 = 'value'
where
    a.accountname = 'Tom'

Answer 2

Based on your question, it seems like you should review the Update Statement.

Insert is used to put new rows in - not update them. Delete is used to remove. And Update is used to modify existing rows. Using "Insert On Duplicate Key Update" is a hackish way to modify rows, and is poor form to use when you know the row is already there.

Answer 3

1. load all of the values in to a temporary table.
2. UPDATE all of the values using a JOIN.
3. INSERT all of the values from the temp table that don't exist in the target table.

Answer 4

You can use replace statement. This will work as a DELETE followed by INSERT