Home » SQL & PL/SQL » SQL & PL/SQL » update query (oracle 10g)
update query [message #572194] Fri, 07 December 2012 04:03 Go to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
Hi I want to make flag value as 'N' if matches the below condition and if it fails needs to make flag as 'Y'.
how to update a single column value as multiples as 'Y' and 'N'.
 UPDATE T_ABC A
SET A.FLAG       ='N'
  WHERE A.PRO_ID=P_PRO_ID
AND EXISTS
  (SELECT '1'
     FROM T_ERROR B
    WHERE B.PRO_ID=A.PRO_ID
  AND A.SHEET_NO      = B.SHEET_NO
  AND A.ROW_NO        = B.ROW_NO
  AND A.EMPLOYEE_NO   =B.REF_COL_VALUE
  AND B.error_code   IN
    (SELECT bus_code
       FROM T_VAL
      WHERE p_code = P_PRODUCT_CODE
    )
  );
Re: update query [message #572196 is a reply to message #572194] Fri, 07 December 2012 04:23 Go to previous messageGo to next message
Michel Cadot
Messages: 58481
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
With any SQL or PL/SQL question, please, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

And if you want from those that helped you many times in the past you should envisage to feedback and thank them.
Waiting for you...

Regards
Michel
Re: update query [message #572228 is a reply to message #572194] Fri, 07 December 2012 10:09 Go to previous message
LKBrwn_DBA
Messages: 424
Registered: July 2003
Location: WPB, FL
Senior Member
Try using NVL() function like this:
UPDATE t_abc a
   SET a.flag      =
         NVL (
              (SELECT 'N'
                 FROM t_error b
                WHERE b.pro_id = a.pro_id
                  AND a.sheet_no = b.sheet_no
                  AND a.row_no = b.row_no
                  AND a.employee_no = b.ref_col_value
                  AND b.ERROR_CODE IN (SELECT bus_code
                                         FROM t_val
                                        WHERE p_code = p_product_code))
            , 'Y'
             )
 WHERE a.pro_id = p_pro_id
;

[Updated on: Fri, 07 December 2012 10:12] by Moderator

Report message to a moderator

Previous Topic: types of pl/sql value or numric error.
Next Topic: OPENING AND CLOSING BALANCE
Goto Forum:
  


Current Time: Tue Jul 22 06:35:19 CDT 2014

Total time taken to generate the page: 0.10357 seconds