Solved

Delete records from one table which exist in another

Posted on 2014-10-10
9
146 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)
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
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.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

763 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