work. My brain hurts though and I'm wondering if those of you with
more than a month's experience with SQL could tell me if there was
easier way to do this.
Situation - I have 3 sets of 2 character strings that together make up
a unique constraint in a table called 'Divisions'. Divisions also has
an auto-incrementer as primary key (divID). Divisions has a one to
many relationship with 'Binders' on divID.
Problem, I need to update the divID in Binders anywhere that the first
set of characters = '01' to the divID where the first set of characers
is something else, say '11' and the second pair match. It should be
noted that the second and third sets of strings are sub divisions to if
the they are both '00' they are headers and those with the same first
pair but different second and third pairs are sub divisions. I hope
that makes sense...this thing is whacky but it has to be that way.
DDL to follow, here's the crazy self-join, full script at bottom of
DDL. So again, was there an easier way to do this? I could have made
a temp table and stored current divID and levels 1, 2, an 3 and
compared them directly to tbl_divisions but this way seems more direct.
--For those divisions that have existing matches it will be
--assumed that reassignment value is the one we want to keep
--so first all binders with the old division header will need
--to be reassigned to the new division header via divID
UPDATE tbl_Binders
SET tbl_Binders.divID = div2.divID
FROM tbl_Binders
JOIN tbl_Divisions AS div1
ON tbl_Binders.divID = div1.divID
JOIN tbl_Divisions AS div2
ON (div1.level2 = div2.level2) AND
(div1.level3 = div2.level3)
WHERE div1.level1=17 AND div2.level1 = @.newLevel1
--HERE's THE FULL DDL--
IF DB_ID('TestDB') IS NOT NULL
DROP DATABASE TestDB
CREATE DATABASE TestDB
GO
USE TestDB
CREATE TABLE [tbl_Divisions] (
[divID] [int] IDENTITY (1, 1) NOT NULL ,
[level1] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF__tbl_Divis__level__1ED998B2] DEFAULT ('00'),
[level2] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF__tbl_Divis__level__1FCDBCEB] DEFAULT ('00'),
[level3] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
CONSTRAINT [DF__tbl_Divis__level__20C1E124] DEFAULT ('00'),
[divDesc] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK__tbl_Divisions__1CF15040] PRIMARY KEY CLUSTERED
(
[divID]
) ON [PRIMARY] ,
CONSTRAINT [CK_divNumbers] UNIQUE NONCLUSTERED
(
[level1],
[level2],
[level3]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [tbl_Binders] (
[binderID] [int] IDENTITY (1, 1) NOT NULL ,
[binderNo] [int] NULL ,
[divID] [int] NOT NULL ,
[catalogName] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[updated] [smalldatetime] NOT NULL CONSTRAINT [DF_tbl_Binders_updated]
DEFAULT (getdate()),
[remarks] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[url] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[keywords] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK__tbl_Binders__286302EC] PRIMARY KEY CLUSTERED
(
[binderID]
) ON [PRIMARY] ,
CONSTRAINT [FK_tbl_Binders_tbl_Divisions] FOREIGN KEY
(
[divID]
) REFERENCES [tbl_Divisions] (
[divID]
) ON DELETE CASCADE ON UPDATE CASCADE
) ON [PRIMARY]
GO
INSERT INTO [tbl_Divisions]
([level1],[level2],[level3],[divDesc])VA
LUES('01','00','00','Best
Topic')
INSERT INTO [tbl_Divisions]
([level1],[level2],[level3],[divDesc])VA
LUES('01','11','00','Great Sub
Topic')
INSERT INTO [tbl_Divisions]
([level1],[level2],[level3],[divDesc])VA
LUES('01','11','13','Second Sub
Topic')
INSERT INTO [tbl_Divisions]
([level1],[level2],[level3],[divDesc])VA
LUES('01','20','00','Third Sub
Topic')
INSERT INTO [tbl_Divisions]
([level1],[level2],[level3],[divDesc])VA
LUES('01','21','00','Etc')
INSERT INTO [tbl_Divisions]
([level1],[level2],[level3],[divDesc])VA
LUES('01','21','13','More great
sub topic')
INSERT INTO [tbl_Divisions]
([level1],[level2],[level3],[divDesc])VA
LUES('23','00','00','Topics to
convert')
INSERT INTO [tbl_Divisions]
([level1],[level2],[level3],[divDesc])VA
LUES('23','11','00','First
Subtopic to Convert')
INSERT INTO [tbl_Divisions]
([level1],[level2],[level3],[divDesc])VA
LUES('23','21','13','Second
Subtopic to Convert')
INSERT INTO [tbl_Divisions]
([level1],[level2],[level3],[divDesc])VA
LUES('23','01','20','Third
Subtopic to Convert')
INSERT INTO [tbl_binders]
([divID],[catalogName])VALUES(2,'TestCat
alog')
INSERT INTO [tbl_binders] ([divID],[catalogName])VALUES(8,'Another
Test
3')
INSERT INTO [tbl_binders] ([divID],[catalogName])VALUES(10,'Testin
g
Testing')
INSERT INTO [tbl_binders] ([divID],[catalogName])VALUES(9,'A catalog')
INSERT INTO [tbl_binders] ([divID],[catalogName])VALUES(7,'Acme Test
Catalog')
INSERT INTO [tbl_binders] ([divID],[catalogName])VALUES(3,'barnum test
catalog')
INSERT INTO [tbl_binders] ([divID],[catalogName])VALUES(4,'fresh test')
INSERT INTO [tbl_binders] ([divID],[catalogName])VALUES(9,'AAA Test
Catalog')
INSERT INTO [tbl_binders] ([divID],[catalogName])VALUES(6,'AA Test
Binder')
INSERT INTO [tbl_binders] ([divID],[catalogName])VALUES(10,'Binder
of
Test')
---
-- HERE's the full script I ran --
---
DECLARE @.oldDiv int, @.newDiv int
SET @.oldDiv = 7
SET @.newDiv = 1
DECLARE @.oldLevel1 char(2), @.newLevel1 char(2)
SELECT @.oldLevel1 = level1 FROM tbl_Divisions WHERE divID = @.oldDiv
SELECT @.newLevel1 = level1 FROM tbl_Divisions WHERE divID = @.newDiv
--change top level, but only if there won't be duplicates caused
--since level1, level2 & level3 makeup a unique contraint
UPDATE tbl_Divisions
SET level1 = @.newLevel1
WHERE level1 = @.oldLevel1 AND level2 + level3 NOT IN
(
SELECT level2 + level3 FROM tbl_Divisions WHERE level1 = @.newLevel1
)
--For those divisions that have existing matches it will be
--assumed that reassignment value is the one we want to keep
--so first all binders with the old division header will need
--to be reassigned to the new division header via divID
UPDATE tbl_Binders
SET tbl_Binders.divID = div2.divID
FROM tbl_Binders
JOIN tbl_Divisions AS div1
ON tbl_Binders.divID = div1.divID
JOIN tbl_Divisions AS div2
ON (div1.level2 = div2.level2) AND
(div1.level3 = div2.level3)
WHERE div1.level1=17 AND div2.level1 = @.newLevel1
--Then the old divID's can all be deleted
DELETE FROM tbl_Divisions WHERE level1 = @.oldLevel1
Thanks in advance for any input!On 9 Jan 2006 20:42:04 -0800, "mahalie" <mahalie@.gmail.com> wrote:
>Yowsers, I just completed my first self join and everything seems to
>work. My brain hurts though and I'm wondering if those of you with
>more than a month's experience with SQL could tell me if there was
>easier way to do this.
...
>Thanks in advance for any input!
Just at a glance, I think you're doing super for a newbie!
If you had a lot of data, or otherwise wanted to run this process
often at high speed, you might consider some more indexes, other than
that, assuming you're getting the results you want, way to go!
J.|||Self joins are a lot more common than you might think. It is best to think
of them just as any other join, and ignore the fact that you are joining the
table to itself. All that matters is you have two sets of data that you
need to join. Don't let the fact that they share the same physical storage
cloud the issue. The only real danger with self joins is typos, as it is
easy to get
and mix up the table aliases (unless you name assignthem very distinct aliases).
Based on what you describe, it looks like you are doing just fine with your
code.
"mahalie" <mahalie@.gmail.com> wrote in message
news:1136868124.736998.33860@.g49g2000cwa.googlegroups.com...
> Yowsers, I just completed my first self join and everything seems to
> work. My brain hurts though and I'm wondering if those of you with
> more than a month's experience with SQL could tell me if there was
> easier way to do this.
> Situation - I have 3 sets of 2 character strings that together make up
> a unique constraint in a table called 'Divisions'. Divisions also has
> an auto-incrementer as primary key (divID). Divisions has a one to
> many relationship with 'Binders' on divID.
> Problem, I need to update the divID in Binders anywhere that the first
> set of characters = '01' to the divID where the first set of characers
> is something else, say '11' and the second pair match. It should be
> noted that the second and third sets of strings are sub divisions to if
> the they are both '00' they are headers and those with the same first
> pair but different second and third pairs are sub divisions. I hope
> that makes sense...this thing is whacky but it has to be that way.
> DDL to follow, here's the crazy self-join, full script at bottom of
> DDL. So again, was there an easier way to do this? I could have made
> a temp table and stored current divID and levels 1, 2, an 3 and
> compared them directly to tbl_divisions but this way seems more direct.
> --For those divisions that have existing matches it will be
> --assumed that reassignment value is the one we want to keep
> --so first all binders with the old division header will need
> --to be reassigned to the new division header via divID
> UPDATE tbl_Binders
> SET tbl_Binders.divID = div2.divID
> FROM tbl_Binders
> JOIN tbl_Divisions AS div1
> ON tbl_Binders.divID = div1.divID
> JOIN tbl_Divisions AS div2
> ON (div1.level2 = div2.level2) AND
> (div1.level3 = div2.level3)
> WHERE div1.level1=17 AND div2.level1 = @.newLevel1
>
> --HERE's THE FULL DDL--
> IF DB_ID('TestDB') IS NOT NULL
> DROP DATABASE TestDB
> CREATE DATABASE TestDB
> GO
> USE TestDB
> CREATE TABLE [tbl_Divisions] (
> [divID] [int] IDENTITY (1, 1) NOT NULL ,
> [level1] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> CONSTRAINT [DF__tbl_Divis__level__1ED998B2] DEFAULT ('00'),
> [level2] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> CONSTRAINT [DF__tbl_Divis__level__1FCDBCEB] DEFAULT ('00'),
> [level3] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> CONSTRAINT [DF__tbl_Divis__level__20C1E124] DEFAULT ('00'),
> [divDesc] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> CONSTRAINT [PK__tbl_Divisions__1CF15040] PRIMARY KEY CLUSTERED
> (
> [divID]
> ) ON [PRIMARY] ,
> CONSTRAINT [CK_divNumbers] UNIQUE NONCLUSTERED
> (
> [level1],
> [level2],
> [level3]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [tbl_Binders] (
> [binderID] [int] IDENTITY (1, 1) NOT NULL ,
> [binderNo] [int] NULL ,
> [divID] [int] NOT NULL ,
> [catalogName] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [updated] [smalldatetime] NOT NULL CONSTRAINT [DF_tbl_Binders_updated]
> DEFAULT (getdate()),
> [remarks] [varchar] (1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [url] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [keywords] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> CONSTRAINT [PK__tbl_Binders__286302EC] PRIMARY KEY CLUSTERED
> (
> [binderID]
> ) ON [PRIMARY] ,
> CONSTRAINT [FK_tbl_Binders_tbl_Divisions] FOREIGN KEY
> (
> [divID]
> ) REFERENCES [tbl_Divisions] (
> [divID]
> ) ON DELETE CASCADE ON UPDATE CASCADE
> ) ON [PRIMARY]
> GO
> INSERT INTO [tbl_Divisions]
> ([level1],[level2],[level3],[divDesc])VA
LUES('01','00','00','Best
> Topic')
> INSERT INTO [tbl_Divisions]
> ([level1],[level2],[level3],[divDesc])VA
LUES('01','11','00','Great Sub
> Topic')
> INSERT INTO [tbl_Divisions]
> ([level1],[level2],[level3],[divDesc])VA
LUES('01','11','13','Second Sub
> Topic')
> INSERT INTO [tbl_Divisions]
> ([level1],[level2],[level3],[divDesc])VA
LUES('01','20','00','Third Sub
> Topic')
> INSERT INTO [tbl_Divisions]
> ([level1],[level2],[level3],[divDesc])VA
LUES('01','21','00','Etc')
> INSERT INTO [tbl_Divisions]
> ([level1],[level2],[level3],[divDesc])VA
LUES('01','21','13','More great
> sub topic')
> INSERT INTO [tbl_Divisions]
> ([level1],[level2],[level3],[divDesc])VA
LUES('23','00','00','Topics to
> convert')
> INSERT INTO [tbl_Divisions]
> ([level1],[level2],[level3],[divDesc])VA
LUES('23','11','00','First
> Subtopic to Convert')
> INSERT INTO [tbl_Divisions]
> ([level1],[level2],[level3],[divDesc])VA
LUES('23','21','13','Second
> Subtopic to Convert')
> INSERT INTO [tbl_Divisions]
> ([level1],[level2],[level3],[divDesc])VA
LUES('23','01','20','Third
> Subtopic to Convert')
> INSERT INTO [tbl_binders]
> ([divID],[catalogName])VALUES(2,'TestCat
alog')
> INSERT INTO [tbl_binders] ([divID],[catalogName])VALUES(8,'Another
Test
> 3')
> INSERT INTO [tbl_binders] ([divID],[catalogName])VALUES(10,'Testin
g
> Testing')
> INSERT INTO [tbl_binders] ([divID],[catalogName])VALUES(9,'A catalog')
> INSERT INTO [tbl_binders] ([divID],[catalogName])VALUES(7,'Acme Test
> Catalog')
> INSERT INTO [tbl_binders] ([divID],[catalogName])VALUES(3,'barnum test
> catalog')
> INSERT INTO [tbl_binders] ([divID],[catalogName])VALUES(4,'fresh test')
> INSERT INTO [tbl_binders] ([divID],[catalogName])VALUES(9,'AAA Test
> Catalog')
> INSERT INTO [tbl_binders] ([divID],[catalogName])VALUES(6,'AA Test
> Binder')
> INSERT INTO [tbl_binders] ([divID],[catalogName])VALUES(10,'Binder
of
> Test')
> ---
> -- HERE's the full script I ran --
> ---
> DECLARE @.oldDiv int, @.newDiv int
> SET @.oldDiv = 7
> SET @.newDiv = 1
> DECLARE @.oldLevel1 char(2), @.newLevel1 char(2)
> SELECT @.oldLevel1 = level1 FROM tbl_Divisions WHERE divID = @.oldDiv
> SELECT @.newLevel1 = level1 FROM tbl_Divisions WHERE divID = @.newDiv
> --change top level, but only if there won't be duplicates caused
> --since level1, level2 & level3 makeup a unique contraint
> UPDATE tbl_Divisions
> SET level1 = @.newLevel1
> WHERE level1 = @.oldLevel1 AND level2 + level3 NOT IN
> (
> SELECT level2 + level3 FROM tbl_Divisions WHERE level1 = @.newLevel1
> )
> --For those divisions that have existing matches it will be
> --assumed that reassignment value is the one we want to keep
> --so first all binders with the old division header will need
> --to be reassigned to the new division header via divID
> UPDATE tbl_Binders
> SET tbl_Binders.divID = div2.divID
> FROM tbl_Binders
> JOIN tbl_Divisions AS div1
> ON tbl_Binders.divID = div1.divID
> JOIN tbl_Divisions AS div2
> ON (div1.level2 = div2.level2) AND
> (div1.level3 = div2.level3)
> WHERE div1.level1=17 AND div2.level1 = @.newLevel1
> --Then the old divID's can all be deleted
> DELETE FROM tbl_Divisions WHERE level1 = @.oldLevel1
>
> Thanks in advance for any input!
>|||Thanks for the feedback. I was not sure about wether it was necessary
as the book I've been reading said "although self-joins are rare, there
are some unique queries that are best soved using self-joins".
I wasn't expecting to run into one so soon!
No comments:
Post a Comment