How to match the longest string and update the value?

Question

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

You have two problems, a) the subselect returns more than one row; `A.stack1 in (` should fix that. b) You cannot update a table that has a value that will be used in a subselect (this may only be if your subselect includes the table you are updating). — Suroot, Jun 17 '11 at 03:36

score 1 · Accepted Answer · answered Jun 17 '11 at 03:39

1

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.

answered Jun 17 '11 at 03:39

Thilo

257,207
101
511
656

Thank you so much, It works:) How to mark this question as answered? – sailaja Jun 17 '11 at 03:43
@sailaja: Click the checkmark, found under the arrows on the left side of the answer. – OMG Ponies Jun 17 '11 at 03:45
MAX does not return the longest, it returns the maximum value. You need to return stack2 from table2 in the subquery where the length matches the MAX length of stack2 in table2. (You will still need MAX or MIN on the returned value because there may be more than 1 string that has the maximum length.) – Datajam Jun 17 '11 at 09:06
@Datajam Ltd: MAX returns the longest string if they all share the same prefix (as they do here). – Thilo Jun 18 '11 at 01:39

OMG Ponies · Answer 2 · 2011-06-17T03:42:56.713

0

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.

edited Jun 17 '11 at 03:42

answered Jun 17 '11 at 03:36

OMG Ponies

325,700
82
523
502

with this query an getting ORA-00927: missing equal sign error – sailaja Jun 17 '11 at 03:40

How to match the longest string and update the value?

2 Answers2