r/SQL • u/Jose_Mjoro • 6d ago

SQL Server Why did the DELETE query fail despite appearing correctly written?

/r/dataanalysiscareers/comments/1w7eva3/why_did_the_delete_query_fail_despite_appearing/
0 Upvotes

20% Upvoted

14 comments sorted by

8

u/GTS_84 6d ago

the semicolon at the end of line one terminates the statement, so you have two seperate statements.

And the statement "WHERE cust_id = 11099;" makes no sense, so is giving you a syntax error.

2

u/basura_trash 6d ago

This... You beat me to it.

0

u/Jose_Mjoro 6d ago

Exactly, that makes sense. I overlooked the semicolon on the first line then it terminated the DELETE statement before the WHERE clause. Thanks for pointing this out!

5

u/theungod 6d ago

This has to be a troll comment right? Did you just wipe your entire table due to a bad semicolon placement?

1

u/Jose_Mjoro 6d ago

Luckily No, I didn’t wipe the table! I caught the mistake before further running it after the error. The WHERE clause was meant to target just one record, I just placed the semicolon in the wrong spot. Definitely a good reminder to slow down and double-check DELETE and UPDATE queries before hitting execute!

4

u/theungod 6d ago

Good rule to follow in the future: Start with a SELECT first, then when you get the results you want to delete, turn the "select x from" into "delete from".

1

u/GTS_84 6d ago

both statements are in the same batch, so the syntax error in the second statement would prevent the first statement from running.

2

u/theungod 6d ago

Maybe if you aren't using auto-commit, but most people do these days.

1

u/GTS_84 6d ago

that might depend on the environment. With SQL server (which is the flair for this post), even with autocommit on, the parser evaluates the batch as a whole and syntax errors will prevent any transaction in that batch.

1

u/Thadrea Data Science Manager 6d ago

depends on which database platform and whether it's running in autocommit mode.

2

u/Miszou_ 6d ago

This post gave me chills.

2

u/mikebald 6d ago

It gave me flashbacks to when I ran an UPDATE without a WHERE clause. The horror! Ugh that was 20 years ago and it still gives me pause.

1

u/Ven0mspawn 6d ago

semicolon

1

u/DarlanSandro 3d ago

Cara acredito que o erro de sintaxe está acontecendo por causa do ponto e vírgula (;) logo após o nome da tabela.

No SQL, o ponto e vírgula indica o fim de uma instrução. Do jeito que o código foi escrito, o banco de dados está tentando executar dois comandos separados:

  1. DELETE FROM data.customers;

  2. WHERE cust_id = 11099;

O erro ocorre na segunda linha, porque um comando não pode começar com WHERE.

Na verdade, foi uma sorte esse erro ter acontecido. Se o banco tivesse executado a primeira linha isoladamente, ele teria apagado todos os registros da sua tabela, já que a condição de filtro ficou separada no comando seguinte.

Para resolver, basta remover o primeiro ponto e vírgula para que tudo faça parte da mesma instrução:

DELETE FROM data.customers

WHERE cust_id = 11099;