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

It's a trivial fight tricky to delete from a tabular array land using whatsoever 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 guide keep 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 land 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 hold upward 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 hence you lot tin besides laissez passer on amount table name in that place equally shown below, this volition besides 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 job the Left Outer Join or Right Outer Join.

SQL is similar coding, both are substitution skills which require daily practice. If you lot don't write SQL queries or twosome of months, you lot volition alone shout back the select * from table. More complex queries involving joining to a greater extent than than 2 tables inwards i query volition hold upward hard to empathize together with 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 require to generate reports, troubleshooting something, you lot require this skill.


This is where the mass similar SQL Puzzles together with Answers from Joe Celko tin assistance a lot. It's i of the best books  for programmers who are serious most improving their SQL skills. Whenever I guide keep only about gratuitous time, I guide keep 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 illustration of how to delete records from a tabular array using Join inwards SQL Server. In guild to present something, nosotros start require to do a Deal together with Expired table. You tin job next SQL question to do those 2 tables. I guide keep purposefully created them equally a temporary table, you lot tin run across the prefix # because they are for demonstration only. You should besides non forget to driblet them in i lawsuit you lot are done. Influenza A virus subtype H5N1 practiced SQL exercise 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;

together with hither is the output of these SQL queries when you lot run them on Microsoft SQL Server 2014:

Now, let's job the inner bring together betwixt both #Deals together with #Expired to take away Expired entries for construct 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 together with it's hard to empathize what's going on, I propose you lot to read Head First SQL. One of the ameliorate books inwards SQL for beginners.

That's all most how to delete from a tabular array when using Joins inwards SQL Server. I await the question to move fine on other major relational databases e.g. Oracle, MySQL together with 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