Need help on SQL for change in DB from guru's

[knipper]

Hi All,

 

I need to change two fields in my products table on ALL rows in the table. One needs to be changed from either empty or "N" to "Y", then the products field from "0" to "1" on all 500+ products.

 

It's been a couple years since I did any SQL and I never was any good! But I am having a complete brain cramp.

 

If someone could give a sample query, I should be able to modify it for my exact needs. Thanks in advance! :)

[FIMBLE]

What are the tables you need changing?

[Guest]

something like:

UPDATE `products` SET column1='Y', column2=1;

 

Can't help more than that unless you are more specific

[knipper]

Hi Fimble,

 

Thanks for the quick reply.

 

I need to change two fields in the "products" table. I need to change the field "product_status" from 0 to 1, (In effect turning all products back on if they are off), and then from a previous contribution, need to change the field "disable_addtobasket" from either blank (null?) or N to Y

 

Thanks for the assistance.

[Guest]

UPDATE `products` SET products_status=1, disable_addtobasket='Y';

 

This will apply changes to every product. As always you should backup your database before running any direct mysql commands on it in case of unexpected results.

[knipper]

Thanks. That did it. Took care of everything I needed!

[t1234]

Do it as 2 seperate sql statments,

 

update products set product_status =1 where product_status=0;

 

I don't have the field disable_addtobasket in my schema so can't tell you whether you mean blank or null but either

 

update products set disable_addtobasket='Y' where disable_addtobasket in ('N', ' ');

 

or

 

update products set disable_addtobasket='Y' where disable_addtobasket = 'N' or disable_addtobasket is null;

 

You can tell if they are nulls as when you select the blank/null columns if they are null it will say null ...

 

Just as an aside always backup a table before running any dml on it !