Yes, that's possible - you can use INSERT ... ON DUPLICATE KEY UPDATE.

Using your example:

INSERT INTO table (id,Col1,Col2) VALUES (1,1,1),(2,2,3),(3,9,3),(4,10,12)
ON DUPLICATE KEY UPDATE Col1=VALUES(Col1),Col2=VALUES(Col2);

UPDATE mytable SET
    fruit = CASE WHEN id=1 THEN 'orange' ELSE 'strawberry' END,
    drink = CASE WHEN id=1 THEN 'water'  ELSE 'wine'       END,
    food  = CASE WHEN id=1 THEN 'pizza'  ELSE 'fish'       END
WHERE id IN (1,2);

Personally, using CASE WHEN THEN END looks clumsy.

You could code this using the IF function.

UPDATE mytable SET
    fruit = IF(id=1,'orange','strawberry'),
    drink = IF(id=1,'water','wine'),
    food  = IF(id=1,'pizza','fish')
WHERE id IN (1,2);

Give it a Try !!!

CAVEAT : CASE WHEN THEN END is only handy when dealing with multiple values (more than 2)

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.

Remarks

It is possible to update rows based on some condition. It is also possible to update multiple tables in one statement in MySQL.

Whether the latter is a good idea is debatable, though. The target tables would be joined together for the update, and when I say "joined", I mean it in a broader sense: you do not have to specify a joining condition, in which case theirs would be a cross join. In a cross join, when at least one of the tables has more than one row, the other table will inevitably have its rows duplicated in the joined set. If both have multiple rows, both will have them multiplied. Somewhat counter-intuitively, MySQL will still update each affected row just once, yet I would refrain from multi-table updates in such scenarios, even if solely because of the counter-intuitiveness.

Method 1

Anyway, moving on to your specific example, there is indeed no joining condition, only a filter on each table. You can specify those filters in the WHERE clause of the UPDATE. Now in order to select which value to update each column with, you can use a CASE expression. This is what the complete UPDATE statement might look like:

UPDATE
  A, B
SET
  A.col1 = 'abc',
  A.col2 = 'xyz',
  B.col1 = CASE B.col3
             WHEN '1' THEN 'a'
             WHEN '2' THEN 'b'
             WHEN '3' THEN 'c'
           END,
  B.col2 = CASE B.col3
             WHEN '1' THEN 'x'
             WHEN '2' THEN 'y'
             WHEN '3' THEN 'z'
           END
WHERE
  A.col3 = '1'
  AND B.col3 IN ('1', '2', '3')
;

You can see that you have to repeat the same set of conditions in a CASE expression both for B.col1 and for B.col2. Is there a way to avoid that?

Method 2

Yes, there is. You can arrange the target values for B.col1 and B.col2 as well as the filtering values for B.col3 as a derived table and join it to B in the UPDATE clause, like this:

UPDATE
  A,
  B
    INNER JOIN
    (
      SELECT 'a' AS col1, 'x' AS col2, '1' AS col3
      UNION ALL
      SELECT 'b', 'y', '2'
      UNION ALL
      SELECT 'c', 'z', '3'
    ) AS fltr ON B.col3 = fltr.col3
SET
  A.col1 = 'abc',
  A.col2 = 'xyz',
  B.col1 = fltr.col1,
  B.col2 = fltr.col2
WHERE
  A.col3 = '1'
;

The join is also acting as a filter for B, so you can omit the one in the WHERE clause.

You can find a demo for each method at db<>fiddle:

• Method 1
• Method 2

Better way

Finally, as have been remarked both at the beginning of this post and in the comments, you can have a separate UPDATE statement for each table. The result would be clear in intention both to the reader of your script and to the database engine. A simpler script enables the latter to have more options for optimisation.

Use either of the methods above for the table B update, but do both tables separately:

UPDATE
  A
SET
  A.col1 = 'abc',
  A.col2 = 'xyz'
WHERE
  A.col3 = '1'
;

