Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
Updating specific rows
Hello,
I have an update query that seems to be updating the records in reverse.
I am trying to update a column that has a value of '0' or a NULL.
I need to update these rows with a default value from a lookup table.
So when I run the below query, it just does the opposite! Even with all the joins,
to ensure a specific record does get updated.
I created a temporary table that holds all of the rows that have a - ard_risk_likelyhood
of '0' or NULL.
On the assessment_response_detail
On the temp table, there are like 7k records or rows with a '0' or NULL.
So of the 168k records, there is a value other than '0' or NULL, so these records are good and should not be updated, only those 7k records in question.
When I run the update below, it is updating the 168k records to NULL or the
ard_risk_likelyhood column to null!
What do I need to change, to ensure only the 7k records get updated.
Thanks,
Update Assessment_Response_Detail
Set ard.Ard_Risk_Likelyhood =
(Select Distinct ta.vafmd_Likelyhood
From tmp_assessments ta
Where ta.ard_id = ard.ard_id
And ta.ard_aqb_id = ard.ard_aqb_id
And ta.ard_arh_id = ard.ard_arh_id
And ard.Ard_Risk_Likelyhood In (0, null));
Using - Windows OS and Oracle 11g
I have an update query that seems to be updating the records in reverse.
I am trying to update a column that has a value of '0' or a NULL.
I need to update these rows with a default value from a lookup table.
So when I run the below query, it just does the opposite! Even with all the joins,
to ensure a specific record does get updated.
I created a temporary table that holds all of the rows that have a - ard_risk_likelyhood
of '0' or NULL.
On the assessment_response_detail
On the temp table, there are like 7k records or rows with a '0' or NULL.
So of the 168k records, there is a value other than '0' or NULL, so these records are good and should not be updated, only those 7k records in question.
When I run the update below, it is updating the 168k records to NULL or the
ard_risk_likelyhood column to null!
What do I need to change, to ensure only the 7k records get updated.
Thanks,
Update Assessment_Response_Detail
Set ard.Ard_Risk_Likelyhood =
(Select Distinct ta.vafmd_Likelyhood
From tmp_assessments ta
Where ta.ard_id = ard.ard_id
And ta.ard_aqb_id = ard.ard_aqb_id
And ta.ard_arh_id = ard.ard_arh_id
And ard.Ard_Risk_Likelyhood In (0, null));
Using - Windows OS and Oracle 11g
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
An update statement without a where clause will update ALL rows in a table.
Would a MERGE work better for you?
http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_9016.htm#SQLRF01606
Would a MERGE work better for you?
http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_9016.htm#SQLRF01606
The doc link I posted has examples in it.
There are tons of examples out there. They are as good as anything I can make up.
Now if you could provide a small test case based on your example described above, we can probably take that and provide a working example based on it.
There are tons of examples out there. They are as good as anything I can make up.
Now if you could provide a small test case based on your example described above, we can probably take that and provide a working example based on it.
I tried this and this seemed to not work either:
Update ard
Set ard.Ard_Risk_Likelyhood = ta.vafmd_Likelyhood
From Assessment_Response_Detail
tmp_assessments ta
Where ta.ard_id = ard.ard_id
And ta.ard_aqb_id = ard.ard_aqb_id
And ta.ard_arh_id = ard.ard_arh_id
And ard.Ard_Risk_Likelyhood In (0, null);
Update ard
Set ard.Ard_Risk_Likelyhood = ta.vafmd_Likelyhood
From Assessment_Response_Detail
tmp_assessments ta
Where ta.ard_id = ard.ard_id
And ta.ard_aqb_id = ard.ard_aqb_id
And ta.ard_arh_id = ard.ard_arh_id
And ard.Ard_Risk_Likelyhood In (0, null);
>>I tried this and this seemed to not work either:
That syntax looks weird to me for Oracle. Not saying it isn't valid syntax. Just never seen an update written that way so cannot say why it didn't work.
That syntax looks weird to me for Oracle. Not saying it isn't valid syntax. Just never seen an update written that way so cannot say why it didn't work.
Are you looking for sub query inner join in an update statement??
you may have to work on the join condition to satisfy your requirement, below is a sample illustration,
Update Assessment_Response_Detail
Set ard.Ard_Risk_Likelyhood = (select ta.vafmd_Likelyhood
From Assessment_Response_Detail
tmp_assessments ta
Where ta.ard_id = ard.ard_id
And ta.ard_aqb_id = ard.ard_aqb_id
And ta.ard_arh_id = ard.ard_arh_id
And ard.Ard_Risk_Likelyhood In (0, null));
more examples of update statements can be found in PSOUG.org
http://psoug.org/reference/update.html
you may have to work on the join condition to satisfy your requirement, below is a sample illustration,
Update Assessment_Response_Detail
Set ard.Ard_Risk_Likelyhood = (select ta.vafmd_Likelyhood
From Assessment_Response_Detail
tmp_assessments ta
Where ta.ard_id = ard.ard_id
And ta.ard_aqb_id = ard.ard_aqb_id
And ta.ard_arh_id = ard.ard_arh_id
And ard.Ard_Risk_Likelyhood In (0, null));
more examples of update statements can be found in PSOUG.org
http://psoug.org/reference/update.html
My preferred way for updates is using updatable join views. Of course you need additional operation of index creation on lookup table
http://docs.oracle.com/cd/B28359_01/server.111/b28313/usingpe.htm#i1009319
-- STEP 1
ALTER TABLE LOOKUP_TABLE ADD CONSTRAINT LK_PID_U UNIQUE (PID);
-- STEP 2
UPDATE (SELECT T1.COL_NAME, LK.LK_COL_NAME
FROM BASE_TABLE T1, LOOKUP_TABLE LK
WHERE T1.PID = LK.PID
AND (T1.CONTROL_COL IS NULL OR T1.CONTROL_COL = 0)
)
SET COL_NAME = LK_COL_NAME;
http://docs.oracle.com/cd/B28359_01/server.111/b28313/usingpe.htm#i1009319
Update - I was able to use the Merge statement and was successful in updating the 240k rows within the specific table. Thanks for the information.
Here is the Merge statement for solution knowledge.
Thanks
--Merge update on the Assessment_Response_Detail
Merge Into Assessment_Response_Detail
Using (Select vard_ard_id, afd_fm_id, vafmd_likelyhood, vafmd_severity From tmp_assessments) ta
On (ard.ard_id = ta.vard_ard_id)
When Matched Then Update
Set ard.ard_risk_severity = ta.vafmd_severity,
ard.ard_risk_likelyhood = ta.vafmd_likelyhood,
ard.ard_fm_id = ta.afd_fm_id;
Here is the Merge statement for solution knowledge.
Thanks
--Merge update on the Assessment_Response_Detail
Merge Into Assessment_Response_Detail
Using (Select vard_ard_id, afd_fm_id, vafmd_likelyhood, vafmd_severity From tmp_assessments) ta
On (ard.ard_id = ta.vard_ard_id)
When Matched Then Update
Set ard.ard_risk_severity = ta.vafmd_severity,
ard.ard_risk_likelyhood = ta.vafmd_likelyhood,
ard.ard_fm_id = ta.afd_fm_id;
Premium Content
You need an Expert Office subscription to comment.Start Free Trial