MySQL update rows if each columns equals the same value

mysql update column value for all rows
mysql update multiple rows from select
mysql update multiple columns
how to use select and update together in mysql
sql update column with value from another table
mysql update multiple columns from select
mysql update one table from another table multiple columns
mysql update multiple tables

I have a table like this :

----------------------------------------------------------
|                Actions                                 |
----------------------------------------------------------
| action_id | user_id | action_active | action_cancelled |
----------------------------------------------------------
|     1     |    1    |       0       |         0        |
----------------------------------------------------------
|     2     |    2    |       1       |         0        |
----------------------------------------------------------
|     3     |    1    |       0       |         0        |
----------------------------------------------------------
|     4     |    2    |       0       |         0        | 
----------------------------------------------------------

I want to update all action_cancelled to 1 for a user_id, if a user has each of his rows as action_active = 0. If he has at least one action_active as 1, don't update.

In this example, it should update action_cancelled to 1 for the row 1 and 3, because the user 1 has each of his action_active at 0.

I thought about something like this, but I'm not sure about how right it is :

UPDATE actions 
SET a.active_cancelled = 1 
WHERE (SELECT p.user_id, SUM(p.action_active) as sum_p FROM actions p GROUP BY user_id WHERE a.user_id = p.user_id AND sum_p = 0);

I'm getting stuck there in the WHERE. How can I say "if a user has all of his rows as action_active = 0, update them"

Would love some help !

You can use HAVING and JOIN to do it

    UPDATE actions a
    JOIN 
   (
    SELECT p.user_id, SUM(p.action_active) as sum_p 
     FROM actions p 
     GROUP BY user_id
     HAVING sum_p = 0)
   b ON a.user_id=b.user_id
    SET a.action_cancelled = 1 

How to Update a Column Based on a Filter of Another , This tutorial will cover ways to update rows, including full and conditional If every field needs to be updated to the same value, you can do that using a simple  As MySQL doesn’t have inherent support for updating more than one rows or records with a single update query as it does for insert query, in a situation which needs us to perform updating to tens of thousands or even millions of records, one update query for each row seems

You can use not exists :

update t
     set t.active_cancelled = 1 
where not exists (select 1 from t t1 where t1.user_id = t.user_id and t1.action_active = 1);

MySQL 8.0 Reference Manual :: 13.2.13 UPDATE Statement, The SET clause indicates which columns to modify and the values they Each matching row is updated once, even if it matches the conditions multiple times. You cannot update a table and select directly from the same table in a subquery. I'm trying to add rows to a column, keeping the order of the newest column set to one, and all other rows counting up from there. In this case, I add a new row with order=0, then use this query to update all the rows by one. "UPDATE favorits SET order = order+1" However, what happens is that all the rows are updated to the same value.

One possible solutions is,

update actions
inner join(
    select user_id,sum(if(action_active=1,1,0))as inactive from actions group by 
    user_id having inactive=0
)as B on B.user_id=temp.user_id
set actions.action_cancelled=1;

How can we update columns values on multiple rows with a single , Column values on multiple rows can be updated in a single UPDATE columns values on multiple rows with a single MySQL UPDATE statement? In this case, the SET clause will be applied to all the matched rows. Now if we want to update the 'rate' column where tender_id is greater than or equal to  For the single-table syntax, the UPDATE statement updates columns of existing rows in the named table with new values. The SET clause indicates which columns to modify and the values they should be given. Each value can be given as an expression, or the keyword DEFAULT to set a column explicitly to its default value.

MySQL UPDATE, The MySQL UPDATE statement is used to update columns of existing rows in a table with new values. For multiple tables, UPDATE updates row in each table named in table_references that satisfy the conditions. will remain same, the following update statement can be used by using IF and CASE. The UPDATE command contains the word ____, followed by the name of the column to be updated, an equals sign, and the new value. commit During your current work session you can save your changes immediately by executing the _______ command.

MySQL Tutorial - MySQL By Examples for Beginners, Summary of MySQL Commands Used in this Tutorial Insert on all Columns INSERT INTO tableName VALUES (column1Value, Insert on selected Columns DELETE FROM tableName WHERE criteria UPDATE For numbers ( INT , DECIMAL , FLOAT ), you could use comparison operators: '=' (equal to), '<>' or '! That column will contain a value based on another column on the same row. What is the easiest, and most optimised, way to acomplish this. Is there any special magic I can use or do I simply have to SELECT each row and then UPDATE the same row individual and then loop through every row in the table until all is done?

MySQL IN, You will learn how to use MySQL IN operator to determine if a value matches any of the expr expression is equal to any value in the list, otherwise, it returns 0. When the values in the list are all constants, MySQL performs the following steps: in the WHERE clause of other statements such as UPDATE , and DELETE . MySQL UPDATE multiple columns. MySQL UPDATE command can be used to update multiple columns by specifying a comma separated list of column_name = new_value. Where column_name is the name of the column to be updated and new_value is the new value with which the column will be updated.

Comments
  • @Komarzer just updated it,removed where in subquery
  • And active_cancelled by action_cancelled by the way :)
  • I think you mean actions.user_id instead of temp.user_id ? Seems to be working like this