How to fix SQL incorrect syntax with IF EXISTS

0
votes

I'm trying to insert data into a table. If the record already exists, I want to replace the Base64 and keep the existing identifier. If the record doesn't already exist, just insert as normal.

The code at the moment checks if the record exists and either replaces or inserts. I'm getting a syntax error and i'm not entirely sure what I'm doing wrong. 

IF EXISTS (
     SELECT Bdt_DocumentDataAsBase64.Identifier
     FROM Bdt_DocumentDataAsBase64
     WHERE Bdt_DocumentDataAsBase64.Identifier = @Identifier
     )

BEGIN 
(
    REPLACE INTO Bdt_DocumentDataAsBase64(Identifier,Base64)
    VALUES (@Identifier, @Base64 )
)
 END 

ELSE

BEGIN 
(
    INSERT INTO Bdt_DocumentDataAsBase64(Identifier,Base64)
    VALUES (@Identifier, @Base64)
)
END
3
sqlif-statementsyntaxexists
Which DBMS product are you using? There is no IF in standard SQL. - a_horse_with_no_name
Please tag the question with the RDBMS that you are using. sql is a generic tag that may correspond to various RDBMS. - GMB
I've used IF Exists before and when i run that independently of the rest of the script it works fine - Greg
Not sure just normal sql server management studio - Greg
SQL Server uses keywords to denote block structure in control flow. Not brackets, and certainly not both. It's BEGIN <STATEMENTS> END, not BEGIN ( <STATEMENTS> ) END. - Damien_The_Unbeliever

3 Answers

1
votes

I understand you use sql-server. SQL Server doesn't have REPLACE INTO. Instead of that you can use UPDATE statement : 

IF EXISTS 
(
     SELECT Bdt_DocumentDataAsBase64.Identifier
     FROM Bdt_DocumentDataAsBase64
     WHERE Bdt_DocumentDataAsBase64.Identifier = @Identifier
)    
BEGIN     
    UPDATE Bdt_DocumentDataAsBase64 SET Base64=@Base64
    WHERE Identifier = @Identifier    
END     
ELSE 
BEGIN 
    INSERT INTO Bdt_DocumentDataAsBase64(Identifier,Base64)
    VALUES (@Identifier, @Base64)
END
1
votes
Declare @cnt int
set @cnt=(case when exists (select top 1 * from Bdt_DocumentDataAsBase64
     WHERE Bdt_DocumentDataAsBase64.Identifier = @Identifier )then 1 else 0 end)

if @cnt=1
 begin
 update Bdt_DocumentDataAsBase64 set Base64=@Base64 where Identifier = @Identifier
 end

else
 begin 
  INSERT INTO Bdt_DocumentDataAsBase64(Identifier,Base64)
    VALUES (@Identifier, @Base64)

 end
0
votes

In sql server 2017 i tried this:

if exists( select * from [dbo].[BIS_Branch] where id = '2fe7339f-9d3d-4e31-971d-00161d975a56')
begin
    print('edit')
end
else
begin
    print('ok')
end

and I have no error. but I have error in "REPLACE INTO" !!