Pulling my hair out...PLEASE help

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Pulling my hair out...PLEASE help

Author

Topic

asiddle
Starting Member

35 Posts

Posted - 2006-08-01 : 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
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-01 : 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

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-08-01 : 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

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-08-01 : 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

asiddle
Starting Member

35 Posts

Posted - 2006-08-01 : 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
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-08-01 : 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 - 2006-08-01 : 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 - 2006-08-01 : 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 - 2006-08-01 : 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 - 2006-08-01 : 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
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-08-01 : 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

X002548
Not Just a Number

15586 Posts

Posted - 2006-08-01 : 17:38:19

Did you read the link?

It's in my sig as well

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-08-02 : 07:06:40

Here are a few more links.

For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources