How to update multiple rows in mysql

Question

I have importing table in my database. This table columns are product_id and ext_product_id. Product_id is unique, but ext_product_id in table has same. I have to update this multiple ext_product_id. My table has thousands of columns. I can't manualy update these rows.

my table foramt is:

 product_id ext_product_id 44 796666 45 796666 46 796666 306 1275631 308 1275631 309 1275631 . .

i have table foramt like this:

 my table foramt is: product_id ext_product_id 44 796666 45 796667 46 796668 306 1275631 308 1275632 309 1275633 . .

How to update this ?

My query only works with a single record:

 update 13_product_id set ext_product_id='796667' where product_id='45';

I have to update multiple rows, please give me a suggestion.

update 13_product_id set ext_product_id='796668' where product_id='46'; — user3685376
– user3685376, Commented Jun 12, 2014 at 13:40
Then remove WHERE clause, if you want to update all records. If you want to updated only some rows ,then list them in the IN clause. — souslov
– souslov, Commented Jun 12, 2014 at 13:41

Bill Gregg · Accepted Answer · 2014-06-12 13:40:55Z

Try updating where your IDs are in:

 update 13_product_id set ext_product_id='796666' where product_id in ('45','16','15');

etc, etc

user2762134 · Accepted Answer · 2014-06-12 13:40:55Z

Well depending on what you want to do you can add multiple ids into the loop so instead of

where product_id = 45

You could have

where product_id in (45,46,47,48)

user2152590 · Accepted Answer · 2014-06-12 13:43:32Z

If you know the product_id's to update, use IN like this:

update product_id set ext_product_id='796666' where product_id IN (45, 46, 386...);

Or, if you want to update all rows with that ext_product_id, just drop your "where" clause::

update product_id set ext_product_id='796666'

Gordon Linoff · Accepted Answer · 2014-06-12 13:43:59Z

Are you trying to update all rows that have the same extended product as '45'? If so, this should do what you want:

update 13_product_id toupdate join (select ext_product_id from 13_product_id where product_id = '45' ) t on toupdate.ext_product_id = t.ext_product_id set toupdate.ext_product_id = '796667';

Collectives™ on Stack Overflow

How to update multiple rows in mysql

4 Answers 4

Comments

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Related