SQL query problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SQL query problem

Author

Topic

Hitesh Shah
Yak Posting Veteran

80 Posts

Posted - 2007-06-06 : 11:54:30

I want to update q field based on following query .
=====================================================
update com_lookup_values_mst_t set lookup_value_group_id =(select top 1 p.lookup_value_id
from
com_lookup_values_mst_t p where p.lookup_id=13 and

patindex('%'+ltrim(rtrim(upper(p.lookup_value_name))) +'%',upper(lookup_value_name))> 0 )
where lookup_id=26

==================================
It gives me the error inner select returns more than 1 value . I am ok if it updates the first value from many selects . How do i proceed .

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-06-06 : 12:40:49

You have a TOP 1 so it should only return 1 record. If you run only the inner query by itself what is the result?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Hitesh Shah
Yak Posting Veteran

80 Posts

Posted - 2007-06-07 : 07:10:05

Sorry . I made a mistake in posting the query .

Here is the query which gives the error (without top 1 )
===============================
update com_lookup_values_mst_t set lookup_value_group_id =(select p.lookup_value_id
from
com_lookup_values_mst_t p where p.lookup_id=13 and

patindex('%'+ltrim(rtrim(upper(p.lookup_value_name))) +'%',upper(lookup_value_name))> 0 )
where lookup_id=26

===============================
Top 1 clause only updates p.looup_value_id regardless of conditions in where code and updates this top code in all occurrences of update clause.

For inner query it returs 0 rows for some rows , 1 row for some and more than 1 row for the others .

Hitesh Shah
Yak Posting Veteran

80 Posts

Posted - 2007-06-07 : 07:47:04

Ifor
Aged Yak Warrior

700 Posts

Posted - 2007-06-07 : 08:34:52

Something like the following may work:

UPDATE L1
SET lookup_value_group_id = L2.lookup_value_id
FROM com_lookup_values_mst_t L1
    JOIN com_lookup_values_mst_t L2
        ON UPPER(L1.lookup_value_name) LIKE '%' + LTRIM(RTRIM(UPPER(L2.lookup_value_name))) + '%'
            AND L2.lookup_id = 13
            AND L1.lookup_id = 26

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-07 : 10:02:16

quote:
Originally posted by Hitesh Shah

I want to update q field based on following query .
=====================================================
update com_lookup_values_mst_t set lookup_value_group_id =(select top 1 p.lookup_value_id
from
com_lookup_values_mst_t p where p.lookup_id=13 and

patindex('%'+ltrim(rtrim(upper(p.lookup_value_name))) +'%',upper(lookup_value_name))> 0 )
where lookup_id=26

==================================
It gives me the error inner select returns more than 1 value . I am ok if it updates the first value from many selects . How do i proceed .

Did you run this query?

Madhivanan

Failing to plan is Planning to fail

Hitesh Shah
Yak Posting Veteran

80 Posts

Posted - 2007-06-08 : 00:20:27

It gave me the error internal select returns more than 1 row . When i used top 1 , it ran but with wrong results . It updated top p.lookup_value_id without considering the patindex condition .

I did check internal query with for sample records . It gave me correct results .

Currently I have resolved this temporarily with manual work . But I think I need to learn more about SQL . I think there are many ways to do the same thing .

Nevertheless thanks for giving good response to this post .

quote:
Originally posted by madhivanan

quote:
Originally posted by Hitesh Shah

It gives me the error inner select returns more than 1 value . I am ok if it updates the first value from many selects . How do i proceed .

Did you run this query?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources