Updating specific rows

Machinegunner
Machinegunner used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
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

Author

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

Thanks
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
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.
Ensure you’re charging the right price for your IT

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!

Author

Commented:
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);
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>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.
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 UngureanuHead of Data Management & Reporting Team

Commented:
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

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial