Update statement by avoiding the duplicated regardless of case sensitivity

Toad for Oracle & DevOps Toolkit
1

Hi Masters,

I need a help in updating a table where by if we find similar pattern of data that should not be updated but the remaining should be updated. Say For Example . In the below data all data that are like ST00 should be treated as similar which I mean to say case sensitivity should be avoided. But if we come to k100 it should be treated as single item and should be converted to upper case. There can be any combination case of data in the stock_code. Lik rC00, Cr00, cr00 like that. Any help is much appreciated. Thanks

CREATE TABLE DEMOSYS(STOCK_CODE CHAR(4));

INSERT INTO DEMOSYS VALUES(‘ST00’);

INSERT INTO DEMOSYS VALUES(‘st00’);

INSERT INTO DEMOSYS VALUES(‘sT00’);

INSERT INTO DEMOSYS VALUES(‘St00’);

INSERT INTO DEMOSYS VALUES(‘k100’);

2

Hi Mohammed.

If you only want to update those rows that are unique, then the following should work, but please test first!

Update demosys

Set stock_code = upper (stock_code)

Where lower (stock_code) in (

Select sc from (

Select lower (stock_code) as sc,

Count (*)

From demosys

Group by lower (stock_code)

Having count (*) = 1)

);

Cheers,

Norm [TeamT]

On 21 October 2014 02:34:34 BST, Mohammedtodd bounce-Mohammedtodd@toadworld.com wrote:

Update statement by avoiding the duplicated regardless of case sensitivity

Thread created by Mohammedtodd
Hi Master,

I need a help in updating a table where by if we find similar pattern of data that should not be updated but the remaining should be updated. Say For Example . In the below data all data that are like ST00 should be treated as similar which I mean to say case sensitivity should be avoided. But if we come to k100 it should be treated as single item and should be converted to upper case. There can be any combination case of data in the stock_code. Lik rC00, Cr00, cr00 like that. Any help is much appreciated. Thanks

CREATE TABLE DEMOSYS(STOCK_CODE CHAR(4));

INSERT INTO DEMOSYS VALUES('ST00');

INSERT INTO DEMOSYS VALUES('st00');

INSERT INTO DEMOSYS VALUES('sT00');

INSERT INTO DEMOSYS VALUES('St00');

INSERT INTO DEMOSYS VALUES('k100');

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or Unsubscribe from Oracle notifications altogether.

Toad World - Oracle Discussion Forum

Flag this post as spam/abuse.

--

Sent from my Android device with K-9 Mail. Please excuse my brevity.

3

Thank you for your advise.