Solved

ORACLE SQL UPDATE

Posted on 2013-01-10
7
432 Views
Last Modified: 2013-01-11
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
0
Comment
Question by:jknj72
  • 3
  • 2
  • 2
7 Comments
 
LVL 34

Expert Comment

by:johnsone
ID: 38764393
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
 
LVL 34

Accepted Solution

by:
johnsone earned 250 total points
ID: 38764418
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
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 250 total points
ID: 38764704
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 34

Expert Comment

by:johnsone
ID: 38764776
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
 
LVL 32

Expert Comment

by:awking00
ID: 38766930
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
 

Author Comment

by:jknj72
ID: 38767052
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
 

Author Closing Comment

by:jknj72
ID: 38767080
Thank you both
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now