Link to home
Get AccessLog in
Avatar of Steve A
Steve AFlag for United States of America

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
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This content is only available to members.
To access this content, you must be a member of Experts Exchange.
Get Access
Avatar of Steve A

ASKER

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

Thanks
Avatar of slightwv (䄆 Netminder)
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.
Avatar of Steve A

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);
>>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
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
Avatar of Steve A

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;