How To Delete From Tabular Array Using Bring Together Inward Sql Server

It's a footling fighting tricky to delete from a tabular array piece using whatever type of JOIN inwards SQL e.g. Inner Join, Left Outer Join, or Right Outer Join. The obvious syntax doesn't move equally shown below:

delete from #Expired e INNER JOIN  Deals d ON e.DealId = d.DealId Where d.Brand = 'Sony'

hither I conduct maintain a tabular array amongst a listing of expired deals which I desire to delete from the Deals tables, but alone for Sony.

When I run this SQL Query inwards Microsoft SQL Server 2008, it gave me next error:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax close 'e'.

Now, I am puzzled, how to delete from a tabular array piece using INNER JOIN inwards SQL Server?

Well, it turned out, I wasn't using the right syntax. The DELETE clause needs to know the tabular array from which information needs to last deleted. You tin solve the job past times using table alias equally shown below:

delete e from #Expired e INNER JOIN Deals d ON e.DealId = d.DealId Where d.Brand = 'Sony'

This tells the SQL Server that deletes the matching row from the tabular array amongst alias "e" i.e. the #Expired table. If you lot are non a fan of using tabular array aliases in addition to thus you lot tin equally good laissez passer on sum table name at that topographic point equally shown below, this volition equally good move fine inwards Microsoft SQL Server database.

delete #Expired from #Expired INNER JOIN Deals ON #Expired.DealId = Deals.DealId Where Deals.Brand = 'Sony'

The same syntax volition move fine fifty-fifty if you lot occupation the Left Outer Join or Right Outer Join.

SQL is similar coding, both are cardinal skills which require daily practice. If you lot don't write SQL queries or brace of months, you lot volition alone retrieve the select * from table. More complex queries involving joining to a greater extent than than 2 tables inwards ane query volition last hard to empathize in addition to write. At the same time, you lot don't acquire the chance to write big, complex SQL queries on a daily basis, it's alone when you lot ask to generate reports, troubleshooting something, you lot ask this skill.


This is where the mass similar SQL Puzzles in addition to Answers from Joe Celko tin aid a lot. It's ane of the best books  for programmers who are serious almost improving their SQL skills. Whenever I conduct maintain closed to gratis time, I conduct maintain on puzzles inwards this book. They are seriously worth doing, you lot volition larn a affair or 2 amongst every puzzle.

Deleting From Table Using Join - SQL Example

Now, let's run across a alive example of how to delete records from a tabular array using Join inwards SQL Server. In gild to present something, nosotros start ask to do a Deal in addition to Expired table. You tin occupation next SQL enquiry to do those 2 tables. I conduct maintain purposefully created them equally a temporary table, you lot tin run across the prefix # because they are for demonstration only. You should equally good non forget to drib them ane time you lot are done. Influenza A virus subtype H5N1 proficient SQL do to follow.

-- creating a Deals tabular array  Create tabular array #Deals (DealId BIGINT, Brand VARCHAR(100), ExpiryDate DATE); Insert into #Deals values (10001, 'Sony', '20160102'); Insert into #Deals values (10002, 'Samsung', '20160103'); Insert into #Deals values (10003, 'HP', '20160104'); Insert into #Deals values (10004, 'Intel', '20160105'); Insert into #Deals values (10005, 'Citibank', '20160106'); Insert into #Deals values (10003, 'HSBC', '20160107'); Insert into #Deals values (10004, 'Disney', '20160108'); Insert into #Deals values (10005, 'Motorola', '20160109');  Select * from #Deals;  -- copying expired deals into a temporarily expired table Select * into #Expired from #Deals where ExpiryDate < '20160106' Select * From #Expired  -- cleanup Drop table #Deals; Drop table #Expired;

in addition to hither is the output of these SQL queries when you lot run them on Microsoft SQL Server 2014:

Now, let's occupation the inner bring together betwixt both #Deals in addition to #Expired to withdraw Expired entries for build Sony from the #Expired table.

You tin run across that start row amongst DealId=10001 has been deleted from the #Expired tabular array because that's the row amongst Sony brand.  If you lot experience SQL bring together is confusing in addition to it's hard to empathize what's going on, I advise you lot to read Head First SQL. One of the improve books inwards SQL for beginners.

That's all almost how to delete from a tabular array when using Joins inwards SQL Server. I await the enquiry to move fine on other major relational databases e.g. Oracle, MySQL in addition to PostgreSQL because I am non using anything SQL Server specific, it's all ANSI SQL.

Further Learning
Introduction to SQL
The Complete SQL Bootcamp
SQL for Newbs: Data Analysis for Beginners

Share this post