How to Delete Using Inner Join With SQL Server

How can I delete using INNER JOIN with SQL Server?

You need to specify what table you are deleting from. Here is a version with an alias:

DELETE w
FROM WorkRecord2 w
INNER JOIN Employee e
  ON EmployeeRun=EmployeeNo
WHERE Company = '1' AND Date = '2013-05-06'

Delete using Inner Joins

What you posted works fine for SQL Server; for MySQL below should do the job

DELETE tableA
FROM tableA
INNER JOIN tableB ON tableB.fieldA = tableA.fieldA;

DELETING using Inner join

what about if you do it on the other way:

delete c from com.Contact c
inner join cqt.CQMTrainer as t on t.ContactId = c.ContactId 

delete a from com.Address a
inner join com.Contact as c on c.AddressId = a.AddressId
inner join cqt.CQMTrainer as t on t.ContactId = c.ContactId

• First you are going to delete the information on the contact table as the error
  
  says you have a reference on contact for address id that's why you can not delete the address table first

SQL DELETE with INNER JOIN

Add .* to s in your first line.

Try:

DELETE s.* FROM spawnlist s
INNER JOIN npc n ON s.npc_templateid = n.idTemplate
WHERE (n.type = "monster");

How to delete rows from two tables using INNER JOIN in mysql?

From the MySQL Docs it looks like you can do this easily:

 DELETE t1, t2 
   FROM t1 INNER JOIN t2 INNER JOIN t3
  WHERE t1.id=t2.id AND t2.id=t3.id;

OR

DELETE 
  FROM t1, t2 
 USING t1 INNER JOIN t2 INNER JOIN t3
 WHERE t1.id=t2.id AND t2.id=t3.id;

It also looks like the newer, preferable, JOIN standard is acceptable and I have no idea why your query is complaining. Are you sure you haven't got any strange characters in your query?

Delete from 2 tables using INNER JOIN

You cannot issue a delete statement against more than one table at a time, you need to have individual delete statements for each of the related tables before deleting the parent record(s)

SQL DELETE based on JOIN and aggregate condition

This answers the first question (about deleting from table 1)

DELETE t1
FROM Table1 AS t1
     INNER JOIN (
         SELECT
            cat,
            min_date=MIN([date])
         FROM
            Table2
         GROUP BY
            cat
    ) AS t2 ON
        t2.cat=t1.cat
WHERE
    t1.[date]>=t2.min_date

How to solve delete using inner join in sql database?

You cannot join in the FROM of DELETEs in Postgres.

If you want to delete all the row from stock_picking sp with a company_id of 1 and all the corresponding rows from stock_pack_operation, you can use a cte with a RETURNING clause.

WITH cte
(
DELETE FROM stock_picking sp
       WHERE sp.company_id = 1
       RETURNING sp.id
)
DELETE FROM stock_pack_operation spo
       USING cte
       WHERE spo.picking_id = cte.id;

If want to delete all the rows from stock_pack_operation where the picking_id is one the ids from stock_picking for the company of ID 1 you can use USING.

DELETE FROM stock_pack_operation spo
       USING stock_picking sp
       WHERE spo.picking_id = sp.id
             AND sp.company_id = 1;

SQL Delete with Join and Foreign Key constraint

Extracted from MySQL documentation:

If you use a multiple-table DELETE statement involving InnoDB tables
for which there are foreign key constraints, the MySQL optimizer might
process tables in an order that differs from that of their
parent/child relationship. In this case, the statement fails and rolls
back. Instead, you should delete from a single table and rely on the
ON DELETE capabilities that InnoDB provides to cause the other tables
to be modified accordingly.

And if you use EXPLAIN you will see that it prefers to first filter (and delete) by the main table as it need it to do the JOIN.

If you use an INNER JOIN, you could try STRAIGHT_JOIN which is the same as an INNER JOIN except that the left table is always read before the right table and put the tables in the order of deletion you want to achieve.

Alternatively to the cascade, you could disable the foreign keys in the delete statement with SET FOREIGN_KEY_CHECKS=0;.

Convert SQL Server Delete statement to use inner join

Just match the tables up directly:

 DELETE FROM A     
 FROM ##TABLE1 A
 INNER JOIN ##TABLE2 B     
 ON A.VND_ACCT_NUM i= B.VND_ACCT_NUM

SQL Server doesn't mind if you attempt to delete the same row multiple times by such a join. Also, the ... IN(SELECT DISTINCT ... was pointless anyway since IN returns true as soon as it has located one qualifying row.

---

Related Topics