What is the equivalent of "if exists()" in databricks sql?

reachrishav — Tue, 06 Aug 2024 18:42:09 GMT

What is the equivalent of the below sql server syntax in databricks sql? there are cases where i need to execute a block of sql code on certain conditions. I know this can be achieved with spark.sql, but the problem with spark.sql() is it does not provide the number of affected columns (insert/update/delete) after the execution of dml statement.

IF EXISTS(SELECT 1 FROM table WHERE col = value)

BEGIN

DELETE FROM table WHERE col = value

END

ELSE

BEGIN

END

Re: What is the equivalent of "if exists()" in databricks sql?

Retired_mod — Thu, 08 Aug 2024 08:11:47 GMT

Hi @reachrishav, In Databricks SQL, you can replicate SQL Server's conditional logic using `CASE` statements and `MERGE` operations. Since Databricks SQL doesn't support `IF EXISTS` directly, you can create a temporary view to check your condition and then use `MERGE` to perform the desired operations. Afterward, you can count the affected rows with a `SELECT` statement. This approach allows for conditional execution and tracking of affected rows without relying on SQL Server-specific syntax.

Does this help with your use case? If you have any more specific requirements or questions, feel free to ask!

topic What is the equivalent of "if exists()" in databricks sql? in Data Engineering

What is the equivalent of "if exists()" in databricks sql?

Re: What is the equivalent of "if exists()" in databricks sql?