February 16, 2009 at 9:11 am
Hi all,
I have ran the nested select below and it produces a list of 150 records. However when I then run the update it updates 5600 records. can anyone tell me where I am going wrong here?
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
February 16, 2009 at 9:16 am
How many rows does this return?
SELECT COUNT(*) FROM hbincome
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'))
February 16, 2009 at 9:21 am
The result for this is now zero, having ran the update. I know it has not done a blanket update on the whole table as there are many more rows but I just can't work out what it has done, and now the data is updated I can't work it out.
February 16, 2009 at 9:24 am
Paula (2/16/2009)
Hi all,I have ran the nested select below and it produces a list of 150 records. However when I then run the update it updates 5600 records. can anyone tell me where I am going wrong here?
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
please post table structures with sample data...
Also, do you have more than 1 rows for one claim_id in the hbincome table?
February 16, 2009 at 9:25 am
You might not be making any mistakes at all, depending on what you reply to Ninja. The UPDATE looks a lot simpler written as a straightforward UPDATE...FROM though, particularly if you specify the joins correctly:
UPDATE b SET inc_type = ''
FROM hbincome b
INNER JOIN hbhousehold a
ON a.claim_id = b.claim_id AND a.house_id = b.house_id
WHERE 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')
If this looks a little weird, you could test it first with SELECT a.claim_id, b.*
FROM hbincome b
INNER JOIN hbhousehold a
ON a.claim_id = b.claim_id AND a.house_id = b.house_id
WHERE 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')
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 16, 2009 at 9:26 am
It's impossible for people to know without knowing the full definition of your tables and how hbhousehold and hbincome are related, but it looks like you're stating that the link between the two is claim_id and house_id in the sub-query, but in the update statement, you're updating every row that matches a claim ID. in hbincome, can a claim_id belong to multiple households?
based on the nested select statement, the following may be what you're meaning:
update b
set inc_type = ''
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')
or using ANSI joins:
update b
set inc_type = ''
from hbhousehold a
inner join hbincome b on a.claim_id = b.claim_id and a.house_id = b.house_id
where 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')
Obviously you need to test this gives the required result for you as I don't know your schema...
P.S. just read your reply - Uh-oh, hope you have a backup of the data that was updated!
February 16, 2009 at 9:31 am
Maybe is only different count because of null values.
Can you check it?
February 16, 2009 at 9:32 am
Or some $$ if this was production. You can get Log Explorer from Lumigent or Apex Log from Apex to undo the transaction from the log, if you are in full recovery mode.
February 16, 2009 at 9:37 am
Paula (2/16/2009)
The result for this is now zero, having ran the update. I know it has not done a blanket update on the whole table as there are many more rows but I just can't work out what it has done, and now the data is updated I can't work it out.
You will get some idea from running this...
SELECT a.claim_id, b.*
FROM hbincome b
INNER JOIN hbhousehold a
ON a.claim_id = b.claim_id AND a.house_id = b.house_id
WHERE a.to_date = '31.12.2099'
AND b.inc_code IN ('IBH','IBN','IBL','DLL','DLA','DLH')
AND b.inc_type = ''
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
February 16, 2009 at 9:37 am
Aha, yes! HowardW you are right. A claim id can have many house ids. It has updated every house id within each claim id. Not too much of a problem. I have a confession to make - I am running on an Ingres database but wanted to post on a SQL Server site to get some sense! I actually wrote the update the way you suggest in the first place but got syntax errors, so re-wrote it the way I showed in my first post. I think Ingres cannot understand the way you showed.
Sorry for not stating it was Ingres, I just wanted to see if in the normal world my statement was valid (and there are no Ingres forums!!)
Many thanks for you help folks!
Paula
February 16, 2009 at 9:45 am
Yes there is... first hit on google for ingres forumn is : http://community.ingres.com/forum/ingres-forums/
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply