asked on
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
Oracle DatabaseProgramming Languages-Other.NET Programming
Last Comment
Machinegunner
ASKER CERTIFIED SOLUTION
Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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.
ASKER
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);
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
>>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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
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;
Thanks