Syntax help - 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

Transact-SQL (2000)

Syntax help

Author

Topic

TJ
Posting Yak Master

201 Posts

Posted - 2002-10-28 : 12:51:04

I have the following query:

update irsdata
set procstat = 'i9'
where (select substring(frmdata,(charindex('[0160]', frmdata)+ 6), 1)
       from irsdata
       where right(ntsrsn, 4)= 'trna')
not in ('P', 'T') and procstat = '$$'

It is supposed to update the records that have field data <> p or t.

It works when I run the select query:

select substring(frmdata,(charindex('[0160]', frmdata)+6), 1)
from irsdata
where right(ntsrsn, 4) = 'trna'

There is one record in the table that meets the criteria. The select finds that record and returns the data from the correct field. The update statement updates ALL of the records (160,000+).

What am I missing?

Thanks!
Teresa

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-10-28 : 15:11:15

I don't understand what the subquery is for. Isn't what you want just to test the values in the WHERE clause of the UPDATE?


update irsdata
set procstat = 'i9'
where right(ntsrsn, 4)= 'trna'
  and substring(frmdata,(charindex('[0160]', frmdata)+ 6), 1) not in ('P', 'T')
  and procstat = '$$'

The reason that your original query is updating all rows where procstat='$$' is exactly that there is only one row that matches the subquery. Had there been more matches, the NOT IN would have generated an error since it's expecting a scalar value as its left operand! If you look at the execution plan for your original query, you'll see an Assert to enforce this.

BTW, substring(frmdata,(charindex('[0160]', frmdata)+ 6), 1) will return the sixth character in frmdata if frmdata does not contain [0160]. Is that what you want? If not, putting a nullif around the charindex: nullif(charindex('[0160]', frmdata),0) would fix this (albeit a little bit hackishly).

Edited by - Arnold Fribble on 10/28/2002 15:15:30

TJ
Posting Yak Master

201 Posts

Posted - 2002-10-28 : 15:31:21

quote:

I don't understand what the subquery is for. Isn't what you want just to test the values in the WHERE clause of the UPDATE?

Yes it is.

quote:

BTW, substring(frmdata,(charindex('[0160]', frmdata)+ 6), 1) will return the sixth character in frmdata if frmdata does not contain [0160]. Is that what you want? If not, putting a nullif around the charindex: nullif(charindex('[0160]', frmdata),0) would fix this (albeit a little bit hackishly).

Is there a better way to do this that isn't hackish? I want the update statement to mark records that have the right(ntsrsn, 4) equal to 'trna' and the frmdata string does not contain a '[0160]' as well as those records where the '[0160]' does exist and the data (6th char) doesn't contain a 'P' or a 'T'.

Thanks for your help!
Teresa

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources