1

I have a single table 'category' and once I want to disable a category, I have to check if it´s a parent or a child. If parent the parent_id is null. If not it has a respective parent_id meaning it´s a child of some other category.

If I receive a parent as param, I have to disable it and it´s children. If I receive a child as a param, I have to disable it and it´s relative grandchildren.

I need to do it recursively and tried to use a 'connect by' from Oracle but I´m not able to cover both cases: when it´s parent and when it´s child.

UPDATE CATEGORY 
SET STATUS = 0 
WHERE EXISTS ( 
SELECT STATUS 
FROM CATEGORY 
START WITH ID = ?1 
CONNECT BY PRIOR PARENT_ID = ID)

I saw many examples from different ways but working with 2 tables using joins but I have one single table and don´t know how to achieve it without using joins.

Thank you in advance.

--------------------------------------------------------
--  DDL for Table CATEGORY
--------------------------------------------------------

  CREATE TABLE "DATATAXONOMY"."CATEGORY" 
   (    "ID" VARCHAR2(15 BYTE), 
    "NAME" VARCHAR2(100 BYTE), 
    "DESCRIPTION" VARCHAR2(512 BYTE), 
    "STATUS" NUMBER(1,0), 
    "SHOW_ON_MENU" NUMBER(1,0), 
    "PARENT_ID" VARCHAR2(15 BYTE), 
    "CREATED_DATE" TIMESTAMP (6) WITH TIME ZONE, 
    "LAST_MODIFIED_DATE" TIMESTAMP (6) WITH TIME ZONE
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;
REM INSERTING into DATATAXONOMY.CATEGORY
SET DEFINE OFF;
Insert into DATATAXONOMY.CATEGORY (ID,NAME,DESCRIPTION,STATUS,SHOW_ON_MENU,PARENT_ID,CREATED_DATE,LAST_MODIFIED_DATE) values ('LIV95682','TESTE','TESTE','1','1',null,to_timestamp_tz('23.12.21 16:54:55,925000000 AMERICA/SAO_PAULO','DD.MM.RR HH24:MI:SSXFF TZR'),to_timestamp_tz('23.12.21 16:54:55,925000000 AMERICA/SAO_PAULO','DD.MM.RR HH24:MI:SSXFF TZR'));
Insert into DATATAXONOMY.CATEGORY (ID,NAME,DESCRIPTION,STATUS,SHOW_ON_MENU,PARENT_ID,CREATED_DATE,LAST_MODIFIED_DATE) values ('LIV13069','TESTE','TESTE','1','1','LIV72783',to_timestamp_tz('23.12.21 17:00:13,075000000 AMERICA/SAO_PAULO','DD.MM.RR HH24:MI:SSXFF TZR'),to_timestamp_tz('23.12.21 17:00:13,075000000 AMERICA/SAO_PAULO','DD.MM.RR HH24:MI:SSXFF TZR'));
Insert into DATATAXONOMY.CATEGORY (ID,NAME,DESCRIPTION,STATUS,SHOW_ON_MENU,PARENT_ID,CREATED_DATE,LAST_MODIFIED_DATE) values ('LIV11301','TESTE','TESTE','1','1','LIV95682',to_timestamp_tz('23.12.21 16:58:31,357000000 AMERICA/SAO_PAULO','DD.MM.RR HH24:MI:SSXFF TZR'),to_timestamp_tz('23.12.21 16:58:31,357000000 AMERICA/SAO_PAULO','DD.MM.RR HH24:MI:SSXFF TZR'));
Insert into DATATAXONOMY.CATEGORY (ID,NAME,DESCRIPTION,STATUS,SHOW_ON_MENU,PARENT_ID,CREATED_DATE,LAST_MODIFIED_DATE) values ('LIV72783','TESTE','TESTE','1','1','LIV95682',to_timestamp_tz('23.12.21 16:59:32,787000000 AMERICA/SAO_PAULO','DD.MM.RR HH24:MI:SSXFF TZR'),to_timestamp_tz('23.12.21 17:23:57,207000000 AMERICA/SAO_PAULO','DD.MM.RR HH24:MI:SSXFF TZR'));
--------------------------------------------------------
--  DDL for Index CATEGORY_ID_PK
--------------------------------------------------------

  CREATE UNIQUE INDEX "DATATAXONOMY"."CATEGORY_ID_PK" ON "DATATAXONOMY"."CATEGORY" ("ID") 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;
--------------------------------------------------------
--  Constraints for Table CATEGORY
--------------------------------------------------------

  ALTER TABLE "DATATAXONOMY"."CATEGORY" MODIFY ("ID" NOT NULL ENABLE);
  ALTER TABLE "DATATAXONOMY"."CATEGORY" MODIFY ("NAME" NOT NULL ENABLE);
  ALTER TABLE "DATATAXONOMY"."CATEGORY" MODIFY ("DESCRIPTION" NOT NULL ENABLE);
  ALTER TABLE "DATATAXONOMY"."CATEGORY" MODIFY ("STATUS" NOT NULL ENABLE);
  ALTER TABLE "DATATAXONOMY"."CATEGORY" MODIFY ("SHOW_ON_MENU" NOT NULL ENABLE);
  ALTER TABLE "DATATAXONOMY"."CATEGORY" MODIFY ("CREATED_DATE" NOT NULL ENABLE);
  ALTER TABLE "DATATAXONOMY"."CATEGORY" MODIFY ("LAST_MODIFIED_DATE" NOT NULL ENABLE);
  ALTER TABLE "DATATAXONOMY"."CATEGORY" ADD CONSTRAINT "CATEGORY_ID_PK" PRIMARY KEY ("ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"  ENABLE;
--------------------------------------------------------
--  Ref Constraints for Table CATEGORY
--------------------------------------------------------

  ALTER TABLE "DATATAXONOMY"."CATEGORY" ADD CONSTRAINT "CATEGORY_ID_FK" FOREIGN KEY ("PARENT_ID")
      REFERENCES "DATATAXONOMY"."CATEGORY" ("ID") ENABLE;

4
  • Please provide CREATE TABLE and a few INSERT statements. Dec 27, 2021 at 18:40
  • Thank you, @Michael Dec 27, 2021 at 19:46
  • When you send 'LIV95682', you want all 4 set to 0 but when you send 'LIV72783' you only want that one and 'LIV13069' set to 0 Dec 27, 2021 at 20:12
  • Exactly! It should be like this. Dec 28, 2021 at 12:49

1 Answer 1

2

This should be what you want:

merge INTO category a
using (
    select id, :status status
    from category
    start with id=:id
    connect by parent_id = prior id
) b
on (a.id = b.id)
when MATCHED then update
    set a.status = b.status;

The SELECT clause picks out the IDs and their new STATUS values that you want to change. The MERGE statement does the bulk update.

By using named parameters ( :id, :status instead of ? ), I can test in SQL Developer with various values to ensure the code works.

IIRC - the ability to use named parameters in JDBC requires a driver that understands them. (eg the Oracle supplied drivers)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.