Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

sql query to bulk change products


ptholt

Recommended Posts

Hi folks,

 

have we got anyone experienced in sql that can assist with the following request please -

 

i would like to search through "product description" field and locate all the records that are post 1953 (year is in product description only, not title) and for those records make the "tax class" to be set to none.

 

cheers.

Link to comment
Share on other sites

Try www.w3schools.com, the sql command will be in this fomat

 

Update table name set tax class = "none" where field name > data

 

but use phpmy admin to find the table and field names.

 

While you are there use the search facility to see how the test should be formatted as after you run a search the sql is displayed on the screen for you.

 

Then when you have worked out your command use the sql option in phpmyadmin.

 

Enjoy

Need help installing add ons/contributions, cleaning a hacked site or a bespoke development, check my profile

 

Virus Threat Scanner

My Contributions

Basic install answers.

Click here for Contributions / Add Ons.

UK your site.

Site Move.

Basic design info.

 

For links mentioned in old answers that are no longer here follow this link Useful Threads.

 

If this post was useful, click the Like This button over there ======>>>>>.

Link to comment
Share on other sites

Try www.w3schools.com, the sql command will be in this fomat

 

Update table name set tax class = "none" where field name > data

 

but use phpmy admin to find the table and field names.

 

While you are there use the search facility to see how the test should be formatted as after you run a search the sql is displayed on the screen for you.

 

Then when you have worked out your command use the sql option in phpmyadmin.

 

Enjoy

 

Thanks thats given me somewhere to start, a friend also suggest using navicat which appears to be a query builder so will try that as well.

Link to comment
Share on other sites

hmm ok, not quite as easy as id hoped as the field to change product_tax_class_is is in the the products table, yet the product description is in the table products_description...........

Link to comment
Share on other sites

Hi, Peter,

 

Not to plug, but I like NaviCat so much I became a reseller and recommend it to many of my clients.

 

Using NaviCat, or phpMyAdmin, you would have to do an inner join, something like this:

 

update products p inner join products_description as pd on p.products_id = pd.products_id

set p.products_tax_class_id= 0 where instr(pd.products_description,'1953')>0

 

Also, PLEASE, PLEASE, PLEASE backup up your database before running any queries against it!

 

Have fun!

 

 

hmm ok, not quite as easy as id hoped as the field to change product_tax_class_is is in the the products table, yet the product description is in the table products_description...........
Link to comment
Share on other sites

Hi Jon,

 

thanks for your assistance, i cant use navicat as my host will not open server access (ssh or telnet) to the database to allow me to use it :(

 

I backed up the database and attempted the query below, only 11 affected rows (which doesnt sound right with nearly 4000 items, approx half of which being post 1953)

 

Some items changed there tax class, others did not :blink:

 

 

 

Hi, Peter,

 

Not to plug, but I like NaviCat so much I became a reseller and recommend it to many of my clients.

 

Using NaviCat, or phpMyAdmin, you would have to do an inner join, something like this:

 

update products p inner join products_description as pd on p.products_id = pd.products_id

set p.products_tax_class_id= 0 where instr(pd.products_description,'1953')>0

 

Also, PLEASE, PLEASE, PLEASE backup up your database before running any queries against it!

 

Have fun!

Link to comment
Share on other sites

Afternoon, Peter!

 

Bummer on not being able to use NaviCat, it makes MySQL admin much, much easier.

 

Sorry the query didn't catch them all, but, truth be told, doing record changes based upon an 'instr' is sloppy, at best. You're relying on that exact string being in just the records you want to effect, and NOT in any records you don't want to effect.

 

A better approach would be if they have a sequential run of product ids, or maybe are unique to one category? Even if they all have a unique attribute which is only assigned to these 'post 1953' products. What you're looking for is a defined way to isolate them that is not subject to any ambiguity.

 

Let me know!

 

 

 

Hi Jon,

 

thanks for your assistance, i cant use navicat as my host will not open server access (ssh or telnet) to the database to allow me to use it :(

 

I backed up the database and attempted the query below, only 11 affected rows (which doesnt sound right with nearly 4000 items, approx half of which being post 1953)

 

Some items changed there tax class, others did not :blink:

Link to comment
Share on other sites

Evening Jon,

 

the only records to effect are those containing a number larger that 1953 in the prod description, none that ive seen have any other chars so cant think what caused it to effect so few :huh:

 

the product ids are non sequential as the products were entered in by geographic area (which is also the categories) not year, and at the time year was considered unimportant and not worth its own database field double doh!

The only way i can think of pin pointing the right products was by the year entered in the product description field.

 

 

Afternoon, Peter!

 

Bummer on not being able to use NaviCat, it makes MySQL admin much, much easier.

 

Sorry the query didn't catch them all, but, truth be told, doing record changes based upon an 'instr' is sloppy, at best. You're relying on that exact string being in just the records you want to effect, and NOT in any records you don't want to effect.

 

A better approach would be if they have a sequential run of product ids, or maybe are unique to one category? Even if they all have a unique attribute which is only assigned to these 'post 1953' products. What you're looking for is a defined way to isolate them that is not subject to any ambiguity.

 

Let me know!

Link to comment
Share on other sites

Hi, again, Peter...

 

Is the site live? If so, mind shooting me a link? Here, PM, or email, whatever. And may be a single example of one of the products to update.

 

You know, as another thought, have you looked at the EZ Product Update contribution? I'll see if I can find a link, I've used it on one cart, and it would be good for this....

 

Evening Jon,

 

the only records to effect are those containing a number larger that 1953 in the prod description, none that ive seen have any other chars so cant think what caused it to effect so few :huh:

 

the product ids are non sequential as the products were entered in by geographic area (which is also the categories) not year, and at the time year was considered unimportant and not worth its own database field double doh!

The only way i can think of pin pointing the right products was by the year entered in the product description field.

Link to comment
Share on other sites

you have a pm :)

 

Hi, again, Peter...

 

Is the site live? If so, mind shooting me a link? Here, PM, or email, whatever. And may be a single example of one of the products to update.

 

You know, as another thought, have you looked at the EZ Product Update contribution? I'll see if I can find a link, I've used it on one cart, and it would be good for this....

Link to comment
Share on other sites

Peter... oh, man, it's so simple now, we're both speaking English, and yet there's still a language barrier :lol:

 

Here's the deal... in your original message, you stated 'post 1953'. Now I've realized, you're using 'post' as a synonym for 'after', aren't you? I was interpreting it literally, like a mechanical part.

 

Unfortunately, that doesn't make your task much easier, but at least we know why the query didn't work. The update query we used earlier is quite literal, it's looking for any product with '1953' in the description.

 

You'd have to rerun the query for each year string ('1954','1955', etc.), changing the year string each pass.

 

However, in reviewing your site, it looks like the year is actually the first four characters of each product name, as well. Is that universal? If it is, it makes the SQL clause a little cleaner.

 

 

 

you have a pm :).
Link to comment
Share on other sites

Morning Jon,

 

i do recall a trip to the usa where i was ordering pizza to a perfectly spoken young american guy, who then stopped me, apologised and said he would have to get someone to speak to me in English as he didn't understand, i was :blink:

 

as you say by post i meant anything after 1953, i did incorrectly think that > was referring to greater than 1953 and would change anything above it, i guess not hehe

 

The year is not universally the first four chars, we had 2-3 people performing data entry along with some imports from an older site so i dont think year being the first four chars can be relied upon, so i will try to run it once for each year, sounds like a fun job.

 

Many thanks once again for your help

 

 

Peter... oh, man, it's so simple now, we're both speaking English, and yet there's still a language barrier :lol:

 

Here's the deal... in your original message, you stated 'post 1953'. Now I've realized, you're using 'post' as a synonym for 'after', aren't you? I was interpreting it literally, like a mechanical part.

 

Unfortunately, that doesn't make your task much easier, but at least we know why the query didn't work. The update query we used earlier is quite literal, it's looking for any product with '1953' in the description.

 

You'd have to rerun the query for each year string ('1954','1955', etc.), changing the year string each pass.

 

However, in reviewing your site, it looks like the year is actually the first four characters of each product name, as well. Is that universal? If it is, it makes the SQL clause a little cleaner.

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...