CODEWITHSUNDEEP
mysqlsql-update
bear
bear

Reputation: 11615

Update multiple rows with mySQL

I have a table that look like this:

property   propertyid  value
 active     1           1
 datastore  2           apc

How would I formulate a SQL query to update a few rows, without updating all of them. IE, if there were 6 rows, update 2,3 and 4 but not 1,5 and 6?

Thanks.

Upvotes: 4

Views: 6812

Answers (1)

Jude Cooray
Jude Cooray

Reputation: 19862

If it is the same column(s) that you are updating on all the rows with the same value you can do it easily with a query like this.

UPDATE property SET value=5 where propertyid IN(2,3,4)

This will set the value 5 to all rows where the property id is either 2,3 or 4.

If you want to update different rows with different values, I am afraid you will have to write separate SQL statements. You could come with a SQL query using the CASE statement but it would be much readable and maintainable with a simple SQL query.

Assuming that you are manipulating the database from an application, I am pretty sure that with whatever programming language you will be using, it will be easy as writing 1 SQL statement and looping, replacing the values and executing the query OR appending all the SQL update statements to a string (while looping) and pass it to the database to execute all at once. I am sorry I don't know whether either would have a significant performance impact over the other, but I believe that executing all at once would have some benefit in regards to performance.

Upvotes: 9

Related Questions