Dale Fye
asked on
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_T ype_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.
DELETE TR.*
FROM dbo.tbl_Readings_Tanks as TR
INNER JOIN zz_Staging_Tanks
ON (TR.Reading_Type_ID = zz_Staging_Tanks.Reading_T
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.
Change line 1 to DELETE TR
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.
You use * only to SELECT, in the case of DELETE or UPDATE you only specify the alias, in this case TR.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
(2) Deleting with a JOIN requires two FROM's, which sounds kinda hokey I know.
No it doesn't need 2 FROMs.
No it doesn't need 2 FROMs.
ASKER
Zberteoc,
it gave me an incorrect syntax error without the 1st FROM
it gave me an incorrect syntax error without the 1st FROM
ASKER
Thanks, Jim.
Precisely what I was looking for, and thanks for the explanation.
Precisely what I was looking for, and thanks for the explanation.
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.
ASKER
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.
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.
Open in new window
You can verify prior to deleting by changing the DELETE FROM to SELECT * FROM.