Hello,
I'm trying to run an update query, to replace all rows with a null date value, with a date from another table.
I get an ora-01427 error when I try to run the query.
I realize that the reason I get this is because there is >1 row being returned - but there are >1 records I need updated.
I'm thinking I need to use 'IN' instead of '=' but not sure how...
I've pasted my code below and attached a .pdf explanation (it's quite confusing).
Thanks in advance!!
Amber
UPDATE SILVICULTURE_ACTIVITY
SET SILA_START_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_START_DATE IS NULL AND
/
You want to update more than one row on SILVICULTURE_ACTIVITY, but you cannot update each SILVICULTURE_ACTIVITY.SILA_START_DATE column with multiple values like your query is trying to do.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
The problem is that you are including the updating table in the FROM clause of the sub-SELECT.
Removing that gives you:
Code:
UPDATE
SILVICULTURE_ACTIVITY SAC
SET
SAC.SILA_START_DATE =
(
SELECT
SAM.SAMM_DIST_APPR_DATE
FROM
SILVICULTURE_COMPANY_ACTIVITY SCA,
SILVICULTURE_PRESCRIPTION SP ,
SILVICULTURE_AMENDMENT SAM,
CUT_BLOCK CB
WHERE
SCA.SICA_SEQ_NBR = SAC.SICA_SEQ_NBR AND
SP.CUTB_SEQ_NBR = SAC.CUTB_SEQ_NBR AND
SAM.SILP_SEQ_NBR = SP.SILP_SEQ_NBR AND
CB.CUTB_SEQ_NBR = SP.CUTB_SEQ_NBR AND
SICA_ACTIVITY_NAME = 'SP' AND
SAM.SAMM_DIST_APPR_DATE IS NOT NULL
)
WHERE
SAC.SILA_STATUS = 'P' AND
SAC.SILA_START_DATE IS NULL
However, since the date you are attempting to get at is in the SAM table, I don't know why you feel you need to include the SCA and CB tables in the sub-SELECT. Of course, you don't say what table the SICA_ACTIVITY_NAME column is in, so maybe one of those two tables has a purpose.
In my original post, I attached a .pdf to clarify how my tables are linked, and what I'm trying to do.
This would answer your questions.
Thanks,
Amber
Yeah - missed that the first time - wow, but you are confused.
Okay, so try this:
Code:
UPDATE
SILVICULTURE_ACTIVITY SAC
SET
-- Update the start date
SAC.SILA_START_DATE =
(
SELECT
SAM.SAMM_DIST_APPR_DATE
FROM
SILVICULTURE_PRESCRIPTION SP ,
SILVICULTURE_AMENDMENT SAM
WHERE
SP.CUTB_SEQ_NBR = SAC.CUTB_SEQ_NBR AND
SAM.SILP_SEQ_NBR = SP.SILP_SEQ_NBR
)
WHERE
-- When status is P,
SAC.SILA_STATUS = 'P' AND
-- start date is NULL,
SAC.SILA_START_DATE IS NULL AND
-- and activity is SP
SAC.SICA_SEQ_NBR IN
(
SELECT
SCA.SICA_SEQ_NBR
FROM
SILVICULTURE_COMPANY_ACTIVITY SCA
WHERE
SCA.SICA_SEQ_NBR = SAC.SICA_SEQ_NBR AND
SCA.SICA_ACTIVITY_NAME = 'SP'
)
Note that the requirement of AMENDMENT.Samm_Dist_Appr_Date ≠ Null is extraneous.
Basically, if the date is already NULL, setting it to another NULL changes nothing, and testing to make sure the source date is not NULL before the record is updated is more work. So, unless you expect a significant number of NULLs, it won't be worth the extra effort.
And, as I said before, the CUT_BLOCK table is completely unnecessary here.
Last edited by chrisrlong; 10-02-2008 at 10:30 PM.
When I run the select SQL pasted at the end of this post, I return 822 records. So for these records, I want to replace the SILA_COMPLETION_DATE with the SAMM_DIST_APPR_DATE.
I tried your code (with a copy of my database) and ended up with a total of 2974 records changed.
And it seemed to have replaced all the dates with the same date - not the related date in the Amendment table.
Thanks,
Amber
SELECT
CUT_PERMIT.PERM_PERMIT_ID,
CUT_BLOCK.CUTB_BLOCK_ID,
SILVICULTURE_ACTIVITY.SILA_STATUS,
SILVICULTURE_ACTIVITY.SILA_COMPLETION_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_AMENDMENT_NBR = '0' AND
SILVICULTURE_AMENDMENT.SAMM_DIST_APPR_DATE IS NOT NULL
/
Well, let's see - you added new tables and new predicates and are wondering why the numbers are different. Are you serious?
Code:
SELECT
CP.PERM_PERMIT_ID ,
CB.CUTB_BLOCK_ID ,
SAC.SILA_STATUS ,
SAC.SILA_COMPLETION_DATE ,
SCA.SICA_ACTIVITY_NAME ,
SAM.SAMM_LICENSEE_APPR_DATE,
SAM.SAMM_DIST_APPR_DATE
FROM
CUT_PERMIT CP ,
BLOCK_ALLOCATION BA ,
SILVICULTURE_ACTIVITY SAC ,
SILVICULTURE_COMPANY_ACTIVITY SCA ,
SILVICULTURE_AMENDMENT SAM ,
SILVICULTURE_PRESCRIPTION SP ,
CUT_BLOCK CB
WHERE
-- When status is P,
SAC.SILA_STATUS = 'P' AND
-- start date is NULL,
SAC.SILA_START_DATE IS NULL AND
SCA.SICA_SEQ_NBR = SAC.SICA_SEQ_NBR AND
-- and activity is SP
SCA.SICA_ACTIVITY_NAME = 'SP' AND
-- What is all this nonsense??
CP.PERM_SEQ_NBR = BA.PERM_SEQ_NBR AND
BA.CUTB_SEQ_NBR = CB.CUTB_SEQ_NBR AND
SAC.CUTB_SEQ_NBR = SP.CUTB_SEQ_NBR AND
SAM.SILP_SEQ_NBR = SP.SILP_SEQ_NBR AND
SP.CUTB_SEQ_NBR = CB.CUTB_SEQ_NBR AND
-- Where did this come from?
SAM.SAMM_AMENDMENT_NBR = '0' AND
-- I already covered why this was not used
SAM.SAMM_DIST_APPR_DATE IS NOT NULL
Where did CUT_PERMIT come from?
Where did SAMM_AMENDMENT_NBR = '0' come from?
Originally Posted by amberh
And it seemed to have replaced all the dates with the same date - not the related date in the Amendment table.
Huh?
The UPDATE statement only targetted those rows where the SILA_START_DATE is NULL, so it shouldn't have replaced any actual 'date' with anything. Are you saying that absolutely none of the values were populated?
You misunderstood.
If you look closely - the SQL in my last post is a SELECT query - not an UPDATE query.
I added this to my last post for clarification. This query correctly shows how many rows I needed to change.
CUT_PERMIT was simply there for clarification (for me) - it wasn't part of the UPDATE query.
The SAMM_AMENDMENT_NBR = '0' was criteria I realized was missing - it is what ensures that there is only one record in the SILVICULTURE_AMENDMENT table for each record in the SILVICULTURE_ACTIVITY table.
I should have said it replaced all the NULL values with a single date, not the related date from the Amendment table.
Okay, let's see if I can explain this a little better.
First, your addition of extra tables and predicates changes the number of rows in the resultset.
You are adding three tables to the statement that have absolutely nothing to do with the functionality of the statement:
Code:
SELECT
...
FROM
SILVICULTURE_ACTIVITY SAC ,
SILVICULTURE_COMPANY_ACTIVITY SCA ,
SILVICULTURE_PRESCRIPTION SP ,
SILVICULTURE_AMENDMENT SAM ,
-- These tables are irrelevant and actually problematic
CUT_BLOCK CB ,
BLOCK_ALLOCATION BA
CUT_PERMIT CP ,
WHERE
... AND
CB.CUTB_SEQ_NBR = SAC.CUTB_SEQ_NBR AND
BA.CUTB_SEQ_NBR = SAC.CUTB_SEQ_NBR AND
CP.PERM_SEQ_NBR = BA.PERM_SEQ_NBR
What you are in effect saying is that you don't want to modify (or select) every row in SAC. You only want to modify those that have a CUTB_SEQ_NBR value that not only exists in the SP table, but *also* exists in *both* the CB and BA tables. And further, to only those values that not only exist in the BA table, but to only those records in the BA table that also have PERM_SEQ_NBR values that exist in the CP table. We have now quite a few restrictions that my statement did not take into account.
Second, the UPDATE statement I provided was also not restricted to those rows where SAM.SAMM_DIST_APPR_DATE IS NOT NULL. The reasoning was that, since you are updating a column that is already NULL, setting it to NULL was not harmful, and the extra work to do said restriction was likely not worth the effort. This would also have restricted the number of rows in your resultset as compared to mine. But since this obviously confused you, I will add it to my statement.
Now, with the appropriate changes made to both statements, they should select / modify the same number of rows.
Code:
SELECT
CP.PERM_PERMIT_ID ,
CB.CUTB_BLOCK_ID ,
SAC.SILA_STATUS ,
SAC.SILA_COMPLETION_DATE ,
SCA.SICA_ACTIVITY_NAME ,
SAM.SAMM_LICENSEE_APPR_DATE,
SAM.SAMM_DIST_APPR_DATE
FROM
SILVICULTURE_ACTIVITY SAC ,
SILVICULTURE_COMPANY_ACTIVITY SCA ,
SILVICULTURE_PRESCRIPTION SP ,
SILVICULTURE_AMENDMENT SAM ,
CUT_BLOCK CB ,
BLOCK_ALLOCATION BA ,
CUT_PERMIT CP
WHERE
-- When status is P,
SAC.SILA_STATUS = 'P' AND
-- start date is NULL,
SAC.SILA_START_DATE IS NULL AND
SCA.SICA_SEQ_NBR = SAC.SICA_SEQ_NBR AND
-- activity is SP,
SCA.SICA_ACTIVITY_NAME = 'SP' AND
SP.CUTB_SEQ_NBR = SAC.CUTB_SEQ_NBR AND
SAM.SILP_SEQ_NBR = SP.SILP_SEQ_NBR AND
-- amendment number is 0,
SAM.SAMM_AMENDMENT_NBR = '0' AND
-- and target date is not null
SAM.SAMM_DIST_APPR_DATE IS NOT NULL AND
-- Let's change these to outer-joins so they don't restrict anything
CB.CUTB_SEQ_NBR (+)= SAC.CUTB_SEQ_NBR AND
BA.CUTB_SEQ_NBR (+)= SAC.CUTB_SEQ_NBR AND
CP.PERM_SEQ_NBR (+)= BA.PERM_SEQ_NBR
Code:
UPDATE
SILVICULTURE_ACTIVITY SAC
SET
-- Update the start date
SAC.SILA_START_DATE =
(
SELECT
SAM.SAMM_DIST_APPR_DATE
FROM
SILVICULTURE_PRESCRIPTION SP ,
SILVICULTURE_AMENDMENT SAM
WHERE
SP.CUTB_SEQ_NBR = SAC.CUTB_SEQ_NBR AND
SAM.SILP_SEQ_NBR = SP.SILP_SEQ_NBR AND
-- amendment number is 0,
SAM.SAMM_AMENDMENT_NBR = '0' AND
-- and target date is not null
SAM.SAMM_DIST_APPR_DATE IS NOT NULL
)
WHERE
-- When status is P,
SAC.SILA_STATUS = 'P' AND
-- start date is NULL,
SAC.SILA_START_DATE IS NULL AND
-- activity is SP,
SAC.SICA_SEQ_NBR IN
(
SELECT
SCA.SICA_SEQ_NBR
FROM
SILVICULTURE_COMPANY_ACTIVITY SCA
WHERE
SCA.SICA_SEQ_NBR = SAC.SICA_SEQ_NBR AND
SCA.SICA_ACTIVITY_NAME = 'SP'
) AND
EXISTS(
SELECT
1
FROM
SILVICULTURE_PRESCRIPTION SP ,
SILVICULTURE_AMENDMENT SAM
WHERE
SP.CUTB_SEQ_NBR = SAC.CUTB_SEQ_NBR AND
SAM.SILP_SEQ_NBR = SP.SILP_SEQ_NBR AND
-- amendment number is 0,
SAM.SAMM_AMENDMENT_NBR = '0' AND
-- and target date is not null
SAM.SAMM_DIST_APPR_DATE IS NOT NULL
)
As for all the rows getting set to the same date - I don't see how that was possible, unless all those dates in the SAM table just happen to all be the same. Please check the results again and let me know what you find.
Reply With Quote
Originally Posted by amberh
