Solved

ORACLE SQL UPDATE

Posted on 2013-01-10
7
431 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 31

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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
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 31

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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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 set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

707 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