MySQL query to update multiple rows based on a column using php

Question

Is there a way that I could update several rows based on a column?

I have a table called category with 2 columns (Category, number). The column category have 2 rows(Agriculture,Apparel) which is fixed. I need a mySQL update statement that could update the number column. That means 2 rows in 'number' column will have to be updated.

The code that I gave could only update one row at the time. How do I update both rows using a single query? Thanks.

My code:

$sql="UPDATE category
    SET number = '$AF'
    WHERE Category = 'Agriculture' ";

The query that you have now will update more than one row if you have multiple rows where Category = Agriculture. Perhaps you could setup a SQL fiddle to illustrate your problem more clearly. — Jay Blanchard
– Jay Blanchard, Commented Nov 14, 2014 at 16:03

Funk Forty Niner · Accepted Answer · 2014-11-14 16:12:32Z

2

Not quite sure I'm understanding. If you want to update all of the rows, then remove your where clause. SQL is very much a "the less you put in the query, the more you get"-type language.

If you want to change the values of the number field to DIFFERENT values, but in a single query, then you have to do a very ugly:

UPDATE category
SET number = CASE Category
   WHEN 'Agriculture' THEN $value_for_agriculture
   WHEN 'Apparel' THEN $value_for_apparel
END CASE

edited Nov 14, 2014 at 16:12

Funk Forty Niner

74.2k14 gold badges71 silver badges146 bronze badges

answered Nov 14, 2014 at 16:05

Marc B

362k44 gold badges433 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL query to update multiple rows based on a column using php

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related