Solved

sql query:

Posted on 2011-03-18
21
340 Views
Last Modified: 2012-08-14
I have a table: Categories

It has the following fields:

Category_ID
Category_Name
Parent_Category

Parent_Category establishes hierchy for example if Parent_Category has and ID = to something in Category_ID it means it is a level bellow it in the hierchy.


My issue is .... for example if I have three records A, B, C and delete B, I want C to go right under A (always the next level up).

How would I do this?
0
Comment
Question by:vbnetcoder
  • 9
  • 6
  • 5
  • +1
21 Comments
 
LVL 2

Expert Comment

by:sihar86
ID: 35170020
can you put example output here?
0
 
LVL 24

Expert Comment

by:jimyX
ID: 35170037
Is A, B and C are different levels and each is parent to the next node?

Is this true:
A has no parent
B has one parent "A"
C has one parent "B"

Then your query will be:
update Categories set Parent_Category = Parent_Category-1 where Parent_Category > (select Parent_Category from Categories where Category_Name = 'B');

Then Delete 'B':
Delete from Categories where Category_Name = 'B';
0
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35170044
assuming category_ID is the field that contain value A, B and C as in your example, in SQL you can use sorting ascending (ASC):

SELECT * from Categories ORDER BY Category_ID ASC
0
 

Author Comment

by:vbnetcoder
ID: 35170049
jimyX: you asked if the following was true and the answer is YES


Is this true:
A has no parent
B has one parent "A"
C has one parent "B"
0
 
LVL 24

Expert Comment

by:jimyX
ID: 35170063
Did you try the query I provided?
0
 

Author Comment

by:vbnetcoder
ID: 35170073
JimyX I don't understand the Parent_Category-1  where does the -1 come from?
0
 
LVL 24

Expert Comment

by:jimyX
ID: 35170087
>   "Parent_Category has and ID = to something in Category_ID"

I assumed you have your data as follows:

ID   Name   Parent
1        A           0
2        B           1
3        C           2

So when B is deleted then you subtract one from the higher levels to eliminate the gap.

ID   Name   Parent
1        A           0
2        B           1
3        C           2

ID   Name   Parent
1        A           0
3        C           1
0
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35170088
vbnetcoder, can you draft an output with a real data you desired as suggested by sihar86? it shall helps us to understand what you are trying to achieve.
0
 

Author Comment

by:vbnetcoder
ID: 35170093
OP_Zaharin:

If the data is this

ID   Name   Parent
1        A           0
2        B           1
3        C           2

And I delete B

C needs to moved up under A (since B is gone).

Therefore, c needs to look like this

3        C          1

0
 
LVL 24

Expert Comment

by:jimyX
ID: 35170110
Vbnetcoder, have you even tested my query?
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35170113
thanks vbnetcoder. i think jimyX code and his explanation will work for you. except that the update statement is to be run after you delete the B record. the Parent_Category - 1 will update the whole hierarchy to go up except for the parent record

SQL> Delete Categories where ID = 2
SQL> UPDATE Categories set Parent_Category = Parent_Category - 1 WHERE PARENT <> 0

0
 

Author Comment

by:vbnetcoder
ID: 35170116
It isn't alway - 1 the data COULD look like this:

ID   Name   Parent
22        A           0
36       B           22
88       C          36
0
 

Author Comment

by:vbnetcoder
ID: 35170118
This is the current query Attached. How would i update it??
ALTER PROCEDURE [dbo].[spDeleteCategoriesByCategoryID]
	-- Add the parameters for the stored procedure here
	@Category_ID as int

AS
BEGIN
	SET NOCOUNT ON; 

DELETE FROM Categories WHERE Category_ID = @Category_ID

DELETE FROM Product_Categories WHERE Category_ID = @Category_ID


END

Open in new window

0
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35170119
i added the WHERE clause to indicate that don't upadate the Parent_Category. let me re-phrase the code:

SQL> UPDATE Categories set Parent_Category = Parent_Category - 1 WHERE Parent_Category <> 0

-this will work if u have only 1 parent record in Categories table. you need to have another field called GROUP to set a Group ID to differentiate a group of parents and child from other groups of parents and child. then the sql statement will look like this:

SQL> UPDATE Categories set Parent_Category = Parent_Category - 1 WHERE Grouping = 1 and Parent_Category <> 0

ID   Name   Parent  Group
1        A           0           1
2        B           1           1
3        C           2           1
4        A           0           2
5        B           1           2
6        C           2           2
0
 
LVL 23

Accepted Solution

by:
OP_Zaharin earned 500 total points
ID: 35170146

It isn't alway - 1 the data COULD look like this:

ID   Name   Parent
22        A           0
36       B           22
88       C          36

based on your example, do u mean if you delete the ID=36 record, you wanted Parent value for ID=88 to be update to 22?

ideally I would do this:
1- do a select for the record that we intent to delete then put the parent_category value into a variable (var)
2- delete the ID=36 record
3- update the record using the variable we fetch in step (1) and specify the where clause to find out what is the lowest number after the parent number '0'
eg: UPDATE Categories set Parent_Category =  var WHERE Category_ID = (Select Category_ID where Parent_Category <> 0 ORDER BY Parent_Category ASC)
0
 

Author Comment

by:vbnetcoder
ID: 35170149
based on your example, do u mean if you delete the ID=36 record, you wanted Parent value for ID=88 to be update to 22?

Yes ... that is what i mean
0
 
LVL 24

Expert Comment

by:jimyX
ID: 35170152
How many levels you possibly could have? Is it always going to be three levels?
0
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35170153
i left out "TOP 1" in my step 3 sql statement. "TOP 1" will take the 1st record in the sorting ascending query:

UPDATE Categories set Parent_Category =  var WHERE Category_ID = (Select TOP 1 Category_ID where Parent_Category <> 0 ORDER BY Parent_Category ASC)
0
 

Author Comment

by:vbnetcoder
ID: 35170160
jimyX: unlimited levels

NOTE if it is a TOP LEVEL Parent_Category_ID will = 0
0
 

Author Comment

by:vbnetcoder
ID: 35170164
I think this worked:


ALTER PROCEDURE [dbo].[spDeleteCategoriesByCategoryID]
      -- Add the parameters for the stored procedure here
      @Category_ID as int

AS
BEGIN
      SET NOCOUNT ON;
--Get the dID for the category that is going to be deleted
--Get the pID for the category that is going to be deleted
--Do a update of all of the parent_ID = ID where id = pID

DECLARE @pID int
DECLARE @dID AS INT

Select @pID=Parent_Category_ID FROM Categories WHERE Category_ID = @Category_ID
Select @dID=Category_ID FROM Categories WHERE Category_ID = @Category_ID
UPDATE Categories SET Parent_Category_ID = @pID WHERE Parent_Category_ID = @dID
DELETE FROM Categories WHERE Category_ID = @Category_ID

DELETE FROM Product_Categories WHERE Category_ID = @Category_ID


END
0
 

Author Closing Comment

by:vbnetcoder
ID: 35172052
That solution put me in the right dirrection
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now