insert query results to "where in ()" query

Hello

I want to print 'delete from customers where id in (' + select id from persons+ ')'

It failes. How do I auto generate it to get the wanted resaults?

Thanks

Avi

I am not sure what you are trying here to achive.

If you want to print the string..

Print 'Delete from Customers Where Id in (Select Id from Persons)'

If you want to execte the query

Exec ('Delete From Customers Where Id in (Select Id from Persons)')

Ooops.. Its not clear.. Tell me more.. What you want to do..

|||

There is a delete command I need to do but I need to capture the current set of id. the same delete statment will not act the same a week ago.

I wan't to pring the query into a text file for futore rollback. the resault should be ' delete from X where id in (num1,num2,num3)'

|||

You are something looking for LOG Based deletion.

When you delete x rows you want to store it somewhere. Then later if you need you can revert back the changes..

Approach 1:

You can achive this using triggers.

Code Snippet

Create Table mydata (

[Id] int ,

[Name] Varchar(100)

);

Go

Create Table mylogdata (

[Id] int ,

[Name] Varchar(100) ,

[DeletedDatetime] datetime

);

Go

Create Trigger trg_MydataDeleteLoger

on Mydata For delete

as

Begin

Insert Into mylogdata

Select *,getdate() from Deleted;

End

Go

Insert Into mydata Values('1','Record1');

Insert Into mydata Values('2','Record2');

Insert Into mydata Values('3','Record3');

Insert Into mydata Values('4','Record4');

Insert Into mydata Values('5','Record5');

Go

Delete From Mydata Where Id<3

Select * From mydata

Select * From mylogdata

Approach 2:

Instead of deleting the rows use the IsDelete flag.

Code Snippet

Create Table mydata (

[Id] int ,

[Name] Varchar(100) ,

--attach the flag columns

IsDeleted bit Default 0,

DeletedDatetime datetime default null

);

|||

Isn't there a way to do it witha simple sql scritp?

|||

Reverting back your data after sometimes is not a simple task.

Your table may hold n numbers columns. Delete command is simply remove all the data.

So you should backup all the columns data before deleteing the row. Somewhere you have to keep that backup data

for future revert-back.

Trigger is one of the common way to achive this.. If you ask me, putting a IsDeleted flag is good practice too.

|||

All I want is to print the wanted string. isn't that possible? regardless of the cause or target. just print it.

Do I need to use cast or convert ?

Thanks

Avi

|||If

I want to print 'delete from customers where id in (' + select id from persons+ ')'

Then, the problem is handling the single quotes. To have a single quote in a PRINT string, use two single quotes. For example, the statement:

PRINT 'DELETE FROM Customers WHERE ID IN ('' + SELECT ID FROM Persons+ '')'

prints out:

DELETE FROM Customers WHERE ID IN (' + SELECT ID FROM Persons+ ')

|||

I would like to write

PRINT 'DELETE FROM Customers WHERE ID IN ('' + SELECT ID FROM Persons+ '')'

in the query analyzer and then press F5.

What I want to see in the resultpan is :

'DELETE FROM Customers WHERE ID IN (10,20,30)'

Can this be done ?

|||

No, a PRINT statement ONLY prints exactly what you tell it to print. It will not execute the statement [ SELECT ID FROM Persons ] and put the results in the PRINT output.

You could, however, 'build' the line you want printed as a variable [varchar()], and then print that variable. You would have to use a technique such as the one below to gather the output from the SELECT statement into a comma delimited list.

Code Snippet

SET NOCOUNT ON

DECLARE @.MyTable table
( RowID int IDENTITY,
CustomerName varchar(20)
)

DECLARE @.MyList varchar(1000)

INSERT INTO @.MyTable VALUES( 'Smith' )
INSERT INTO @.MyTable VALUES( 'Williams' )
INSERT INTO @.MyTable VALUES( 'O''Reilly' )
INSERT INTO @.MyTable VALUES( 'Jones' )
INSERT INTO @.MyTable VALUES( 'Johnson' )
INSERT INTO @.MyTable VALUES( 'Marvin' )

SELECT DISTINCT
@.MyList = substring( ( SELECT ', ' + cast( RowID as varchar(10)) as [text()]
FROM @.MyTable t2
WHERE t2.CustomerName <> t1.CustomerName
FOR XML path(''), elements
), 3, 1000
)
FROM @.MyTable t1

DECLARE @.MyPrintStatement varchar(2000)

SET @.MyPrintStatement = '''DELETE FROM Customers WHERE ID IN (' + @.MyList + ')'''

PRINT @.MyPrintStatement

'DELETE FROM Customers WHERE ID IN (2, 3, 4, 5, 6)'