CODEWITHSUNDEEP
sqloracle-databaseora-01427
sailaja
sailaja

Reputation: 429

How to match the longest string and update the value?

I need to compare and match the longest match of two strings in two different tables and update one values if there is a closest match.

Table 1     Table 2
stack1     stack2
ABCDEFG    ABC
GHIJKLM    ABCDE
PQRSUVW    ABCDEF

I need to compare these two tables and match the closeet one and update Table 1 first row as ABCDEF the closest match, Please can anyone help me out. I am stuck here.

Here is my query

UPDATE table1 A 
   SET A.stack1 = (SELECT DISTINCT B.stack2 
                     FROM table2 B 
                    WHERE A.stack1 LIKE CONCAT(B.stack2,'%')) 
 WHERE name = 'name';

with this query am getting an error called

ORA-01427: single-row subquery returns more than one row

Upvotes: 1

Views: 944

Answers (2)

OMG Ponies
OMG Ponies

Reputation: 332691

The ORA-01427 error is saying the subquery is returning more than one value -- even with the DISTINCT.

You need to correct the case(s) that are returning more than one distinct value. That could be with an aggregate function, like MAX or MIN, but without details I hesitate to make that recommendation.

Upvotes: 0

Thilo
Thilo

Reputation: 262774

You need to make the subquery return only a single match (the longest one). In your case MAX should do that.

UPDATE table1 A 
SET A.stack1 = (SELECT Max( B.stack2 )
                 FROM table2 B 
                WHERE A.stack1 LIKE CONCAT(B.stack2,'%')) 
WHERE name = 'name';

Also, you should think about the case where nothing matches.

Upvotes: 1

Related Questions