June 19, 2002 at 8:13 am
Please tell me what this statement does?
DELETE Base.dbo.na_log FROM Chg.dbo.na_log a, Base.dbo.na_log b
WHERE
a.Update_Type = 1 and
a.na_inst_nbr = b.na_inst_nbr
and a.na_tr_code = b.na_tr_code
and a.na_acct_nbr = b.na_acct_nbr
and a.na_seq = b.na_seq
and a.na_subseq = b.na_subseq
and a.deleted_record = 0
I have only 24 rows with update_type = 1 in Base database. But when i execute this query, i delete 34 rows. Paying more attention, i found this query actually deletes all rows with Update_type = 1 (24 rows) and update_type = 2 (10 rows). It does not delete rows with Update_type = 3 or 4 or 0.
Give me a fish, you feed me for a day! Teach me to fish, you feed me for a life time.
June 19, 2002 at 8:21 am
Well I'll suggest that you're deleting rows from base.dbo.na_log which has an alias of B in the following query, but you're filtering on A.Update_Type?
Perhaps it's more subtle than this?
Regards
Simon
June 19, 2002 at 8:31 am
Thanks SJCSystems.
I want it to delete from base database (which is alias b) based on A.Update_Type = 1. But your suggestion reminds me of one thing. May be i need to add another condition:
a.update_type = b.update_type.
The query would look like this:
DELETE Base.dbo.na_log FROM Chg.dbo.na_log a, Base.dbo.na_log b
WHERE
a.Update_Type = 1 and
a.Update_Type = b.Update_type and
a.na_inst_nbr = b.na_inst_nbr
and a.na_tr_code = b.na_tr_code
and a.na_acct_nbr = b.na_acct_nbr
and a.na_seq = b.na_seq
and a.na_subseq = b.na_subseq
and a.deleted_record = 0
I will test it and let you know.
Give me a fish, you feed me for a day! Teach me to fish, you feed me for a life time.
June 19, 2002 at 8:42 am
How about this one
DELETE FROM b
from
Chg.dbo.na_log a
inner join
Base.dbo.na_log b
on a.na_tr_code = b.na_tr_code and
and a.na_acct_nbr = b.na_acct_nbr and
and a.na_seq = b.na_seq and
and a.na_subseq = b.na_subseq and
a.Update_Type = b.Update_type and
a.na_inst_nbr = b.na_inst_nbr
where
a.Update_Type = 1 and
and a.deleted_record = 0
Edited by - Nazim on 06/19/2002 08:46:22 AM
June 19, 2002 at 8:50 am
The query works but it will not work for my business logic. I cannot put a.Update_type = b.update_type in the condition.
quote:
Thanks SJCSystems.I want it to delete from base database (which is alias b) based on A.Update_Type = 1. But your suggestion reminds me of one thing. May be i need to add another condition:
a.update_type = b.update_type.
The query would look like this:
DELETE Base.dbo.na_log FROM Chg.dbo.na_log a, Base.dbo.na_log b
WHERE
a.Update_Type = 1 and
a.Update_Type = b.Update_type and
a.na_inst_nbr = b.na_inst_nbr
and a.na_tr_code = b.na_tr_code
and a.na_acct_nbr = b.na_acct_nbr
and a.na_seq = b.na_seq
and a.na_subseq = b.na_subseq
and a.deleted_record = 0
I will test it and let you know.
Give me a fish, you feed me for a day! Teach me to fish, you feed me for a life time.
June 19, 2002 at 9:01 am
Hi there,
i just gave u a guideline. whichever conditions are redundant you can do away with them. feel free to modify the query to match your requirements.
HTH
June 19, 2002 at 9:01 am
The inner Join produces the same results. It deletes 34 rows. Similarly Right Outer Join produces the same results.
What i need is to delete only 24 rows (only rows with Update_type = 1) and not include the condition a.Update_type = b.Update_type. Any thoughts?
quote:
How about this oneDELETE FROM b
from
Chg.dbo.na_log a
inner join
Base.dbo.na_log b
on a.na_tr_code = b.na_tr_code and
and a.na_acct_nbr = b.na_acct_nbr and
and a.na_seq = b.na_seq and
and a.na_subseq = b.na_subseq and
a.Update_Type = b.Update_type and
a.na_inst_nbr = b.na_inst_nbr
where
a.Update_Type = 1 and
and a.deleted_record = 0
Edited by - Nazim on 06/19/2002 08:46:22 AM
Give me a fish, you feed me for a day! Teach me to fish, you feed me for a life time.
June 19, 2002 at 9:06 am
i think we are missing something . when you run this from query analyzer how many records does this return and what are the records which are unexpected one's.
select *
from
Chg.dbo.na_log a
inner join
Base.dbo.na_log b
on a.na_tr_code = b.na_tr_code and
and a.na_acct_nbr = b.na_acct_nbr and
and a.na_seq = b.na_seq and
and a.na_subseq = b.na_subseq and
a.Update_Type = b.Update_type and
a.na_inst_nbr = b.na_inst_nbr
where
a.Update_Type = 1 and
and a.deleted_record = 0
if you can provide some ddl and dml operations . i think , v can bail you out easily.
June 19, 2002 at 9:12 am
Thanks Nazim. Thanks for your input.
The Select statement returns 34 rows. 24 rows with Update_type = 1 and 10 rows with Update_type = 2. These are the exact rows which gets deleted when we issue the Delete statement.
What we need to achieve is delete only 24 rows (Update_type = 1 only) without issueing the condition:
a.Update_type = b.Update_type
Give me a fish, you feed me for a day! Teach me to fish, you feed me for a life time.
June 19, 2002 at 12:08 pm
Thanks Guys especially Nazim and SJC Systems.
Atlast I found it. The correct query is:
DELETE Base.dbo.na_log FROM Chg.dbo.na_log a, Base.dbo.na_log b
WHERE
a.Update_Type = 1 and
a.Update_Type = b.Update_type and
a.na_inst_nbr = b.na_inst_nbr
and a.na_tr_code = b.na_tr_code
and a.na_acct_nbr = b.na_acct_nbr
and a.na_seq = b.na_seq
and a.na_subseq = b.na_subseq
and b.deleted_record = 0
Note that the last condition a.deleted_record is changed to b.deleted_record.
Give me a fish, you feed me for a day! Teach me to fish, you feed me for a life time.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply