Delete records based on join condition

Question

Delete records based on join condition

Amit Thakur-412802

SSC Journeyman

Points: 80
More actions
February 25, 2010 at 11:00 am

#219446

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

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004664 More actions · Answer 1

I can't help you on the DELETE but I can tell you that I'd find a way to avoid hard coding user name and password especially for the "SA" password.

It would also be helpful if you told us what the exact error you're getting is.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)