Home » SQL & PL/SQL » SQL & PL/SQL » update column and text search (merged)
update column and text search (merged) [message #326062] Tue, 10 June 2008 01:47 Go to next message
ramya29p
Messages: 100
Registered: November 2007
Location: Chennai
Senior Member
hi,i am trying to search the text ..

 SQL> select * from e4;

NAME                 CLASS
-------------------- --------------------
ramya
nandhini
akshaya
danya

SQL> select name from e4 where contains(name,'%dan%',1)>0;

no rows selected

SQL> select name from e4 where contains(name,'%nan%',1)>0;

NAME
--------------------
nandhini


if i try to select the name which contains the text 'dan' the query returns no record.can any one tell me what is the reason?

but if i select by using like keyword it is working

SQL> select name from e4 where name like '%dan%';

NAME
--------------------
danya
Re: Text Search [message #326064 is a reply to message #326062] Tue, 10 June 2008 01:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Remove % in contains expressions.

Regards
Michel
Re: Text Search [message #326081 is a reply to message #326064] Tue, 10 June 2008 02:28 Go to previous messageGo to next message
ramya29p
Messages: 100
Registered: November 2007
Location: Chennai
Senior Member
Hi, i tried by removing the % then also it is not displaying

  1* select name from e4 where contains(name,'dan',1)>0
SQL> /

no rows selected
Update the Column [message #326103 is a reply to message #326062] Tue, 10 June 2008 03:51 Go to previous messageGo to next message
ramya29p
Messages: 100
Registered: November 2007
Location: Chennai
Senior Member
Hi,i have 2 tables

SQL> select * from e3;

KEYWORD              CLASS
-------------------- --------------------
nan                  cse
ram                  ece
dan                  it

SQL> select * from e4;

NAME                 CLASS
-------------------- --------------------
ramya
nandhini
akshaya
danya


If the name field of table e4 contains the keyword from the table e3 then i need to update the class field of e4 with class field from e3...
can anyone tell me how to do it
Re: Update the Column [message #326111 is a reply to message #326103] Tue, 10 June 2008 04:21 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
For now keep the update statement in the backburner. Can you think of how you will do it in a select statement? We will take it from there. For how to do it in a select statement check this link.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm#sthref3157

Regards

Raj
Re: Text Search [message #326125 is a reply to message #326081] Tue, 10 June 2008 04:53 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
That would be because your column does not contain the word 'dan'.

Try
select name from e4 where instr(name,'dan',1)>0
Re: Update the Column [message #326146 is a reply to message #326103] Tue, 10 June 2008 05:48 Go to previous messageGo to next message
sarwagya
Messages: 87
Registered: February 2008
Location: Republic of Nepal
Member
Try this:
UPDATE table2
SET class = (SELECT class
		FROM table1
		WHERE INSTR(table2.name,keyword) <> 0;
Re: Update the Column [message #326150 is a reply to message #326146] Tue, 10 June 2008 06:08 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Have you tested the code what you have posted ? Also can you explain how your code works.

Regards

Raj

[Updated on: Tue, 10 June 2008 06:08]

Report message to a moderator

Re: Update the Column [message #326155 is a reply to message #326103] Tue, 10 June 2008 06:36 Go to previous messageGo to next message
Martin Eysackers
Messages: 80
Registered: October 2005
Location: Belgium
Member
first the OP should at least try to write a query which gives this as output, we can take it from there

it's always a good idea to try it yourself rather than copy/paste from a forum Smile

NAME     NAM CLA
-------- --- ---
ramya    ram ece
nandhini nan cse
danya    dan it
Re: Update the Column [message #326158 is a reply to message #326150] Tue, 10 June 2008 06:53 Go to previous messageGo to next message
sarwagya
Messages: 87
Registered: February 2008
Location: Republic of Nepal
Member
That was just a hint. I haven't tested it.
Quote:
If the name field of table e4 contains the keyword from the table e3 then i need to update the class field of e4 with class field from e3...


In my code, table1 is table e3 and table2 is table e4.

while updating a value in table2, it checks if there is any matching keyword in table1.
If there is, it updates it with the class field of table1.
Re: Update the Column [message #326162 is a reply to message #326146] Tue, 10 June 2008 07:05 Go to previous messageGo to next message
ttparavindh
Messages: 22
Registered: December 2006
Location: Bangalore,India
Junior Member

following output also match for this query.
1 UPDATE e4
2 SET class =(SELECT class
3 FROM e3
4* WHERE INSTR(e4.name,keyword) <>0)
SQL> /

4 rows updated.

SQL> select * from e4;

NAME CLASS
------- ------
ramya ece
nandhini cse
akshaya
danya it

Best regards.
Re: Update the Column [message #326164 is a reply to message #326162] Tue, 10 June 2008 07:10 Go to previous messageGo to next message
sarwagya
Messages: 87
Registered: February 2008
Location: Republic of Nepal
Member
Great Job!

You are the first person to land on moon.
Re: Update the Column [message #326170 is a reply to message #326162] Tue, 10 June 2008 07:20 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Add the following row and try the update again...

INSERT INTO E4 VALUES( 'ramdan', null);
Re: Update the Column [message #326171 is a reply to message #326146] Tue, 10 June 2008 07:22 Go to previous messageGo to next message
sarwagya
Messages: 87
Registered: February 2008
Location: Republic of Nepal
Member
UPDATE table2
SET class = (SELECT class
		FROM table1
		WHERE INSTR(table2.name,keyword) <> 0)
WHERE EXISTS(SELECT class
		FROM table1
		WHERE INSTR(table2.name,keyword) <> 0)
Re: Update the Column [message #326172 is a reply to message #326164] Tue, 10 June 2008 07:23 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
sarwagya wrote on Tue, 10 June 2008 13:10
Great Job!

You are the first person to land on moon.

Nope, Buzz Aldrin (spelling?) was the first to land on the moon (of course Niel Armstrong was the first to walk on the surface, but Buzz was the 'pilot' so he landed Smile
Re: Update the Column [message #326173 is a reply to message #326170] Tue, 10 June 2008 07:24 Go to previous messageGo to next message
sarwagya
Messages: 87
Registered: February 2008
Location: Republic of Nepal
Member
Quote:
Add the following row and try the update again...

INSERT INTO E4 VALUES( 'ramdan', null);

That depends on the requirement.

[Updated on: Tue, 10 June 2008 07:26]

Report message to a moderator

Re: Update the Column [message #326174 is a reply to message #326173] Tue, 10 June 2008 07:28 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Do you reckon it can be guaranteed to not happen? Bet it can't.

[Updated on: Tue, 10 June 2008 07:28]

Report message to a moderator

Re: Update the Column [message #326177 is a reply to message #326174] Tue, 10 June 2008 07:31 Go to previous messageGo to next message
ramya29p
Messages: 100
Registered: November 2007
Location: Chennai
Senior Member
Thanks a lot...
Re: Update the Column [message #326205 is a reply to message #326177] Tue, 10 June 2008 12:06 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
If the length of your e4.name is short, then using instr may be the most efficient method. However, I noticed that you were originally using a query with contains. If your name field is very long and you have a text application, then you may get better performance from using contains with a context index that has a basic_wordlist with a substring_index. Your original query syntax in your first post in this thread was correct. However, there may be things in your index creation statement and associated preferences that you did not post that were preventing you from getting the expected results. For example, if the word danya was in a stoplist that was used in the index parameters, that would explain it. There are other possibilities, but I would need to see the index creation statement and associated preferences to determine that. So, you should not necessarily abandon the context approach you began with. Below, I have provided an example that produces the desired results, followed by another example that produces what you originally posted, due to the presence of a stopword.

-- test data:
SCOTT@orcl_11g> CREATE TABLE e3
  2    (keyword  VARCHAR2 (15),
  3  	class	 VARCHAR2 (15))
  4  /

Table created.

SCOTT@orcl_11g> INSERT ALL
  2  INTO e3 (keyword, class) VALUES ('nan', 'cse')
  3  INTO e3 (keyword, class) VALUES ('ram', 'ece')
  4  INTO e3 (keyword, class) VALUES ('dan', 'it')
  5  SELECT * FROM DUAL
  6  /

3 rows created.

SCOTT@orcl_11g> CREATE TABLE e4
  2    (name  VARCHAR2 (15),
  3  	class VARCHAR2 (15))
  4  /

Table created.

SCOTT@orcl_11g> INSERT ALL
  2  INTO e4 (name) VALUES ('ramya')
  3  INTO e4 (name) VALUES ('nandhini')
  4  INTO e4 (name) VALUES ('akshaya')
  5  INTO e4 (name) VALUES ('danya')
  6  SELECT * FROM DUAL
  7  /

4 rows created.

SCOTT@orcl_11g> 


-- example that produce the desired results:
SCOTT@orcl_11g> EXEC CTX_DDL.CREATE_PREFERENCE ('e4_wordlist', 'BASIC_WORDLIST')

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> EXEC CTX_DDL.SET_ATTRIBUTE ('e4_wordlist', 'SUBSTRING_INDEX', 'YES')

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> CREATE INDEX e4_name_idx ON e4 (name)
  2  INDEXTYPE IS CTXSYS.CONTEXT
  3  PARAMETERS
  4    ('WORDLIST e4_wordlist')
  5  /

Index created.

SCOTT@orcl_11g> SELECT token_text FROM dr$e4_name_idx$i
  2  /

TOKEN_TEXT
----------------------------------------------------------------
AKSHAYA
DANYA
NANDHINI
RAMYA

SCOTT@orcl_11g> SELECT name
  2  FROM   e4
  3  WHERE  CONTAINS (name, '%dan%', 1) > 0
  4  /

NAME
---------------
danya

SCOTT@orcl_11g> SELECT name
  2  FROM   e4
  3  WHERE  CONTAINS (name, '%nan%', 1) > 0
  4  /

NAME
---------------
nandhini

SCOTT@orcl_11g> UPDATE e4 e4a
  2  SET    class =
  3  	    (SELECT e3.class
  4  	     FROM   e3, e4 e4b
  5  	     WHERE  CONTAINS (e4b.name, '%' || e3.keyword || '%') > 0
  6  	     AND    e4b.name = e4a.name)
  7  WHERE  EXISTS
  8  	    (SELECT e3.class
  9  	     FROM   e3, e4 e4b
 10  	     WHERE  CONTAINS (e4b.name, '%' || e3.keyword || '%') > 0
 11  	     AND    e4b.name = e4a.name)
 12  /

3 rows updated.

SCOTT@orcl_11g> SELECT * FROM e4
  2  /

NAME            CLASS
--------------- ---------------
ramya           ece
nandhini        cse
akshaya
danya           it

SCOTT@orcl_11g> 


-- reset the values for second test:
SCOTT@orcl_11g> ROLLBACK
  2  /

Rollback complete.

SCOTT@orcl_11g> SELECT * FROM e3
  2  /

KEYWORD         CLASS
--------------- ---------------
nan             cse
ram             ece
dan             it

SCOTT@orcl_11g> SELECT * FROM e4
  2  /

NAME            CLASS
--------------- ---------------
ramya
nandhini
akshaya
danya

SCOTT@orcl_11g> DROP INDEX e4_name_idx
  2  /

Index dropped.

SCOTT@orcl_11g> 


-- example using stoplist that produces what you got:
SCOTT@orcl_11g> EXEC CTX_DDL.CREATE_STOPLIST ('e4_stop', 'BASIC_STOPLIST')

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> EXEC CTX_DDL.ADD_STOPWORD ('e4_stop', 'danya')

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> CREATE INDEX e4_name_idx ON e4 (name)
  2  INDEXTYPE IS CTXSYS.CONTEXT
  3  PARAMETERS
  4    ('WORDLIST e4_wordlist
  5  	 STOPLIST e4_stop')
  6  /

Index created.

SCOTT@orcl_11g> SELECT token_text FROM dr$e4_name_idx$i
  2  /

TOKEN_TEXT
----------------------------------------------------------------
AKSHAYA
NANDHINI
RAMYA

SCOTT@orcl_11g> SELECT name
  2  FROM   e4
  3  WHERE  CONTAINS (name, '%dan%', 1) > 0
  4  /

no rows selected

SCOTT@orcl_11g> SELECT name
  2  FROM   e4
  3  WHERE  CONTAINS (name, '%nan%', 1) > 0
  4  /

NAME
---------------
nandhini

SCOTT@orcl_11g> UPDATE e4 e4a
  2  SET    class =
  3  	    (SELECT e3.class
  4  	     FROM   e3, e4 e4b
  5  	     WHERE  CONTAINS (e4b.name, '%' || e3.keyword || '%') > 0
  6  	     AND    e4b.name = e4a.name)
  7  WHERE  EXISTS
  8  	    (SELECT e3.class
  9  	     FROM   e3, e4 e4b
 10  	     WHERE  CONTAINS (e4b.name, '%' || e3.keyword || '%') > 0
 11  	     AND    e4b.name = e4a.name)
 12  /

2 rows updated.

SCOTT@orcl_11g> SELECT * FROM e4
  2  /

NAME            CLASS
--------------- ---------------
ramya           ece
nandhini        cse
akshaya
danya

SCOTT@orcl_11g> 

Re: Update the Column [message #327337 is a reply to message #326205] Mon, 16 June 2008 02:29 Go to previous messageGo to next message
ramya29p
Messages: 100
Registered: November 2007
Location: Chennai
Senior Member
Hi, If i'm Trying to do with large data the updation fails,can anyone please tell me how to acheive this ?

SQL> select count(*) from key;

  COUNT(*)
----------
       184

SQL> select count(*) from name1;

  COUNT(*)
----------
      2240

SQL> ed 
Wrote file afiedt.buf

  1  UPDATE name1 a1
  2     SET    classification =
  3                 (SELECT a.classification
  4                  FROM   key a, name1 b
  5                  WHERE  CONTAINS (b.business_name, '%' || a.keyword || '%') > 0
  6                  AND    b.business_name = a1.business_name)
  7       WHERE  EXISTS
  8                 (SELECT a.classification
  9                  FROM   key a, name1 b
 10                  WHERE  CONTAINS (b.business_name, '%' || a.keyword || '%') > 0
 11*         AND    b.business_name = a1.business_name)
SQL> /
            (SELECT a.classification
             *
ERROR at line 3:
ORA-01427: single-row subquery returns more than one row
Re: Update the Column [message #327344 is a reply to message #327337] Mon, 16 June 2008 02:59 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Since you order the database to set the column "classification" to a certain value, in this case to the result of the "select a.classification" part, you have to make sure that this select returns only one value. How else would the database decide which value should be used?
Add a where-clause to that select to make sure there is only one row returned. If all rows that are returned for a specific value already contain the same value, you can add a distinct.
Re: Update the Column [message #327351 is a reply to message #327344] Mon, 16 June 2008 03:25 Go to previous messageGo to next message
ramya29p
Messages: 100
Registered: November 2007
Location: Chennai
Senior Member
Hi,
I tried by giving Distinct then also i am getting the same error
Re: Update the Column [message #327353 is a reply to message #327351] Mon, 16 June 2008 03:32 Go to previous messageGo to next message
sarwagya
Messages: 87
Registered: February 2008
Location: Republic of Nepal
Member
You should ensure that the inner query returns only one value.
Re: Update the Column [message #327354 is a reply to message #327351] Mon, 16 June 2008 03:32 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
READ my reply, not just pick out a single part
Previous Topic: One-time-only procedure doubt
Next Topic: Reg KUP-04063 error while using External tables
Goto Forum:
  


Current Time: Sun Dec 11 04:07:13 CST 2016

Total time taken to generate the page: 0.09425 seconds