Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL update multiple rows with same value

Tags:

sql

mysql

I use this to update (add points) rows which mgroup is 15

UPDATE ibf_members SET points = points + 500 WHERE mgroup = 15

What can I use to update (add points + 500) for rows which has its id as 5, 7, 10, 11, 16, 25 and also has mgroup as 15?

like image 840
Dipa Avatar asked Dec 07 '12 23:12

Dipa


People also ask

How to update all rows in SQL?

- GeeksforGeeks How to Update All Rows in SQL? In SQL, sometimes situations arise to update all the rows of the table. We will use the UPDATE command to achieve this in SQL. For this article, we will be using the Microsoft SQL Server as our database. UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;

Why does SQL Server update the value with the same?

updates the value with the same, according to the column definition. But for some reason SQL Server ignores the collation settings and actually writes the page. So, it's not "the same" by data definition, it's "the same" by binary contents of the page (s).

Does PostgreSQL update the same value twice in a query?

PostgreSQL is a database and not an ORM. It would have decreased performance if it took the time to check if a new value was the same as the updated value in your query. It will therefore update the value regardless of whether it is the same as the new value or not.

How do you define the same in SQL Server?

1. Define "the same". updates the value with the same, according to the column definition. But for some reason SQL Server ignores the collation settings and actually writes the page. So, it's not "the same" by data definition, it's "the same" by binary contents of the page (s).


2 Answers

You can use the IN clause for this, which is easier to read (and possibly more efficient?) than building a giant OR list. Try something like:

UPDATE ibf_members
SET points = points + 500
WHERE mgroup = 15
AND id IN (5, 7, 10, 11, 16, 25);
like image 89
Mike Christensen Avatar answered Sep 17 '22 16:09

Mike Christensen


Just add another condition to your WHERE clause:

UPDATE ibf_members SET points = points + 500 WHERE mgroup = 15 AND id IN (5, 7, 10, 11, 16, 25)
like image 27
Mike Brant Avatar answered Sep 18 '22 16:09

Mike Brant