| Author |
 Topic  |
|
asiddle
Starting Member
35 Posts |
 Posted - 08/02/2006 : 14:31:21
|
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? |
 |
|
|
TG
Flowing Fount of Yak Knowledge
USA
5469 Posts |
Posted - 08/02/2006 : 14:37:13
|
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 |
|
|
|
asiddle
Starting Member
35 Posts |
Posted - 08/02/2006 : 15:55:49
|
| 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. |
|
|
|
asiddle
Starting Member
35 Posts |
Posted - 08/03/2006 : 04:50:36
|
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
|
|
|
|
asiddle
Starting Member
35 Posts |
Posted - 08/03/2006 : 05:00:08
|
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 |
|
|
|
asiddle
Starting Member
35 Posts |
Posted - 08/03/2006 : 05:36:44
|
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. |
|
|
|
TG
Flowing Fount of Yak Knowledge
USA
5469 Posts |
Posted - 08/03/2006 : 08:20:13
|
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 |
|
|
|
Topic |
|
|
|
SQL Guru Wanted
