Delete records from one table which exist in another

Posted on 2014-10-10
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:

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.
Question by:Dale Fye
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
LVL 26

Expert Comment

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

Expert Comment

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

Expert Comment

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.
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

LVL 66

Accepted Solution

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

LVL 27

Expert Comment

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

No it doesn't need 2 FROMs.
LVL 48

Author Comment

by:Dale Fye
ID: 40374214

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

Author Closing Comment

by:Dale Fye
ID: 40374216
Thanks, Jim.

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

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.
LVL 48

Author Comment

by:Dale Fye
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.

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

622 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