POPULATING NULL FIELDS FROM RELATED FIELD W/IN SAME TABLE

[missmis44]

FOR AN ARREST RECORD, WE ONLY ENTER THE CJI_PCN FOR THE FIRST RECORD.THIS SCRIPT NEEDS TO POPULATE ALL THE OTHER RECORDS WITH THE SAME ARRE_ID WITH THE CJI_PCN THAT IS POPULATED, IN OTHER WORDS WHERE CJI_PCN IS NULL, POPULATE IT WITH THE CJI_PCN OF THE RECORD THAT HAS THE SAME ARRE_ID.
FOR EXAMPLE: ARREST RECORD 1 HAS 3 CHARGES AND ARREST RECORD 2 HAS 4 CHARGES:
ARRE_ID CHARGE CJI_PCN
1 1 3201098167
1 2 NULL
1 3 NULL
2 1 3201098254
2 2 NULL
2 3 NULL
2 4 NULL

I WANT TO AUTOMATE POPULATING THE NULLS WITH 3201098167. THIS IS NOT JUST FOR ONE RECORD, I WANT TO DO THIS FOR THE ENTIRE TABLE.

HOW CAN I AUTOMATE THIS? WHAT IS THE SYNTAX?

 

[SantaMufasa]

Miss,

First, I know that your design is probably already fairly "cast in bronze", but should the opportunity for re-work occur, may I suggest that your design would improve and you would not need to go through this exercise if you split the ARREST table into an ARREST table and a CHARGE table.

Now that we've addressed the design issue, code to do what you want could be:

select * from arrest;

   ARRE_ID     CHARGE    CJI_PCN
---------- ---------- ----------
         1          1 3201098167
         1          2
         1          3
         2          1 3201098254
         2          2
         2          3
         2          4

7 rows selected.

update arrest a set cji_pcn =
    (select min(cji_pcn) from arrest b
      where a.arre_id = b.arre_id)
where cji_pcn is null;

5 rows updated.

select * from arrest;

   ARRE_ID     CHARGE    CJI_PCN
---------- ---------- ----------
         1          1 3201098167
         1          2 3201098167
         1          3 3201098167
         2          1 3201098254
         2          2 3201098254
         2          3 3201098254
         2          4 3201098254

7 rows selected.

Let us know if this satisfactorily resolves your need.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[missmis44]

The Arrest Table and Arrest_Charge Table are separate. Sorry that I did not clarify that. I will give that a try, thanks!

 

[SantaMufasa]

Miss,

If that is the case (no pun intended), then:

1) why is the table above called the ARREST table when it carries CHARGE data?
2) why is "CJI_PCN" repeated on every CHARGE row? It seems to me that "CJI_PCN" should be on the ARREST rows.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[missmis44]

THIS WORKED PERFECTLY. THANKS AND HAVE A GREAT WEEKEND!

UPDATE ARCHRG A
SET CJI_PCN =
(SELECT MAX(B.CJI_PCN)
FROM ARCHRG B
WHERE A.ARRE_ID = B.ARRE_ID
)
WHERE CJI_PCN = ' ';

 

[missmis44]

UNFORTUNATELY, THERE CAN BE MULTIPLE PCN NUMBERS FOR EACH ARREST RECORD DO TO POLICE POLICE. DARN BUSINESS RULES GET IN THE WAY. THE ARREST CHARGE TABLE IS CALLED ARCHRG AND THE ARREST TABLE IS ARMAIN. I TYPED IT UP WRONG...SORRY ABOUT THAT. ERIN

 

[SantaMufasa]

Whoa, Miss !!!

If it worked perfectly, then something is definitely wrong...When you say "WHERE CJI_PCN = ' ';". Are you telling me that you have precisely one blank space in each of the CJI_PCNs that have no explicit numeric value? Because that is the only time your code would have updated any row. When you ran your code, how many rows did it say were updated? If it said anything greater than "no rows updated", then you had screwy data in your rows. If, instead, it said "no rows updated", then it didn't do what you wanted.

Let us know,

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[SantaMufasa]

Additionally, if, as you said in your post from "22 Apr 05 20:09"

THERE CAN BE MULTIPLE PCN NUMBERS FOR EACH ARREST RECORD

...then why are we populating null CJI_PCNs with the value from another CJI_PCN? If an Arrest Record can have Multiple PCN numbers, then how do we know if we are using the correct one to populate the NULL entries?

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[missmis44]

When we book an inmate and fingerprint them they are given one pcn number. Sometimes for instance if we serve a warrant, they are fingerprinted again, and they get another pcn number. they get a pcn number each time they are fingerprinted. Our business rules are, if they have a separate pcn number they have to enter each pcn. We are only populating the NULLS, which are records that have the same PCN across the board. Thank again!

 

[SantaMufasa]

Also, what about the "WHERE CJI_PCN = ' ';" issue? If your data are behaving properly, the above WHERE clause should have resulted in "0 rows updated."

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.