| Author |
 Topic  |
|
|
asiddle
Starting Member
35 Posts |
 Posted - 08/01/2006 : 12:30:31
|
Ok so every week I have to download a file containing in excess of 40000 products. I then have to change the category ids to suit my system. Is there a script or something that I could create so I just run it each week to do a search and replace and then loop until all category ids have been changed.
Table = ecommstore
Column = section
I would need to change say from 84 to 1 then 86 to 2, 87 to 3 etc etc |
|
|
Srinika
Flowing Fount of Yak Knowledge
Sri Lanka
1378 Posts |
Posted - 08/01/2006 : 12:51:50
|
Does ur change is same or following a pattern all the time ?
or if u r worried about hair
http://www.hairlosstalk.com/discussions 
Srinika
|
Edited by - Srinika on 08/01/2006 12:53:59 |
 |
|
|
jsmith8858
Dr. Cross Join
USA
7423 Posts |
Posted - 08/01/2006 : 12:56:27
|
Just create a table that maps the imported values to the ones you need.
Then import the raw data into a table, and join to your mapping table to retrieve the corresponding ID.
I suspect that you may need to learn the basics of SQL to implement this, since it's a pretty core concept of relational databases; I strongly urge you to do some reading about SQL to get familiar with JOINS and SELECT statements and the like.
- Jeff |
Edited by - jsmith8858 on 08/01/2006 13:02:01 |
|
|
|
TG
Flowing Fount of Yak Knowledge
USA
5469 Posts |
Posted - 08/01/2006 : 13:00:49
|
How about:
Instead of "changing" the values, how about adding a new column. Also maybe a new map table (ie product_category_map) Use your internal value to associate with the rest of your model but leave the exteranal value as an attribute of the product table. Your new values will be the one that has <null> for the interal id and you can update those values with a single update statement (joining to your map table).
Be One with the Optimizer
TG |
Edited by - TG on 08/01/2006 13:01:16 |
|
|
|
asiddle
Starting Member
35 Posts |
Posted - 08/01/2006 : 13:06:10
|
Guys whilst I really really appreciate all your help I need this in basic terms. I rarely touch sql and have had to learn 6 - 7 new things in the last month to get to where I am know. I am sure to you guys this is very simple, but to me this has now become a show stopper. I am not short of sitting in a corner dribbling from the amount I have had to take in. My brain is now on overload with a continous migrain. Please just treat me like an idiot and explain.
Thanks to all |
|
|
|
TG
Flowing Fount of Yak Knowledge
USA
5469 Posts |
Posted - 08/01/2006 : 13:30:42
|
>>Please just treat me like an idiot and explain.
If you've gotten to this point I doubt you're an idiot but you may be over your head. Read this post and try to define a specific (simple) problem you want solved. Then post what you you've got so far and enough detail for what you need so we can help. Hang in there...
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Be One with the Optimizer
TG |
|
|
|
asiddle
Starting Member
35 Posts |
Posted - 08/01/2006 : 13:47:13
|
I have 2 databases of which one has a column called pSection and the other a column called category. Now I need to convert the numbers in category to numbers recognised by the program in psection. eg.
Category psection
1 10
17 11
18 12
At the moment, each week when the new product list is published I manually do a find and replace to change the category to a psection id. I am hoping that I could create a script to execute each week to automatically change each one for me. At the moment I have nothing as I cant seem to find anything on the web (or I am not looking for the right thing) which will explain how to and why hence why I am now here. I guess I need that little bit of insight to get me started. |
|
|
|
asiddle
Starting Member
35 Posts |
Posted - 08/01/2006 : 13:49:37
|
| Oh what I forgot to say, whilst it may be obvious, the only db that is used contains the pSection Id. The db with category is just something my supplier supplies with all the latest changes in product etc, but obviously I need to update from it, so the conversion of cat to psec is very important, ensuring all product ire in the right section in the ecomm site |
|
|
|
asiddle
Starting Member
35 Posts |
Posted - 08/01/2006 : 13:55:24
|
I did try this
DECLARE @find int(4),
@replace int(4),
@patfind int(4)
SELECT @find = '1',
@replace = '10'
SELECT @patfind = '%' + @find + '%'
UPDATE store
SET pSection = STUFF( pSection,
PATINDEX( @patfind, pSection ),
DATALENGTH( @find ),
@replace )
WHERE pSection LIKE @patfind
But unfortunately it just kicked up loads of error. Add to that I have no idea how to get it to loop so I could enter all the cat to psec id changes
|
|
|
|
asiddle
Starting Member
35 Posts |
Posted - 08/01/2006 : 14:15:59
|
Ok so I have found this works
UPDATE products
SET pSection = '72'
WHERE pSection = '1'
Not the best way I am sure but once created I just need to run it each week |
|
|
|
TG
Flowing Fount of Yak Knowledge
USA
5469 Posts |
Posted - 08/01/2006 : 14:37:16
|
Ok, you didn't follow the link instructions too well but see if this gives you any ideas:
set nocount on
--this is your Mapping table
create table #map (psec int, cat int)
insert #map (psec, cat) values (1,72)
insert #map (psec, cat) values (2,73)
insert #map (psec, cat) values (3,74)
insert #map (psec, cat) values (72,209)
--This is your product table
create table #prod (psec int, prodName varchar(10))
insert #prod (psec, prodName) values (72, 'prod1')
insert #prod (psec, prodName) values (73, 'prod2')
insert #prod (psec, prodName) values (74, 'prod3')
insert #prod (psec, prodName) values (209, 'prod4')
select * from #prod
--This is your update statment using your mapping table
update p set
p.psec = m.psec
from #prod p
join #map m on m.cat = p.psec
select * from #prod
drop table #prod
drop table #map
--======================================
output:
psec prodName
----------- ----------
72 prod1
73 prod2
74 prod3
209 prod4
psec prodName
----------- ----------
1 prod1
2 prod2
3 prod3
72 prod4
EDIT:
I added another row to the mapping table and the product table to illustrate that you won't have circular reference problems.
Be One with the Optimizer
TG |
Edited by - TG on 08/02/2006 13:28:02 |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
DonAtWork
Flowing Fount of Yak Knowledge
2111 Posts |
|
| |
Topic |
|
Pulling my hair out...PLEASE help