UPDATE
  B
    INNER JOIN
    (
      SELECT 'a' AS col1, 'x' AS col2, '1' AS col3
      UNION ALL
      SELECT 'b', 'y', '2'
      UNION ALL
      SELECT 'c', 'z', '3'
    ) AS fltr ON B.col3 = fltr.col3
SET
  B.col1 = fltr.col1,
  B.col2 = fltr.col2
;

There is also another reason for splitting the updates in separate statements. Since for a single UPDATE statement the tables need to be joined, it is important that both tables have rows intended for the update. If one table has no matching rows, then, even if the other does, neither will be updated. This is because an empty set cross-joined to a non-empty set still results in an empty set.

So, the single UPDATE statement would have no rows to work with if at least one table had no rows matching the condition(s). That would not happen with separate UPDATEs, because each would work with its own table regardless of the contents of the other, therefore, absence of rows in one table would not affect the update of the other.

Finally i got it, after several tries i manage to get what i needed, so i'll share my answer.

First i made one procedure to update one single row in the column:

delimiter //
create procedure update_amount_products (in id int)
begin
update categories set products_amount= 
           (SELECT DISTINCT COUNT( products_to_categories.products_id ) 
FROM categories_description
INNER JOIN products_to_categories ON products_to_categories.categories_id = categories_description.categories_id
INNER JOIN products_description ON products_description.products_id = products_to_categories.products_id
WHERE categories_description.categories_id =id)
WHERE categories_id = id;
end
//

DELIMITER ;

Then i realized i could handle the whole column by calling the procedure with another procedure with a loop on it

DELIMITER $$
CREATE PROCEDURE update_products_amount()

   BEGIN
      DECLARE a INT Default 1 ;
      DECLARE category_length int;

      select max(categories_id) into category_length from categories_description;

      simple_loop: LOOP
         SET a=a+1;

         call update_amount_products(a);

         IF a >= category_length THEN
            LEAVE simple_loop;
         END IF;
   END LOOP simple_loop;
END $$
DELIMITER ;

commit;

Finally, all i have to do now, is to call my last procedure and voilá, all my column will be automatically updated.

call update_products_amount();

First here is sample data

mysql> drop table if exists mytable;
Query OK, 0 rows affected (0.03 sec)

mysql> create table mytable
    -> (
    ->     id int not null,
    ->     value VARCHAR(255),
    ->     primary key (id)
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql> insert into mytable (id) values (1),(2),(3);
Query OK, 3 rows affected (0.06 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from mytable;
+----+-------+
| id | value |
+----+-------+
|  1 | NULL  |
|  2 | NULL  |
|  3 | NULL  |
+----+-------+
3 rows in set (0.00 sec)

mysql>

Here is the new query

update mytable A inner join
(
    SELECT 1 id,'xyz' value UNION
    SELECT 2   ,'abc'       UNION
    SELECT 3   ,'def'

) B USING (id)
SET A.value = B.value;

Here is the new query executed

mysql> update mytable A inner join
    -> (
    ->     SELECT 1 id,'xyz' value UNION
    ->     SELECT 2   ,'abc'       UNION
    ->     SELECT 3   ,'def'
    -> ) B USING (id)
    -> SET A.value = B.value;
Query OK, 0 rows affected (0.06 sec)
Rows matched: 3  Changed: 0  Warnings: 0

mysql> select * from mytable;
+----+-------+
| id | value |
+----+-------+
|  1 | xyz   |
|  2 | abc   |
|  3 | def   |
+----+-------+
3 rows in set (0.00 sec)

mysql>

I would suggest using join:

UPDATE items i JOIN
       (SELECT 1 as id, 'name1' as name, 1 as members_only, 20 as price UNION ALL
        . . .
       ) newdata
       USING (id)
    SET i.name = newdata.name2,
        i.members_only = newdata.members_only,
        i.price = newdata.price;

Alternatively, run separate updates for each id within the same transaction, so they all take effect at the same time.