August 13, 2009 at 1:43 am
Before deleting a record, i have to check whether the record exists in someother tables or not..
need help in doing
August 13, 2009 at 6:55 am
Run a query using the EXISTS keyword. Look it up in the Books Online.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 13, 2009 at 10:42 am
Also, if you are simply checking for the existence of key values in other tables, then the use of JOIN in your DELETE query can prevent deletions of rows that don't exist in other tables.
-------------------------------------------------------------------------------------------
-- This code sets up five tables and populates them with numbers 1 through 5, but the only
-- value common to all five tables is "3". The delete query will be unable to delete any
-- row except "3".
-------------------------------------------------------------------------------------------
set nocount on;
declare @table1 table (data int)
declare @table2 table (data int)
declare @table3 table (data int)
declare @table4 table (data int)
declare @table5 table (data int)
insert into @table1-- has 1-5
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5
insert into @table2 -- doesn't have 1
select 2 union all
select 3 union all
select 4 union all
select 5
insert into @table3 -- doesn't have 2
select 1 union all
select 3 union all
select 4 union all
select 5
insert into @table4 -- doesn't have 4
select 1 union all
select 2 union all
select 3 union all
select 5
insert into @table5 -- doestn't have 5
select 1 union all
select 2 union all
select 3 union all
select 4
-- show that 3 is the only value common to all 5 tables
select t1.*
from @table1 t1
join @table2 t2 on t2.data = t1.data
join @table3 t3 on t3.data = t1.data
join @table4 t4 on t4.data = t1.data
join @table5 t5 on t5.data = t1.data
set nocount off;
--===========================================================================
delete @table1
from @table1 t1
join @table2 t2 on t2.data = t1.data
join @table3 t3 on t3.data = t1.data
join @table4 t4 on t4.data = t1.data
join @table5 t5 on t5.data = t1.data
where t1.data = 5-- Try to delete any number. Only 3 can succesfully be deleted
--===========================================================================
set nocount on;
select * from @table1
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 13, 2009 at 11:40 am
thanks for the reply.........let us say i have 4 tables (table1,table2,table3,table4) table1 is the master table with one column having the primary-foreign key relationship among the column in rest of the tables..
Now consider,
table1 has one record
table2 has one record (one to one)
table3 has three records w.r.t record in table1
table4 has no records (empty)
Now, before deleting a row in table1, i have to find out if any corresponding record(s) exist in table2,table3 and table4......if exist ignore else delete
I have tried writing joins but it didnot worked.....need help regarding
can it be possible in a single query?
August 13, 2009 at 12:01 pm
Can you post the code you tried that didn't work?
It is possible in a single query.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 13, 2009 at 12:10 pm
can you give some example?
August 13, 2009 at 12:19 pm
something like this: testing if any child data exists?
IF NOT EXISTS
( SELECT 1 FROM CHILDTABLE1 WHERE PK IN (SELECT PK FROM MASTERTABLE WHERE PK=4)
UNION ALL SELECT 1 FROM CHILDTABLE2 WHERE PK IN (SELECT PK FROM MASTERTABLE WHERE PK=4)
UNION ALL SELECT 1 FROM CHILDTABLE3 WHERE PK IN (SELECT PK FROM MASTERTABLE WHERE PK=4)
)
BEGIN
DELETE FROM MASTERTABLE WHERE PK=4
END
Lowell
August 13, 2009 at 12:25 pm
can you give some example?
We would prefer that YOU give us an example of your code. Simply throwing out blind examples is a waste of time for the people who are attempting to help you. If we see how you have tried to solve it, we can probably spot the error quickly.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 13, 2009 at 12:53 pm
thanks for the reply........i have to do this from JDBC (java)
i have to execute the query.........
i have tried writing inner join on the tables, but failed as one table has empty data.....the result is always zero
select count(*) from table1 t1, table2 t2,table3 t3,table1 t4 where t1.col1=t2.col1 and t1.col1=t3.col1 and t1.col1=t4.col1 and t1.col1='xyz';
i know the above query is wrong......
first i want to know whether the record(S) exists in table2,table3 and table4 if not delete else ignore...
Need to write a query in such a way tht, even if any of the table(s) (table2,table3 and table4) contains data, the count (or something else) should return positive value..
August 13, 2009 at 1:08 pm
If you are using joins to check, an inner join will only work where a record exists. If you want to check for non-existence, you will have to use an outer join and check for a null value in the outer table.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 13, 2009 at 1:25 pm
drew.allen (8/13/2009)
If you are using joins to check, an inner join will only work where a record exists. If you want to check for non-existence, you will have to use an outer join and check for a null value in the outer table.Drew
Or, since you only care about when records do exist, check for NOT NULL values on the outer table. You'll still arrive at the same place.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 13, 2009 at 2:35 pm
Grant Fritchey (8/13/2009)
Or, since you only care about when records do exist, check for NOT NULL values on the outer table. You'll still arrive at the same place.
No, the OP really only cares about the case when records DO NOT EXIST.
abhishek.c1984 (8/13/2009)
if exist ignore else delete
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 14, 2009 at 5:33 am
drew.allen (8/13/2009)
Grant Fritchey (8/13/2009)
Or, since you only care about when records do exist, check for NOT NULL values on the outer table. You'll still arrive at the same place.No, the OP really only cares about the case when records DO NOT EXIST.
abhishek.c1984 (8/13/2009)
if exist ignore else deleteDrew
Misread it, sorry.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 28, 2018 at 1:32 am
This was removed by the editor as SPAM
February 28, 2018 at 1:43 am
Please note: 9 year old thread
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply