SQL Guru Wanted - SQL Server Forums

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

SQL Guru Wanted

Author

Topic

asiddle
Starting Member

35 Posts

Posted - 2006-08-02 : 06:18:17

Ok guys I have read everything in my previous post but unfortunately can not seem to get it to work properly. Would anyone like to do this 10 minutes work (I am sure). Obviously I dont expect it to be free but if I continue I am going to get a sledge hammer to this now.

Thanks in advance

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-02 : 08:14:24

maybe you can start by providing information as per this link http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-02 : 09:03:25

asiddle,

In that post ur question was not clear enough, thats why all are guessing and encouraging u to read the above link.

Srinika

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2006-08-02 : 09:46:25

I don't see a question here, you sure aren't making it easy to help you.

Damian
"A foolish consistency is the hobgoblin of little minds." - Emerson

asiddle
Starting Member

35 Posts

Posted - 2006-08-02 : 09:48:52

Ok I have 2 tables incproduct and products. Products is the main db that the ecomm store works from. Products is a table populated from a csv file from my supplier.

The Problem:
The categories in the product db do not relate to the categories in the store (column pSection).

What I need
I need a way to translate all category ids in incproduct to the correct categories required by products. Now I have created a third table with the relevant categories to change to but I just dont know how to make it all work together

CREATE TABLE [dbo].[products] (
[pID] [nvarchar] (128) COLLATE Latin1_General_CI_AS NOT NULL ,
[pName] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[pName2] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[pName3] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[pSection] [int] NULL ,
[pDropship] [int] NULL ,
[pDescription] [ntext] COLLATE Latin1_General_CI_AS NULL ,
[pDescription2] [ntext] COLLATE Latin1_General_CI_AS NULL ,
[pDescription3] [ntext] COLLATE Latin1_General_CI_AS NULL ,
[pLongdescription] [ntext] COLLATE Latin1_General_CI_AS NULL ,
[pLongdescription2] [ntext] COLLATE Latin1_General_CI_AS NULL ,
[pLongdescription3] [ntext] COLLATE Latin1_General_CI_AS NULL ,
[pImage] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[pLargeimage] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[pDownload] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[pPrice] [float] NULL ,
[pListPrice] [float] NULL ,
[pWholesalePrice] [float] NULL ,
[pShipping] [float] NULL ,
[pShipping2] [float] NULL ,
[pWeight] [float] NULL ,
[pTax] [float] NULL ,
[pDisplay] [tinyint] NULL ,
[pSell] [tinyint] NULL ,
[pStaticPage] [bit] NULL ,
[pStockByOpts] [bit] NULL ,
[pRecommend] [bit] NULL ,
[pOrder] [int] NULL ,
[pExemptions] [tinyint] NULL ,
[pDims] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[pInStock] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[products] WITH NOCHECK ADD
CONSTRAINT [DF__products__pSecti__60A75C0F] DEFAULT (0) FOR [pSection],
CONSTRAINT [DF__products__pDrops__619B8048] DEFAULT (0) FOR [pDropship],
CONSTRAINT [DF__products__pPrice__628FA481] DEFAULT (0) FOR [pPrice],
CONSTRAINT [DF__products__pListP__6383C8BA] DEFAULT (0) FOR [pListPrice],
CONSTRAINT [DF__products__pWhole__6477ECF3] DEFAULT (0) FOR [pWholesalePrice],
CONSTRAINT [DF__products__pShipp__656C112C] DEFAULT (0) FOR [pShipping],
CONSTRAINT [DF__products__pShipp__66603565] DEFAULT (0) FOR [pShipping2],
CONSTRAINT [DF__products__pWeigh__6754599E] DEFAULT (0) FOR [pWeight],
CONSTRAINT [DF__products__pTax__68487DD7] DEFAULT (0) FOR [pTax],
CONSTRAINT [DF__products__pDispl__693CA210] DEFAULT (0) FOR [pDisplay],
CONSTRAINT [DF__products__pSell__6A30C649] DEFAULT (0) FOR [pSell],
CONSTRAINT [DF__products__pStati__6B24EA82] DEFAULT (0) FOR [pStaticPage],
CONSTRAINT [DF__products__pStock__6C190EBB] DEFAULT (0) FOR [pStockByOpts],
CONSTRAINT [DF__products__pRecom__6D0D32F4] DEFAULT (0) FOR [pRecommend],
CONSTRAINT [DF__products__pOrder__6E01572D] DEFAULT (0) FOR [pOrder],
CONSTRAINT [DF__products__pExemp__6EF57B66] DEFAULT (0) FOR [pExemptions],
CONSTRAINT [DF__products__pInSto__6FE99F9F] DEFAULT (0) FOR [pInStock],
PRIMARY KEY CLUSTERED
(
[pID]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[incproducts] (
[partno] [nvarchar] (128) COLLATE Latin1_General_CI_AS NOT NULL ,
[vendname] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[proddesc] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[rrp] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[catid] [int] NULL ,
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Ok so I dont think I can explain it any more

asiddle
Starting Member

35 Posts

Posted - 2006-08-02 : 09:50:56

sorry that top section should read

Ok I have 2 tables incproduct and products. Incproducts is the main db that the ecomm store works from. Products is a table populated from a csv file from my supplier.

asiddle
Starting Member

35 Posts

Posted - 2006-08-02 : 09:51:34

Error again

Ok I have 2 tables incproduct and products. Products is the main db that the ecomm store works from. incproducts is a table populated from a csv file from my supplier.

X002548
Not Just a Number

15586 Posts

Posted - 2006-08-02 : 10:06:25

DUDE

Please read the link in my sig, and provide what we are looking for.

I'm sure you're problem is very minor...but to solve it we NEED that info

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

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-02 : 10:22:25

Also, its good if u write ur question in a document "Properly" and read it carefully and correct if there are any errors, rather than posting once, making a correction and after some time again making a correction and not giving any feedback or comment.

Key to solve a question is to presenting the question in a manner, understood by the others.

Srinika

asiddle
Starting Member

35 Posts

Posted - 2006-08-02 : 10:23:57

So what have I missed? It could not be explained any simpler. If I knew what else to put I would probably know the answer

asiddle
Starting Member

35 Posts

Posted - 2006-08-02 : 10:27:04

Look fine, just keep posting go read this article that article. I will go find the answer somewhere else. I just hope you dont ever need me to answer any qustions on any other forums because the answer is simple 'go read this article'. Frankly it could not be explained any simplier than it already has

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-08-02 : 10:41:58

as far as i see you haven't provided sample data and desired result based on that test data.

this data is needed because then anyone trying to solve the problem is simply going to cut and paste the code in
query analyzer and try it.

Take a step back an put yourself in our place. Pretend you have no idea what the problem is
and then see if you could solve it with the provided info.

seems to me you have this kind of scenario.
sourceTable [sourceValue] -< mapping table [sourceValue, destinationValue] >- destination table [destinationValue]

so you need to just join properly. something like:


update  t3
set     destinationValue = t2.sourceValue
from    sourceTable t1
        join mappingTable t2 on t1.sourceValue = t2.sourceValue
        join destinationTable t3 on t2.destinationValue = t3.destinationValue

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-08-02 : 10:43:20

I’m really sorry that SQLTeam was unable to respond to your question with the proper sense of urgency and seriousness.

Rest assured that we are taking appropriate action to make sure there is no repeat of this unfortunate incident.

We treasure your questions, and hope we will be able to serve you satisfactorily in the future. Best regards.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-02 : 10:45:52

asiddle,

Is this the simplest way, that u talk about ?

>> Oh what I forgot to say,
>> sorry that top section should read ....
>> Error again ....

also
>> go read this article that article
-- Asking to read only one article, which guids u to get an answer quickly.

May be it is so simple to u, for us, only having a vague idea.
Its just like asking I have 3 tables and can u answer my question

Srinika

Kristen
Test

22859 Posts

Posted - 2006-08-02 : 10:47:33

"just keep posting go read this article that article."

I can't see anyone has done that. There is ONE article posted which explains what folk here need in order to help you. "It could not be explained any simpler"

"So what have I missed"

As per Brett's Blog article:

You've already done 1 and 2

You can skip point 4 (unless you have something that folk here could start from)

So that leaves points 3 and 5:

Sample data (presented as INSERT statements), and examples of expected results.

"It could not be explained any simpler"

That's clearly your opinion

, however speaking for myself I won't attempt complex questions unless there is enough material for me to chuck it into my SQL tools and work up a solution. I simply don't have the time to build the core stuff plus a) you already have that and b) if I make my own you probably won't know how to convert it into the structures you are using, or you'll THEN tell me your structure and want me to re-work all my code - "no thanks" on that point!

You sound mighty miffed, but khtan told you what we needed in his very first response.

Hope that helps!

Kristen

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-08-02 : 11:24:19

quote:
Originally posted by asiddle

I just hope you dont ever need me to answer any qustions on any other forums because the answer is simple 'go read this article'.

That's a risk I'm willing to take...

asiddle
Starting Member

35 Posts

Posted - 2006-08-02 : 11:58:42

Guys, I really do appreciate your assistance and time to answer my somewhat rash replies. At the moment I am against the wall on this. As this is a topic heading of New to SQL thats the bottom line NEW. I dont have time to read several books on SQL to find what is going to be a simple issue to you guys and look to your knowledge for that assistance. KRISTEN, thankyou for explaining what bits I have missed. Ofcourse it would have been simplier to refer me to article http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx but uptil about 3 minutes ago I had no clue what the hell DML or DDL was. Frankly I still dont really know but at least I now know what it stands for.

Ok so some sample data from what I was going to try and then found a flaw.

UPDATE products
SET pSection = '72'
WHERE pSection = '1'
UPDATE products
SET pSection = '73'
WHERE pSection = '5'
UPDATE products
SET pSection = '74'
WHERE pSection = '11'
UPDATE products
SET pSection = '75'
WHERE pSection = '12'
UPDATE products
SET pSection = '76'
WHERE pSection = '14'
UPDATE products
SET pSection = '77'
WHERE pSection = '16'
UPDATE products
SET pSection = '78'
WHERE pSection = '21'
UPDATE products
SET pSection = '79'
WHERE pSection = '33'
.
.
.

Now this worked upto the point

UPDATE products
SET pSection = '209'
WHERE pSection = '73'

If you remember the first update was to change any section with value 1 to 73 so now we start to rewrite what was section 1 and is now 73 to 209 thus putting it in the wrong category.

The result is all of pSection has been rewritten to the correct values.

I cant actually find anywhere to attach the complete update text file so just request it if you need all the category info, alternatively I have it all in .csv as well

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-08-02 : 13:33:33

Ok, I'll try one more time...

Please refer back my code in your "part 1" of this saga:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=69849

notice how using a Mapping table solves your problem. The one blue highlighted statement replaces all your update statements.

Put the code into a query analyzer window and execute the code to see it work.

Be One with the Optimizer
TG

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-08-02 : 13:35:41

Set up a translation table that includes BEFORE and AFTER values. Then run a single UPDATE against your target table linked to your translation table.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-08-02 : 13:38:57

have you even looked at my post to see if it represents your problem?
In my opinion it's exactly what you need as Blindman and TG also points out.

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

asiddle
Starting Member

35 Posts

Posted - 2006-08-02 : 14:03:19

Thanks TG after all the reading I think I am getting what you have said.

What I dont understand is the p. and m. Am I correct in assuming it would be products. and map. ?

update prod set
prod.psec = map.psec
from #prod prod
join #map map on map.cat = prod.psec

Or have I read all this completely wrong?

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources