ORACLE SQL UPDATE

I have a procedure that will be passed in a value that will need to do an update to a field that is PIPE delimited

Here is the value to be updated
TRACK - RED BANK|TRACK - NEW STATION

If the value being sent in matches the left portion of the PIPE then I need to Delete each record in the table
>Row Deleted
If the value being sent in matches the right side of the PIPE then I need to Update all records to the right of the PIPE with NONE
> column Updated to 'TRACK - RED BANK|NONE'

Thanks everyone
jknj72Asked:
Who is Participating?
 
johnsoneConnect With a Mentor Senior Oracle DBACommented:
Making it into a procedure, it looks like this:

CREATE OR replace PROCEDURE Myproc(check_val VARCHAR) 
AS 
BEGIN 
    UPDATE mytab 
    SET    col = Substr(col, 1, Instr(col, '|')) 
                 || 'NONE' 
    WHERE  col LIKE '%|' 
                    || check_val; 

    DELETE mytab 
    WHERE  col LIKE check_val 
                    || '|%'; 
END; 

/ 

Open in new window


Again, you still have to get the correct table and column names in there.
0
 
johnsoneSenior Oracle DBACommented:
This statement will handle the update:

UPDATE mytab 
SET    col = Substr(col, 1, Instr(col, '|')) 
             || 'NONE' 
WHERE  col LIKE '%|TRACK - NEW STATION';

Open in new window


This statement will handle the delete:

DELETE mytab 
WHERE  col LIKE 'TRACK - NEW STATION|%';

Open in new window


If you incorporate both of those statements into your procedure and use the correct table and column names, then just add your variable instead of the hard coded values, it should work just fine.
0
 
awking00Connect With a Mentor Commented:
create or replace procedure updt_del_proc(p_val in varchar2) is
begin
 update yourtable set yourcol = replace(yourcol,p_val,'NONE')
 where instr(yourcol,p_val) > 1;
 delete from yourtable where instr(yourcol,p_val) = 1;
end;
/
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
johnsoneSenior Oracle DBACommented:
awking00,

I don't think that would work.

If you were looking for 'abc' and the column contained 'dabc|queid' it would get updated and I don't think that is what is intended as the abc is not after the |.

Additionally, if you were looking for 'abc' and the column contained 'abcd|jdie' it would get deleted and I don't think that is what is intended as abc is not the complete field before the |.
0
 
awking00Commented:
johnsone,
The question stated if the value "matches" the left or right portions, which I assumed did not mean if the left or right portion contains the value. Perhaps jknj72 can clarify that.
0
 
jknj72Author Commented:
This is what I came up with and it works fine....

--Delete all rows where vOldText is equal to the left side of the PIPE '|' in Lookup_TAG field
        DELETE from OMNI_COMMON_LOOKUP
        WHERE SUBSTR(LOOKUP_TAG,0,INSTR(LOOKUP_TAG,'|')-1)= vOldText

--Update all rows where vOldText = right side of PIPE | and replace the right side with NONE
        UPDATE  OMNI_COMMON_LOOKUP
        SET LOOKUP_TAG = SUBSTR(LOOKUP_TAG,0,INSTR(LOOKUP_TAG, '|')-1) || '|NONE'
        WHERE SUBSTR(LOOKUP_TAG,INSTR(LOOKUP_TAG,'|')+1) = vOldText


FYI, it needs to be an exact match on either side and unfortunately I dont have time to test each one of your solutions to see who should get the points so Im gonna split them with both of you. Thank you both for your help
0
 
jknj72Author Commented:
Thank you both
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.