?
Solved

ORACLE SQL UPDATE

Posted on 2013-01-10
7
Medium Priority
?
440 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 35

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 35

Accepted Solution

by:
johnsone earned 1000 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 1000 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
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
LVL 35

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
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 configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

809 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