Delete records from one table which exist in another

In SQL Server 2008 R2, I'm trying to delete records from one table that match records in another table based on the values in 4 fields.  I've tried:

DELETE TR.*
FROM dbo.tbl_Readings_Tanks as TR
INNER JOIN zz_Staging_Tanks
ON (TR.Reading_Type_ID = zz_Staging_Tanks.Reading_Type_ID)
AND (TR.Prod_ID = zz_Staging_Tanks.Prod_ID)
AND (TR.docDate = zz_Staging_Tanks.docDate)
AND (TR.Equip_ID = zz_Staging_Tanks.Equip_ID)

But this returns an error :

Incorrect syntax near '*' and underlines as indicated.

There is an unique index in tbl_Readings_Tanks on those 4 columns.
LVL 50
Dale FyeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Shaun KlineLead Software EngineerCommented:
DELETE FROM tbl_Readings_Tanks 
WHERE EXISTS (
   SELECT 1 
   FROM zz_Staging_Tanks 
    WHERE Reading_Type_ID = tbl_Readings_Tanks .Reading_Type_ID 
         AND Prod_ID = tbl_Readings_Tanks.Prod_ID
         AND docDate = tbl_Readings_Tanks.docDate 
         AND Equip_ID = tbl_Readings_Tanks.Equip_ID)

Open in new window


You can verify prior to deleting by changing the DELETE FROM to SELECT * FROM.
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Change line 1 to DELETE TR
0
ZberteocCommented:
Remove  .*  from the DELETE line.

You use * only to SELECT, in the case of DELETE or UPDATE you only specify the alias, in this case TR.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Two things Dale:

(1)  Access uses DELETE *, SQL Server just uses DELETE (as stated in multiple comments above)
(2)  Deleting with a JOIN requires two FROM's, which sounds kinda hokey I know.
DELETE 
FROM TR
FROM dbo.tbl_Readings_Tanks as TR
   JOIN zz_Staging_Tanks 
      ON TR.Reading_Type_ID = zz_Staging_Tanks.Reading_Type_ID
      AND TR.Prod_ID = zz_Staging_Tanks.Prod_ID
      AND TR.docDate = zz_Staging_Tanks.docDate
      AND TR.Equip_ID = zz_Staging_Tanks.Equip_ID

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ZberteocCommented:
(2)  Deleting with a JOIN requires two FROM's, which sounds kinda hokey I know.

No it doesn't need 2 FROMs.
0
Dale FyeAuthor Commented:
Zberteoc,

it gave me an incorrect syntax error without the 1st FROM
0
Dale FyeAuthor Commented:
Thanks, Jim.

Precisely what I was looking for, and thanks for the explanation.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the grade.  btw If you're doing a lot of Access to SQL conversion I have an article out there on Migrating your Access Queries to SQL Server Transact-SQL that's a grab bag of differences.
0
Dale FyeAuthor Commented:
Thanks, Jim; I'll take a look.  

I've been moving what was an Access BE to SQL Server so that I can pull data in from multiple data sources.  I've used SQL Server as a backend before, but never when trying to merge data with different structures from multiple sources.  It has been a trip learning how to deal with PIVOT and MERGE querys.  I really like the functionality of the MERGE queries and am now working on one that includes an and clause, so the it only updates a record in the target table if that record has not already been manually edited.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.