Recently I came across a CRM query which used FROM twice in the DELETE statement.
Below is an example of it in action and yes a warning the results can be a bit perplexing.
-- Create a table
CREATE TABLE your_table
(
id int IDENTITY (1,1)
,y_n char(1) NULL
);
GO
-- Create a view
CREATE VIEW your_view
AS
SELECT id,y_n
FROM your_table
WHERE y_n = 'y';
GO
-- Insert some data into the table
INSERT INTO your_table (y_n)
VALUES
('y')
,('y')
,('y')
,('y')
,('n');
GO
-- First DELETE test -- With the view after the first FROM with 'n' BEGIN TRAN DELETE FROM your_view FROM your_table t WHERE t.y_n = 'n'; -- You get 1 row returned SELECT * FROM [your_table] WITH (NOLOCK); ROLLBACK
-- Second DELETE test -- With the view after the first FROM with 'y' BEGIN TRAN DELETE FROM your_view FROM your_table t WHERE t.y_n = 'y'; -- You also get the same row returned SELECT * FROM your_table WITH (NOLOCK); ROLLBACK
-- Third DELETE test -- With the table after the first FROM with 'n' BEGIN TRAN DELETE FROM your_table FROM your_view v WHERE v.y_n = 'n'; -- You get all rows returned SELECT * FROM your_table WITH (NOLOCK); ROLLBACK
-- Final DELETE test -- With the table after the first FROM with 'y' BEGIN TRAN DELETE FROM your_table FROM your_view v WHERE v.y_n = 'y'; -- All rows are now deleted SELECT * FROM your_table WITH (NOLOCK); ROLLBACK
To remove the table and view use:
DROP VIEW your_view; DROP TABLE your_table;
