Avatar of Machinegunner
Machinegunner
 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_details table, there are like 168k records.
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_Details ard
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

Avatar of undefined
Last Comment
Machinegunner

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
slightwv (䄆 Netminder)

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
Sign up - Free for 7 days
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
Machinegunner

ASKER
Sure, that may work.  Do you have any examples of the MERGE?

Thanks
slightwv (䄆 Netminder)

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.
Machinegunner

ASKER
I tried this and this seemed to not work either:

Update ard
Set     ard.Ard_Risk_Likelyhood = ta.vafmd_Likelyhood
From  Assessment_Response_Details ard,
           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
slightwv (䄆 Netminder)

>>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.
Wasim Akram Shaik

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_Details
Set     ard.Ard_Risk_Likelyhood = (select ta.vafmd_Likelyhood
From  Assessment_Response_Details ard,
           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
Alexandru Ungureanu

My preferred way for updates is using updatable join views. Of course you need additional operation of index creation on lookup table


-- 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;

Open in new window

For more details:
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.
Machinegunner

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_Details table...
Merge Into Assessment_Response_Details ard
  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;