SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SQL Guru Wanted
 New Topic  Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

asiddle
Starting Member

35 Posts

Posted - 08/02/2006 :  14:31:21  Show Profile  Reply with Quote
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)

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')

Where is it placing these tables?
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 08/02/2006 :  14:37:13  Show Profile  Reply with Quote
These tables are temporary tables. You can tell because of the <#> pound sign in front of the name. All temp tables reside in tempdb. They are automagically dropped by sql server when your "session" ends. In my example, they are only there as a possible guide to show you how you could create your own mapping table in your database. The #prod table is just supposed to represent your actual products table. You hadn't provided any DDL by the time I posted that code so it is just a simple example.

At your leisure, please read about temp tables in Books Online.

EDIT:
Yes, the "p" and "m" are table aliases. You can read more about them under FROM Clause in Books Online.

Be One with the Optimizer
TG

Edited by - TG on 08/02/2006 14:39:15
Go to Top of Page

asiddle
Starting Member

35 Posts

Posted - 08/02/2006 :  15:55:49  Show Profile  Reply with Quote
Thanks TG and evryone who was helpful. Its been a crash course in SQL the last couple of days. Hopefully wont have to do it again. I will now go and play with TGs code and get to grips with it.
Go to Top of Page

asiddle
Starting Member

35 Posts

Posted - 08/03/2006 :  04:50:36  Show Profile  Reply with Quote
When I run this it claims it affects 1366 rows. There is 8000. Then when I go to the db incproducts none of the psections have changed. Have I missed something here?

create table catconversion(catid int, pSection int)
insert catconversion (catid, pSection) values (1,72)
insert catconversion (catid, pSection,) values (5,73)
insert catconversion (catid, pSection,) values (11,74)
insert catconversion(catid, pSection,) values (12,75)
insert catconversion(catid, pSection,) values (73,209)
insert catconversion(catid, pSection,) values (74,210)

--Product Table--
create table incproducts (pSection int, prodName varchar(10))
insert incproducts (pSection, prodName) values (1, 'prod1')
insert incproducts (pSection, prodName) values (5, 'prod2')
insert incproducts (pSection, prodName) values (11, 'prod3')
insert incproducts (pSection, prodName) values (12, 'prod4')
insert incproducts (pSection, prodName) values (73, 'prod5')
insert incproducts (pSection, prodName) values (74, 'prod6')

update incproducts set incproducts.psection = catconversion.psec
from incproducts
join catconversion on catconversion.psec = incproducts.psection


Go to Top of Page

asiddle
Starting Member

35 Posts

Posted - 08/03/2006 :  05:00:08  Show Profile  Reply with Quote
I just tried the query and notice some issues

create table catconversion(catid int, pSection int)
insert catconversion (catid, pSection) values (1,72)
insert catconversion (catid, pSection) values (5,73)
insert catconversion (catid, pSection) values (11,74)
insert catconversion(catid, pSection) values (12,75)
insert catconversion(catid, pSection) values (73,209)
insert catconversion(catid, pSection) values (74,210)

update incproducts set incproducts.psection = catconversion.pSection
from incproducts
join catconversion on catconversion.pSection = incproducts.pSection

Ok so even on this when run I only get 2 rows changed
Go to Top of Page

asiddle
Starting Member

35 Posts

Posted - 08/03/2006 :  05:36:44  Show Profile  Reply with Quote
Ok all, I have finally figured it all out. Add to the fact the supplier db contains more categories than they have actually give did not help.

Again a BIG thanks to everyone that assisted and tolerated me.
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 08/03/2006 :  08:20:13  Show Profile  Reply with Quote
I guess you noticed you had an issue with your JOIN criteria:

update incproducts set incproducts.psection = catconversion.pSection
from incproducts
join catconversion on catconversion.catid = incproducts.psection


Congrats!

Be One with the Optimizer
TG
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.3 seconds. Powered By: Snitz Forums 2000