Select & Update without a join

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)

Select & Update without a join

Author

Topic

TJ
Posting Yak Master

201 Posts

Posted - 2002-08-21 : 13:30:14

Can anyone tell me what's wrong with this query? I think I'm in an endless loop?? If you know of a better way to write this, please say so.

I need to find and update records accordingly. I'm using SQL Server 7.0.

The table names are Taxpayer and Banks and the criteria is stated in the where clauses listed below.

If you need ad'l info to help me, please let me know.


declare @bnkapp as char(1)
declare @rtn as char (10)
declare @blnRtn as char (1)
declare @PRIMSSN as char (9)
declare @ProcStat as char (2)
declare @DirState as char (2)
declare @AccptCode as char (1)
declare @Dan as char (10)
declare @ID int
declare @MaxID int
declare @Proc as char (2)
 
select @proc = '35', @ID = 0, @maxid = max(taxpayerid) from taxpayer 
  --print @maxid 

while @ID < @maxid 
  begin
    select @id = min(taxpayerid) from taxpayer where procstat = @proc
    select @Primssn = Primssn, @procstat = procstat, @accptcode = accptcode,
           @Dan = dan, @dirstate = dirstate, @rtn = rtn
    from taxpayer
    where taxpayerid = @id and @procstat = @proc

    if @accptcode = 'A' and (left(@dan,1) = 'R' or left(@dan,1) = 'Q') 
       and @dirstate = 'FD' --and @rtn is not null
    --begin
      --print @primssn + " | " + @rtn + " | " + @blnrtn + " | " +@accptcode + " | " + @dan + " | " + @procstat + " | " + @dirstate + " | "
    --end

    if exists (select rtn from banks where rtn = @rtn )
      begin
        while @procstat = @proc
          begin  
            set @blnRtn = 'Y'  
            print @primssn + " | " + @rtn + " | " + @blnrtn + " | " +@accptcode + " | " + @dan + " | " + @procstat + " | " + @dirstate + " | "
            update taxpayer set procstat = 'TB' where taxpayerid = @id and @blnrtn = 'Y' 
                   and  @accptcode = 'A' and (@DAN = 'Q' or @DAN = 'R') and @procstat = '35' and @dirstate = 'FD'
          end
      end
 end

(I know how to do this with a cursor, but I'm trying very hard not to use them!

)

Thanks!
Teresa

Edited by - TJ on 08/21/2002 13:33:14

TJ
Posting Yak Master

201 Posts

Posted - 2002-08-21 : 14:13:12

I was reading a message in the Yak corral after I posted this and realized that not only do I sometimes expect you to know what I'm saying by osmosis, but that I could predict what you would say using the same methods and principals. It's been awhile since I've tried to code in SQL and was taking the advice of a co-worker, who by the way isn't an SQL programmer either, and came up with the inane code previously posted. After thinking about what I'd read, and then thinking about what I'd learned from earlier replies to my posts, I figured it out on my own.

Thanks for not posting that you couldn't help me, that my code was garbage, and that I needed to take up a new career.

Here's the good stuff:


update taxpayer set taxpayer.procstat = 'TB'
    from taxpayer  
    inner join 
      (select b.rtn, b.lasmailbox
      from Banks B
      where B.rtn is not null)
      as B on taxpayer.lasmailbox = B.lasmailbox
    where taxpayer.procstat = '35' and taxpayer.dirstate = 'FD'

"Trying to remember to keep it simple!"
Teresa

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources