June 27, 2008 at 12:49 pm
Hi experts,
How can i delete data without primary key or specific key , my imagine such as below
delete from table_xx where exists (
select a,b,c from table_1
except
select a,b,c from table_2
)
but the result does'nt meet my expectation,
any idea? or i can't delete it?
🙂
June 27, 2008 at 12:55 pm
You can delete with a join.
delete Table1
from dbo.Table1
inner join dbo.Table2
on Table1.a = Table2.a
and Table1.b = Table2.b
left outer join dbo.Table3
on Table1.a = Table3.a
and Table1.b = Table3.b
where Table3.a is null
Something like that should do what you need.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 27, 2008 at 12:58 pm
Panji_panjul (6/27/2008)
Hi experts,How can i delete data without primary key or specific key , my imagine such as below
delete from table_xx where exists (
select a,b,c from table_1
except
select a,b,c from table_2
)
but the result does'nt meet my expectation,
any idea? or i can't delete it?
🙂
Well - considering your exists will be either true or false independently of anything within table_XX, that statement will either delete ALL or NO records in table_XX. I'm guessing that's not what you want.
What is it you're trying to to?
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
June 27, 2008 at 5:01 pm
Hi,
DELETE FROM table_xx WHERE A + B + C IN (
SELECT A + B + C FROM table_1 where A + B + C NOT IN ( SELECT A + B + C FROM table_2) )
This is not the right way to query but, the logic is very simple.
Thanks -- Vj
June 27, 2008 at 9:21 pm
Matt Miller (6/27/2008)
Panji_panjul (6/27/2008)
Hi experts,How can i delete data without primary key or specific key , my imagine such as below
delete from table_xx where exists (
select a,b,c from table_1
except
select a,b,c from table_2
)
but the result does'nt meet my expectation,
any idea? or i can't delete it?
🙂
Well - considering your exists will be either true or false independently of anything within table_XX, that statement will either delete ALL or NO records in table_XX. I'm guessing that's not what you want.
What is it you're trying to to?
i've a project of syncronize data,
the syncronize should has 'insert','update','delete' to make sure the syncronize of data from database source with database destination will uptodate/accurate.
Have 3(or more) sources database will be syncronize into 1 database as destination.
But i found :
1. any table doesn't has a specific key (or Primary Key) to delete
2. any table has Primary Key (but some of them using running number/autonumber/ identity) . As we known, Running number at source and destination can't be exactly same(depend on running ) + the 3 source will syncronize into 1 destination
Example 1:
Tbl_source ( doesn't has a specific/unique key )
A B C
-------------------
81 y k4
2 g a1
Tbl_destination ( doesn't has a specific/unique key )
A B C
-------------------
81 5 89
7 cv gg
Example 2:
Tbl_source ( column 'A' is Running number as Primary key)
A B C
-------------------
1 y k4
2 g a1
Tbl_destination ( column 'A' is Running number as Primary key)
A B C
-------------------
4 5 89
5 cv gg
June 27, 2008 at 9:29 pm
Vijaya Krishna (6/27/2008)
Hi,DELETE FROM table_xx WHERE A + B + C IN (
SELECT A + B + C FROM table_1 where A + B + C NOT IN ( SELECT A + B + C FROM table_2) )
This is not the right way to query but, the logic is very simple.
Thanks -- Vj
Good Idea, i think that's the solution
Thanks VJ
June 28, 2008 at 6:03 am
GSquared suggested you a much faster solution, I will finish his script for your situation:
delete tx from dbo.table_xx tx
inner join (
select t1.*
from table_1 t1 left outer join table_2 t2
on t1.a = t2.a and t1.b = t2.b and t1.c = t2.c
where t2.a is null
) ex
on tx.a = ex.a and tx.b = ex.b and tx.c = ex.c
it's faster and safer, but have the same minus as Vijaya Krishna's script, will not work for null columns, this depends on your tables content
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply