CODEWITHSUNDEEP
sql-servert-sql
Naveen Kumar
Naveen Kumar

Reputation: 632

Delete from Table variable with where condition

I'm deleting data from the table variable based on the condition like below.

DELETE FROM @SampleTable WHERE Fname IN (@Fname)

In @Fname I have values like 'abc','def','ghi'.

But the delete statement is not deleting the data. If I pass a SELECT statement in the condition it is deleting.

Upvotes: 0

Views: 1996

Answers (3)

Amira Bedhiafi
Amira Bedhiafi

Reputation: 1

DECLARE  @SampleTable TABLE (Fname VARCHAR(MAX))
DECLARE @Fname VARCHAR(MAX) ='abc,ghi'
INSERT INTO @SampleTable(Fname)VALUES('abc'),('bb'),('ghi')

DELETE FROM @SampleTable WHERE Fname IN ( SELECT value from  STRING_SPLIT(@Fname,','));
SELECT * FROM @SampleTable

GO

| Fname |
| :---- |
| bb    |

db<>fiddle here

Upvotes: 0

Ilyes
Ilyes

Reputation: 14928

You can use STRING_SPLIT() and REPLACE() as

declare @data table (name varchar(45));

insert @data values
('abc'),('def'),('ghi'),('jkl');

select * from @data;

declare @var varchar(100) = '''abc'',''def'',''ghi''';

delete from @data 
where name in(
              select value 
              from string_split(replace(@var, '''', ''), ',')
             );

select * from @data;

Online Demo

Upvotes: 2

Hong Van Vit
Hong Van Vit

Reputation: 2986

I thing, you can try some thing like this.

DECLARE @Query NVARCHAR(MAX)
SET @Query = 'DELETE FROM '+@SampleTable+' WHERE Fname IN ('+@Fname+')'
EXEC SP_EXECUTESQL @Query

Upvotes: 1

Related Questions