Ingres 2006 Update Error

Polly013 · #1 (**permalink**) 02-16-09, 11:08

Hi all,

I am attempting to run an update statement in Ingres 2006 to update a number of records. When I run the nested select I get 150 records, but when I run the update it changes 5600 records!!! Can anyone spot where I am going wrong with this command. I just can't see how it can be updating more than I ask it to.

Code:

update hbincome                                                            
     set inc_type = ''                                                          
     where claim_id in (select a.claim_id                                       
     from hbhousehold a, hbincome b                                             
     where a.claim_id = b.claim_id                                              
     and a.house_id = b.house_id                                                
     and a.to_date = '31.12.2099'                                               
     and b.inc_code in ('IBH','IBN','IBL','DLL','DLA','DLH')                    
     and b.inc_type in ('18','19','HL','HR'));

Many thanks,
Paula

JF.pt · #2 (**permalink**) 07-07-09, 12:18

I Polly013,

try a select distinct, because if the select returns duplicated rows, it may have this behavior.

update hbincome
set inc_type = ''
where claim_id in (select distinct a.claim_id
from hbhousehold a, hbincome b
where a.claim_id = b.claim_id
and a.house_id = b.house_id
and a.to_date = '31.12.2099'
and b.inc_code in ('IBH','IBN','IBL','DLL','DLA','DLH')
and b.inc_type in ('18','19','HL','HR'));

Internet Services

Web Development

Digital Marketing

Consumer Tech