Update Multiple Records in Sql

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

UPDATE multiple rows with different values in one query in MySQL

You can do it this way:

UPDATE table_users
    SET cod_user = (case when user_role = 'student' then '622057'
                         when user_role = 'assistant' then '2913659'
                         when user_role = 'admin' then '6160230'
                    end),
        date = '12082014'
    WHERE user_role in ('student', 'assistant', 'admin') AND
          cod_office = '17389551';

I don't understand your date format. Dates should be stored in the database using native date and time types.

Update multiple records in SQL

You can simply combine an update with a case statement such

UPDATE records
   SET name =
     CASE
       WHEN id = 3 THEN 'abc'
       WHEN id = 1 THEN 'def'
       ELSE name
     END

SQL update multiple rows with different values where they match a value from a list

You could use a case expression:

update mytable
set email = case email
    when 'OldEmail@1.com' then 'NewEmail@1.com'
    when 'OldEmail@2.com' then 'NewEmail@2.com'
end
where email in ('OldEmail@1.com','OldEmail@2.com')

Or better yet, if you have a large list of values, you might create a table to store them (like myref(old_email, new_email)) and join it in your update query, like so:

update t
set t.email = r.new_email
from mytable t
inner join myref r on r.old_email = t.email

The actual syntax for update/join does vary accross databases - the above SQL Server syntax.

MySQL how to Update multiple rows with conditions in the same table

Let's examine the expressions in the then part of your statement. e.g.: users.accounting_id = '102'. This is not an assignment, but a boolean expression, which evaluates to false, since accounting_id is not 102 (it's 102215 in this case).
The = before the case is used for the assignment. In the then branches you just need to return the value to be assigned:

UPDATE users
SET users.accounting_id = CASE
        WHEN users.user_id = '102215' THEN '102'
        WHEN users.user_id = '102144' THEN '193'
    END    
WHERE users.user_id IN ('102215','102144');

How to update multiple rows within the same table in one SQL statement?

A simple case expression can work here easily enough.

update YourTable
set DefaultforAxisID = case CategoryID when 21 then 2 when 22 then NULL else DefaultforAxisID end 
where CategoryID in (21, 22)

How to update multiple records at once with subquery in SQL

Your subquery needs to tie in to the outer query.

update a
set companyname = (select facilityname f from facility WHERE f.facilityid = a.facilityid)
FROM accountinginfo a
where a.facilityid in (12345,12346,12347)

---

Related Topics