I found a query miss written in one of our databases like this

Use database2
go

delete database1.dbo.table1
from table1 as t1
join table2 on t1.column1 = t2.column2 and t2.column3 = 1

I am having a hard time getting my head around it as it looks like it will delete EVERYTHING from database1.dbo.table1 if column3 in table1 or table2 = 1 and if the join condition is successful (Please correct me if I am wrong). It is not meant to even touch database1.

Also, if that happens, which database transaction log will get huge, database1 or database2? Query runs in Database2 but delete is happening for table in database1.

Thanks for your input.

New contributor
Sarah is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
  • Just FYI, you can "format" code by selecting the code, then pressing CTRL-K – Max Vernon Nov 8 at 18:54
  • Most definitely, thank you. – Sarah Nov 8 at 18:55
  • 1
    Just as a suggestion moving forward, something like this is pretty easy to test on your development workstation. SImply create two databases, with the required tables, then insert a couple of rows, and run the DELETE statement. – Max Vernon Nov 8 at 18:58
  • Also, the column names in the join need to be qualified with the table name - as it is you have a syntax error. – Max Vernon Nov 8 at 19:06
  • Also, I get this error: Msg 1013, Level 16, State 1, Line 14 The objects "db1.dbo.table1" and "dbo.table1" in the FROM clause have the same exposed names. Use correlation names to distinguish them. You should add the actual delete statement to your question. – Max Vernon Nov 8 at 19:07
up vote 1 down vote accepted

Creating a minimal, complete, and verifiable example will help you better formulate questions in future, and allow other folks to help you more easily.

I've created one:

CREATE DATABASE db1;
CREATE DATABASE db2;
GO
USE db1;
CREATE TABLE dbo.table1 (id1 int, id2 int);
USE db2
CREATE TABLE dbo.table1 (id1 int, id2 int);
CREATE TABLE dbo.table2 (id1 int, id2 int);

INSERT INTO db1.dbo.table1(id1, id2) VALUES (1, 2);
INSERT INTO db1.dbo.table1(id1, id2) VALUES (3, 2);
INSERT INTO db2.dbo.table1(id1, id2) VALUES (1, 2);
INSERT INTO db2.dbo.table2(id1, id2) VALUES (1, 2);

DELETE FROM db1.dbo.table1
FROM dbo.table1 t1
    INNER JOIN dbo.table2 ON t1.id1 = table2.id1

Looking at the tables is instructive:

SELECT *
FROM db1.dbo.table1;
╔═════╦═════╗
║ id1 ║ id2 ║
╚═════╩═════╝
SELECT *
FROM dbo.table1;
╔═════╦═════╗
║ id1 ║ id2 ║
╠═════╬═════╣
║   1 ║   2 ║
╚═════╩═════╝
SELECT *
FROM dbo.table2;
╔═════╦═════╗
║ id1 ║ id2 ║
╠═════╬═════╣
║   1 ║   2 ║
╚═════╩═════╝

Cleanup:

USE master;
DROP DATABASE db2;
DROP DATABASE DB1;

The takeaway appears to be that all rows in db1.dbo.table1 are deleted, which makes sense since the FROM dbo.table1 JOIN dbo.table2 ... clause does not reference database1.dbo.table1 at all.

  • Yep, I just created an example of that on my machine too. Question is, this query has been running for years and never removed data from db1.dbo.table1, why now.. – Sarah Nov 8 at 19:40
  • code change recently? – Max Vernon Nov 8 at 19:41
  • Look what I found now, if the join condition does not happen between table1 and table2, DELETE FROM db1.dbo.table1 doesnt do anything. No delete happens.. – Sarah Nov 8 at 19:41
  • Like replace INSERT INTO db2.dbo.table2(id1, id2) VALUES (1, 2) with INSERT INTO db2.dbo.table2(id1, id2) VALUES (3, 4); – Sarah Nov 8 at 19:42
  • 1
    SQL statements can be tricky. – Max Vernon Nov 8 at 19:45

Your Answer

Sarah is a new contributor. Be nice, and check out our Code of Conduct.
 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.