update irsdata 
set procstat = 'I9'
where ((select charindex('[007]', frmdata, 5)
        from tempbatch 
        where left(frmheader, 3) = 'RET')<> '30401') 
      and (ntsbatch = 'zzp30401')

Server: Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated.

quote:

---

What are you trying to do with this query?

---

quote:

---

quote:

---

What are you trying to do with this query?

---

I need all 'RET' records that do not have a '30401' in their frmdata
column to fail. The 'batch' of records may have five records or 50,
with one of them being a 'ret' or many of them being a 'ret'.

Thanks for responding so quickly!
Teresa

Edited by - tj on 11/06/2002 11:51:08

---

quote:

---

Okay but you are updating irsdata and looking at tempbatch, so I'm not sure how these two relate to each other.

---

quote:

---

quote:

---

Okay but you are updating irsdata and looking at tempbatch, so I'm not sure how these two relate to each other.

---

Yes. I'm comparing irsdata to tempbatch and updating irsdata.

Teresa

---

quote:

---

I'd try out what I posted above as a SELECT to see if you get the rows you are looking for in the results. You just need to figure out what the something = something join is between irsdata and tempbatch, which is the bit I can't tell.

---

I've been working on this code while we posted back and forth.

update irsdata 
set procstat = 'I9'
from irsdata
  inner join 
    (select irsdataid,ntsbatch, frmheader, substring(frmdata,(charindex('[007]', frmdata)+ 12), 5)as batch 
     from tempbatch mb
     where (left(mb.frmheader, 3) = 'RET' and right(mb.frmheader, 4) = 'pg01'))as mb 
  on irsdata.ntsbatch = mb.ntsbatch 
where (irsdata.ntsbatch = 'zzp30401') and batch <> '30401'

I was finally able to get it work.

Thanks again for your assistance.

Best regards,
Teresa


Edited by - tj on 11/06/2002 12:43:17