Need Mysql Query Help

[Dirty Butter]

I'm trying to remove the sale_price for all the out of stock items currently "on sale". I thought this would work, but my syntax is wrong:

 

SELECT * FROM `CubeCart_inventory` WHERE 'sale_price'>0 AND 'stock_level'=0;
UPDATE `CubeCart_inventory` SET 'sale_price'=0;

 

Your help is appreciated.

[bsmither]

Don't use apostrophes around table names. Use backticks (same key as squiggle) or don't use them at all.

 

Then, try this single query:

UPDATE `CubeCart_inventory` SET sale_price = 0 WHERE stock_level < 1;

 

CubeCart's Global Sales mode doesn't use the product's individual sale price, but a common percentage from the regular price.

[havenswift-hosting]

Hi

You do it in a single statement but you also seem to have a mix of single quotes in your statements so be careful. Standard caveat when using sql statements like this - Only do so if you know exactly what you are doing and if any doubts ensure you have a backup of the database before. If you had run those two commands with correct syntax, you would have first selected all matching records and then as a sevond process, you would have set the sake_price of EVERY single product to zero

UPDATE 'CubeCart_inventory' SET 'sale_price'=0 WHERE 'sale_price'>0 AND 'stock_level'=0;

Ian

[Dirty Butter]

Thank you as always, Brian. Perfect and very useful.

[Dirty Butter]

And Ian I did have a suspicion I would end up setting all to zero if I had succeeded lol. But once I had my logic wrong I couldn't think of anything else to try. I know just enough to get into trouble.....

[Dirty Butter]

I don't allow out of stock purchases, but I do show out of stock items. We run a "finding service" as well.

[Dirty Butter]

In need of another query, and sure don't want to mess THIS one up! I want to move all our plush that are only in the main Animals Category to a child category of All Other Brands. Once I'm sure of the syntax, I will do the same thing again with a few other categories that have many pages of miscellaneous brand items, like Dolls.

 

These are the ones I want to change:

SELECT *  FROM `CubeCart_category_index` WHERE `cat_id` = 36 AND `primary` = 1

 

I want to change the cat_id to 134

[bsmither]

Make a backup.

 

UPDATE `CubeCart_category_index` SET `cat_id = '134' WHERE `cat_id` = '36' AND `primary` = '1';

[Dirty Butter]

Backup made first - learned my lesson

Thanks for the help as always!!!

 

No - syntax error near primary = 1

[bsmither]

No - syntax error near primary = 1

 

What is this? Is this an error reported back from the database? If so, what is the complete error?

Really batting 1000, lately!

 

UPDATE `CubeCart_category_index` SET `cat_id` = '134' WHERE `cat_id` = '36' AND `primary` = '1';

[Dirty Butter]

From SQL tab in cpanel:

 

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'cat_id` = '36' AND `primary` = '1'' at line 1

 

The missing tick fixed it. NO apologies needed AT ALLL!!!!!!