Convert exists to in or any other method (MSSQL PDW)

Transact-SQL
1

UPDATE [tbl_destination]
SET OBSOLETE = 1
WHERE EXISTS(
SELECT 1
FROM [tbl_source] B
WHERE [tbl_destination].Key_Update = B.Key_Update
OR B.Key_Update LIKE [tbl_destination].Key_Update + '-rerun%')
AND OBSOLETE = 0

how to convert above query into other method? below query is not allow in MSSQL PDW

UPDATE [tbl_destination]
SET OBSOLETE = 1
FROM [tbl_destination]
JOIN [tbl_source]
ON [tbl_destination].Key_Update = [tbl_source].Key_Update
OR [tbl_source].Key_Update LIKE [tbl_destination].Key_Update + '-rerun%'

2

Duuno if it is allowed in MSSQL PDW but I usually use an Alias for this tye of multi-table delete - e.g. instead of

UPDATE [tbl_destination]
SET ...
FROM [tbl_destination]
JOIN ...

I would use

UPDATE D
SET ...
FROM [tbl_destination] AS D
JOIN ...
3

not int pdw....it will just reply update does not allow from with nested query or joints....

4

Sorry, I don't know anything about PDW so only guessing, but can you do:

UPDATE D
SET ...
FROM [tbl_destination] AS D
WHERE EXISTS(

although I'm not sure that makes any difference (e.g. to possible syntax opportunities for you) than what you had in your first example.

Why do you need to use a JOIN (instead of an EXISTS)?