Solved

Delete records from one table which exist in another

Posted on 2014-10-10
9
139 Views
Last Modified: 2014-10-11
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.
0
Comment
Question by:Dale Fye (Access MVP)
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 26

Expert Comment

by:Shaun Kline
ID: 40373193
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
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40373194
Change line 1 to DELETE TR
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 40373262
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
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 40373633
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 26

Expert Comment

by:Zberteoc
ID: 40373932
(2)  Deleting with a JOIN requires two FROM's, which sounds kinda hokey I know.

No it doesn't need 2 FROMs.
0
 
LVL 47

Author Comment

by:Dale Fye (Access MVP)
ID: 40374214
Zberteoc,

it gave me an incorrect syntax error without the 1st FROM
0
 
LVL 47

Author Closing Comment

by:Dale Fye (Access MVP)
ID: 40374216
Thanks, Jim.

Precisely what I was looking for, and thanks for the explanation.
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40374781
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
 
LVL 47

Author Comment

by:Dale Fye (Access MVP)
ID: 40374845
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

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now