• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 349
  • Last Modified:

sql query:

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
vbnetcoder
Asked:
vbnetcoder
  • 9
  • 6
  • 5
  • +1
1 Solution
 
sihar86Commented:
can you put example output here?
0
 
jimyXCommented:
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
 
OP_ZaharinCommented:
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
vbnetcoderAuthor Commented:
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
 
jimyXCommented:
Did you try the query I provided?
0
 
vbnetcoderAuthor Commented:
JimyX I don't understand the Parent_Category-1  where does the -1 come from?
0
 
jimyXCommented:
>   "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
 
OP_ZaharinCommented:
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
 
vbnetcoderAuthor Commented:
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
 
jimyXCommented:
Vbnetcoder, have you even tested my query?
0
 
OP_ZaharinCommented:
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
 
vbnetcoderAuthor Commented:
It isn't alway - 1 the data COULD look like this:

ID   Name   Parent
22        A           0
36       B           22
88       C          36
0
 
vbnetcoderAuthor Commented:
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
 
OP_ZaharinCommented:
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
 
OP_ZaharinCommented:

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
 
vbnetcoderAuthor Commented:
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
 
jimyXCommented:
How many levels you possibly could have? Is it always going to be three levels?
0
 
OP_ZaharinCommented:
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
 
vbnetcoderAuthor Commented:
jimyX: unlimited levels

NOTE if it is a TOP LEVEL Parent_Category_ID will = 0
0
 
vbnetcoderAuthor Commented:
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
 
vbnetcoderAuthor Commented:
That solution put me in the right dirrection
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 9
  • 6
  • 5
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now