SQL Help to update products.

[sbutton]

Hi,

I have set up my upsells using Norman's groovy drillpine.biz upsell add on, and this involves making copies of products. Some of our popular products are referred to dozens of times as upsells, and this means that updating the descriptions / price or other details becomes a real headache.

I would like to put together some SQL so I can update all the products with a similar product code. I've done some SQL in the past but never with smelly Microsoft products.

I'm looking for something similar to :-

UPDATE Product set Full Description="blardy blardy bla" WHERE Product Reference RLIKE "#1$"

RLIKE being a regular expressing meaning anything ending in "#1" which would be all the upsell products which point to product 1.

Anyone got something similar to hand?

Thanks,

Steve Button

[NormanRouxel]

Steve,

The price shouldn't be a problem as this is only changeable on the main product.

The description is a real pain but I think that Jan (www.mole-end.biz) may have something that can help.

[andyh]

Hello Steve...

I had some problems using access to determine a # in the [product reference], but worked fine in SQLServer, Does anyone else know why? Or let me know if # works for you Steve. So for my products I used a – ( think _ and | work fine too)

If you are accessing the database via MSAccess try this

SELECT Product.[Product reference]
FROM Product
where Product.[Product reference] like "*-1";

Or, If you are using MSSQLServer try this

SELECT [Product reference]
FROM Product
where [Product reference] like '%-1';

Then just add the where to your existing update query, so it’s something like

UPDATE Product set [Full Description] ="blardy blardy bla" WHERE [Product reference] like '*-1';

Note the [ ] around field names with a space in them.

Hope this helps.

Andy