Delete records based on join condition

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Delete records based on join condition

Author

Topic

thakur_amit
Starting Member

6 Posts

Posted - 2010-02-25 : 12:49:46

Hi,

I want to delete recodrs from based on some join condition between two tables.

One table belongs to my DB ( table name : FeeDetails)

and second table ( TargetTable) is on different server.

I have delete records from TargetTable table based on join condition between two tables.

I am able to select records

select * from
OPENDATASOURCE
('SQLOLEDB','Data Source=BHUPENDRA;user id=sa;password=password')
.DBName.dbo.targetTable a
where exists
(
select * from FeeDetails b
where a.SFH_ID=b.SFH_ID
and a.SchoolId=b.SchoolId and
a.SPD_NAME=b.SPD_NAME
and b.TransferFlag='M'
)

SELECT * FROM OPENDATASOURCE
('SQLOLEDB','Data Source=BHUPENDRA;user id=sa;password=password')
.DBName.dbo.targetTable a
WHERE EXISTS (SELECT *
FROM FeeDetails b
WHERE b.TransferFlag='M'
and a.SFH_ID=b.SFH_ID
and a.SchoolId=b.SchoolId
and a.SPD_NAME=b.SPD_NAME
)

------

But when i am trying to delete records from target table...i am getting error...

my delete queries

delete from OPENDATASOURCE
('SQLOLEDB','Data Source=BHUPENDRA;user id=sa;password=password')
.DBName.dbo.fee_ a
WHERE EXISTS (SELECT *
FROM fee_ b
WHERE b.TransferFlag='M'
and a.SFH_ID=b.SFH_ID
and a.SchoolId=b.SchoolId
and a.SPD_NAME=b.SPD_NAME
)

delete from
OPENDATASOURCE
('SQLOLEDB','Data Source=BHUPENDRA;user id=sa;password=password')
.DBName.dbo.fee_ a
where exists
(
select * from fee_ b
where a.SFH_ID=b.SFH_ID
and a.SchoolId=b.SchoolId and
a.SPD_NAME=b.SPD_NAME
and b.TransferFlag='M'
)

Please help me out..to fix this ...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-26 : 08:19:27

whats the error you're getting?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources