Where clause in update query

[amberlynn]

Hello,
I'm fairly new to SQL. I can figure out how to get the data out that I want, but am not sure how to update.
I've created a SELECT statement that pulls all the records I want.
Right now it's pulling all records from one table that don't contain a date (SILA_START_DATE) and all associated records from another table that do contain a date (SAMM_DIST_APPR_DATE).
This works great.
Now I want to figure out how to replace all blank 'SILA_START_DATE' with 'SAMM_DIST_APPR_DATE'.
I can't figure out how to form the 'where' statement....

I'm pasting my SQL below:

SELECT
CUT_PERMIT.PERM_PERMIT_ID,
CUT_BLOCK.CUTB_BLOCK_ID,
SILVICULTURE_ACTIVITY.SILA_STATUS,
SILVICULTURE_ACTIVITY.SILA_START_DATE,
SILVICULTURE_COMPANY_ACTIVITY.SICA_ACTIVITY_NAME,
SILVICULTURE_AMENDMENT.SAMM_LICENSEE_APPR_DATE,
SILVICULTURE_AMENDMENT.SAMM_DIST_APPR_DATE
FROM
CUT_PERMIT,
BLOCK_ALLOCATION,
SILVICULTURE_ACTIVITY,
SILVICULTURE_COMPANY_ACTIVITY,
SILVICULTURE_AMENDMENT,
SILVICULTURE_PRESCRIPTION,
CUT_BLOCK
WHERE
CUT_PERMIT.PERM_SEQ_NBR = BLOCK_ALLOCATION.PERM_SEQ_NBR AND
BLOCK_ALLOCATION.CUTB_SEQ_NBR = CUT_BLOCK.CUTB_SEQ_NBR AND
SILVICULTURE_ACTIVITY.SICA_SEQ_NBR = SILVICULTURE_COMPANY_ACTIVITY.SICA_SEQ_NBR AND
SILVICULTURE_ACTIVITY.CUTB_SEQ_NBR = SILVICULTURE_PRESCRIPTION.CUTB_SEQ_NBR AND
SILVICULTURE_AMENDMENT.SILP_SEQ_NBR = SILVICULTURE_PRESCRIPTION.SILP_SEQ_NBR AND
SILVICULTURE_PRESCRIPTION.CUTB_SEQ_NBR = CUT_BLOCK.CUTB_SEQ_NBR AND
SILA_STATUS = 'P' AND
SICA_ACTIVITY_NAME = 'SP' AND
SILA_START_DATE IS NULL AND
SILVICULTURE_AMENDMENT.SAMM_DIST_APPR_DATE IS NOT NULL
/

Thanks in advance!!

Amber

 

[SantaMufasa]

AmberLynn,

Rather than our trying to match up your query with an UPDATE statement, I believe it will be less complex if you can explain precisely what joins up rows in "SILVICULTURE_ACTIVITY" table (that have NULL SILA_START_DATE) with rows in "SILVICULTURE_AMENDMENT".

If you can explain that relationship, then we can formulate an Oracle SQL UPDATE statement that will look like this in structure:

UPDATE SILVICULTURE_ACTIVITY x
   SET SILA_START_DATE = (SELECT SAMM_DIST_APPR_DATE 
                            from SILVICULTURE_AMENDMENT y
                           where <your join criteria>)
 WHERE SILA_START_DATE is NULL;

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

 

[amberlynn]

Thanks Dave,

I've attached a link to a page that contains a .pdf with a simple diagram/explanation of what I need to do.

http://www.chrysalid.ca/SQL_exp.pdf

Cheers,
Amber

 

[SantaMufasa]

Sorry, Amberlynn, for the delay...I've had some other irons in the fire today.

Thanks for the details in the .pdf. Given those details, I presume that your original query that you posted has all of the join conditions you need, so I've simply copied and pasted your original join in the following code upon which to start work:

UPDATE SILVICULTURE_ACTIVITY x
   SET SILA_START_DATE = (SELECT SAMM_DIST_APPR_DATE 
                            FROM CUT_PERMIT, 
                                 BLOCK_ALLOCATION, 
                                 SILVICULTURE_ACTIVITY, 
                                 SILVICULTURE_COMPANY_ACTIVITY, 
                                 SILVICULTURE_AMENDMENT, 
                                 SILVICULTURE_PRESCRIPTION, 
                                 CUT_BLOCK 
                           WHERE CUT_PERMIT.PERM_SEQ_NBR = BLOCK_ALLOCATION.PERM_SEQ_NBR
                             AND BLOCK_ALLOCATION.CUTB_SEQ_NBR = CUT_BLOCK.CUTB_SEQ_NBR
                             AND SILVICULTURE_ACTIVITY.SICA_SEQ_NBR = SILVICULTURE_COMPANY_ACTIVITY.SICA_SEQ_NBR
                             AND SILVICULTURE_ACTIVITY.CUTB_SEQ_NBR = SILVICULTURE_PRESCRIPTION.CUTB_SEQ_NBR
                             AND SILVICULTURE_AMENDMENT.SILP_SEQ_NBR = SILVICULTURE_PRESCRIPTION.SILP_SEQ_NBR
                             AND SILVICULTURE_PRESCRIPTION.CUTB_SEQ_NBR = CUT_BLOCK.CUTB_SEQ_NBR
                             AND SILA_STATUS = 'P'
                             AND SICA_ACTIVITY_NAME = 'SP'
                             AND SILA_START_DATE IS NULL
                             AND SILVICULTURE_AMENDMENT.SAMM_DIST_APPR_DATE IS NOT NULL
                         )
  WHERE SILA_START_DATE is null
/

Let us know if this proves useful.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

 

[amberlynn]

I cleaned things up a bit, and pulled out what I didn't need (very little).
When I try to run the following, I get an ORA-01427 error.
I understand what this error means, but not how to re-word the query so it works...

What I have now:

UPDATE SILVICULTURE_ACTIVITY
SET SILA_COMPLETION_DATE = (SELECT SILVICULTURE_AMENDMENT.SAMM_DIST_APPR_DATE
FROM
SILVICULTURE_ACTIVITY,
SILVICULTURE_COMPANY_ACTIVITY,
SILVICULTURE_AMENDMENT,
SILVICULTURE_PRESCRIPTION,
CUT_BLOCK
WHERE
SILVICULTURE_ACTIVITY.SICA_SEQ_NBR = SILVICULTURE_COMPANY_ACTIVITY.SICA_SEQ_NBR AND
SILVICULTURE_ACTIVITY.CUTB_SEQ_NBR = SILVICULTURE_PRESCRIPTION.CUTB_SEQ_NBR AND
SILVICULTURE_AMENDMENT.SILP_SEQ_NBR = SILVICULTURE_PRESCRIPTION.SILP_SEQ_NBR AND
SILVICULTURE_PRESCRIPTION.CUTB_SEQ_NBR = CUT_BLOCK.CUTB_SEQ_NBR AND
SICA_ACTIVITY_NAME = 'SP' AND
SILVICULTURE_AMENDMENT.SAMM_DIST_APPR_DATE IS NOT NULL)
WHERE
SILA_STATUS = 'P' AND SILA_COMPLETION_DATE IS NULL
/

 

[SantaMufasa]

That's one that we can resolve very quickly. I'll guess that you would want the most recent of the multiple dates that are returning. If that is the case, then use the "MAX()" function as I have done to your code, below:

UPDATE SILVICULTURE_ACTIVITY
SET SILA_COMPLETION_DATE = (SELECT [b]MAX(SILVICULTURE_AMENDMENT.SAMM_DIST_APPR_DATE)[/b]
    FROM 
      SILVICULTURE_ACTIVITY,
      SILVICULTURE_COMPANY_ACTIVITY,
        SILVICULTURE_AMENDMENT,
        SILVICULTURE_PRESCRIPTION,
        CUT_BLOCK
      WHERE 
         SILVICULTURE_ACTIVITY.SICA_SEQ_NBR = SILVICULTURE_COMPANY_ACTIVITY.SICA_SEQ_NBR AND
         SILVICULTURE_ACTIVITY.CUTB_SEQ_NBR = SILVICULTURE_PRESCRIPTION.CUTB_SEQ_NBR AND
         SILVICULTURE_AMENDMENT.SILP_SEQ_NBR = SILVICULTURE_PRESCRIPTION.SILP_SEQ_NBR AND
         SILVICULTURE_PRESCRIPTION.CUTB_SEQ_NBR = CUT_BLOCK.CUTB_SEQ_NBR AND
         SICA_ACTIVITY_NAME = 'SP' AND
         SILVICULTURE_AMENDMENT.SAMM_DIST_APPR_DATE IS NOT NULL)
WHERE
     SILA_STATUS = 'P' AND   SILA_COMPLETION_DATE IS NULL
/

Let us know how that works for you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”

 

[amberlynn]

Your question brought to my attention that I'm missing one more criteria.
If I add

AND SILVICULTURE_AMENDMENT.SAMM_AMENDMENT_NBR = '0'

then there will only ever be a single SAMM_DISTRICT_APPROVAL_DATE for each SILA_COMPLETION_DATE.

So what I have now is:

UPDATE SILVICULTURE_ACTIVITY
SET SILA_COMPLETION_DATE = (SELECT SILVICULTURE_AMENDMENT.SAMM_DIST_APPR_DATE
FROM
SILVICULTURE_ACTIVITY,
SILVICULTURE_COMPANY_ACTIVITY,
SILVICULTURE_AMENDMENT,
SILVICULTURE_PRESCRIPTION,
CUT_BLOCK
WHERE
SILVICULTURE_ACTIVITY.SICA_SEQ_NBR = SILVICULTURE_COMPANY_ACTIVITY.SICA_SEQ_NBR AND
SILVICULTURE_ACTIVITY.CUTB_SEQ_NBR = SILVICULTURE_PRESCRIPTION.CUTB_SEQ_NBR AND
SILVICULTURE_AMENDMENT.SILP_SEQ_NBR = SILVICULTURE_PRESCRIPTION.SILP_SEQ_NBR AND
SILVICULTURE_PRESCRIPTION.CUTB_SEQ_NBR = CUT_BLOCK.CUTB_SEQ_NBR AND
SICA_ACTIVITY_NAME = 'SP' AND
SILVICULTURE_AMENDMENT.SAMM_AMENDMENT_NBR = '0' AND
SILVICULTURE_AMENDMENT.SAMM_DIST_APPR_DATE IS NOT NULL)
WHERE
SILA_STATUS = 'P'
AND SILA_COMPLETION_DATE IS NULL
/

Does this bring us any closer??


Amber

 

[SantaMufasa]

Does this bring us any closer??

Dunno...you tell me. <grin>


What does you current code iteration do/not do that differs from what you want? (Your desk is too far away for me to see it clearly...Lucky for you.)

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[amberlynn]

When I run my select query (that I included in my first post) (and with the added criteria that I overlooked until my last post)I end up with 822 records. So for these 822 records, I need to replace the SILA_COMPLETION_DATE with the SAMM_DIST_APPR_DATE.
When I run the SQL I included in my last post, I still get the ORA_01427 error.

*sigh*
Amber

 

[SantaMufasa]

...And what happens when you include the code I suggested in my "2 Oct 08 19:19" code to ensure that you do not receive the ORA-1427 error?:

...SELECT [b]MAX(SILVICULTURE_AMENDMENT.SAMM_DIST_APPR_DATE)[/b]...

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[amberlynn]

My concern with using MAX is that there should only be one associated SAMM_DIST_APPR_DATE - and if there isn't, then the joins in my query are incorrect.

Am I looking at this wrong?

 

[SantaMufasa]

Then to pre-identify cases where there are more than one row per condition is to use this code:

SELECT <list of should-be unique expressions>, count(*)
  FROM <table_list>
 WHERE <join conditions>
 GROUP BY <list of should-be unique expressions>
HAVING COUNT(*) > 1;

Let us konw your findings.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”