How to update single column with multiple entries in MySQL in the same table

mysql update multiple rows with same value
mysql update multiple rows from (select)
mysql update multiple columns from select
update a single column on multiple rows with one sql query
sql update multiple rows with different values from same table
how to update multiple rows in sql using single query
mysql update multiple rows one column
how to update multiple values in a single column in mysql

I have 4 columns in a table which are as

id = Auto Increment Value user_id = user id from session short_name = short name for page status = status of page

Following is the array in the $_POST
Array
(
    [pg_about] => 1
    [pg_tools] => 1
    [pg_in] => 1
    [pg_ex] => 1
    [pg_cats] => 1
    [pg_payee] => 1
    [pg_payment] => 1
    [pg_find] => 1
    [pg_menu] => 1
    [pg_sm] => 1
    [pg_cs] => 1
    [pg_csv] => 1
    [pg_calc] => 1
    [pg_budget] => 1
    [pg_tip] => 1
    [pg_exp_pr] => 1
    [pg_bal] => 1
    [pg_mn_ct_in] => 1
    [pg_mn_ct_ex] => 1
    [pg_mn_sct_in] => 1
    [pg_mn_sct_ex] => 1
    [pg_yr_in] => 1
    [pg_yr_ex] => 1
    [pg_dr_in] => 1
    [pg_dr_ex] => 1
    [pg_ct_in] => 1
    [pg_ct_ex] => 1
    [pg_sct_in] => 1
    [pg_sct_ex] => 1
    [pg_all] => 1
    [pg_all_time] => 1
    [pg_inc_vs_exp] => 1
    [pg_audit] => 1
    [pg_imp] => 1
)

pg_* are stored in the short_name column and the values in the status.

Currently I'm using the following method;

UPDATE tbl_menu2
   SET status = 0
 WHERE user_id = 5 
   AND short_name = 'pg_in';

UPDATE tbl_menu2
   SET status = '0' 
 WHERE user_id = 5 
   AND short_name = 'pg_ex';

...and so on

How can I make it more efficient using single SQL statement.

Note ; user_id will be same for all the 30+ entries.

Update ; I forgot to mention that the status will be different it won't be the same status value is user dependent.

if you have a set of value for short_name you could use a IN clause

UPDATE `tbl_menu2` 
SET `status` = '0' 
WHERE  `user_id` = 5 
AND `short_name` IN ( 'pg_in','pg_ex');

How to update single column with multiple entries in MySQL in the , if you have a set of value for short_name you could use a IN clause. UPDATE ` tbl_menu2` SET `status` = '0' WHERE `user_id` = 5 AND� 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.

You can use the same query as following by using in

UPDATE `tbl_menu2` SET `status` = '0' WHERE  `user_id` = 5 AND `short_name` in( 'pg_in','pg_ex','pg_cats',......);

Update a single column on multiple rows with one SQL query, You can actually do it using insert into on duplicate key update insert into [table ](ID,DATA) values(23,'FOO'),(47,'ASD'),(54,'DSF')..,� To update values in multiple columns, you use a list of comma-separated assignments by supplying a value in each column’s assignment in the form of a literal value, an expression, or a subquery. Third, specify which rows to be updated using a condition in the WHERE clause. The WHERE clause is optional.

I know this is MYSQL question but you can use PHP loop.

foreach ($array as $key => $value) {
    mysql_query("UPDATE tbl_menu2 SET status = $value WHERE user_id = 5  AND short_name = '$key'");
 }

MySQL UPDATE, For a single table, the UPDATE statement updates columns of existing rows For multiple tables, UPDATE updates row in each table named in 2 and 4 and the other will remain same, the following update statement can be� First, specify the table name that you want to change data in the UPDATE clause. Second, assign a new value for the column that you want to update. In case you want to update data in multiple columns, each column = value pair is separated by a comma (,). Third, specify which rows you want to update in the WHERE clause.

Different ways to update rows in a table using SQL progressing, If every field needs to be updated to the same value, you can do that using a Perhaps an easier way is to specify multiple tables after the UPDATE clause. a MySQL database can be boiled down to two basic points: Quotes (Single and� Modify/update an entry. Update of a row/entry in a MySQL table has the following format: update [table name] set [element]="newvalue" where [other element].[category]="value2"; Suppose you originally bought 995 shares of Microsoft at $46.24 each, but accidentally entered the total number as 5? update stocks set shares="995" where price="46.24";

MySQL 8.0 Reference Manual :: 13.2.13 UPDATE Statement, The SET clause indicates which columns to modify and the values they should be given. For the multiple-table syntax, UPDATE updates rows in each table named in For partitioned tables, both the single-single and multiple-table forms of this This option takes a list of one or more partitions or subpartitions (or both) . To update multiple columns use the SET clause to specify additional columns. Just like with the single columns you specify a column and its new value, then another set of column and values. In this case each column is separated with a column. Here we’ll update both the First and Last Names: UPDATE Person.Person Set FirstName = 'Kenneth

MySQL 8.0 Reference Manual :: 13.2.6 INSERT Statement, TABLE table_name} [ON DUPLICATE KEY UPDATE assignment_list] value: { expr TABLE in MySQL 8.0.19 and later to insert rows from a single table. In MySQL 8.0.19 and later, a row alias with one or more optional column alises can be� Example - Update multiple columns. Let's look at a MySQL UPDATE example where you might want to update more than one column with a single UPDATE statement. UPDATE customers SET state = 'California', customer_rep = 32 WHERE customer_id > 100; When you wish to update multiple columns, you can do this by separating the column/value pairs with commas.

Comments
  • somethine like AND `short_name` IN (" . implode(',', array_keys($_POST)) . "), but this is vulnerable to SQL injections