[SOLVED] How not to update a column if a file path is null or file doesn't exist?

How not to update a column if a file path is null or file doesn't exist?

I am updating a column with this:

UPDATE myTable
SET myFile = (SELECT myFile.BulkColumn  FROM OPENROWSET(BULK N'D:\Text3.txt', SINGLE_BLOB) AS myFile) 
WHERE id = 2

How not to update a column and leave old file there if the file Text3.txt does not exist? Some like:

SET myFile = COALESCE((SELECT myFile.BulkColumn  FROM OPENROWSET(BULK N'D:\Text3.txt', SINGLE_BLOB) AS myFile), myFile)
WHERE id = 2

Solution

Use Master.dbo.xp_fileexist to check file is exist or not

Create Table #temp
(
   File_Exists  bit,
   File_is_Directory int,
   Parent_Directory_Exists bit
)

INSERT INTO #temp
EXEC Master.dbo.xp_fileexist N'D:\Text3.txt'

--1 means exists while 0 means not exists
IF 1=(SELECT File_Exists FROM #temp)
BEGIN
   UPDATE myTable
   SET myFile = (SELECT myFile.BulkColumn  FROM OPENROWSET(BULK N'D:\Text3.txt', SINGLE_BLOB) AS myFile) 
   WHERE id = 2

   SELECT * FROM myTable
END
ELSE
BEGIN
   SELECT * FROM myTable
END

DROP table #temp