Good Morning,
I have a quick Question
Have a table called subtable with column Year(YYYY) format number.
so I am doing
DELETE FROM ORDERS WHERE YEAR(ORDER_DATE) IN
(SELECT YEAR_YYYY FROM subtable)
---> this will do what I need. the quetion I have is
How to delete all if the subquery returns no rows. in other words assume subtable is empty then how can i delete orders table data all together.
?
please advise
Thanks in advance
Asiti
Do I understand correctly? If Subtable is empty you want to delete the Orders table completely, but if there are rows in Subtable you want to limit the deletes to orders with dates matching the years contained in the Subtable data? Is that correct? and does this do what you need?
DROP TABLE IF EXISTS dbo.Orders
CREATE TABLE dbo.Orders
( OrderID INT IDENTITY(1,1),
Order_Date DATE,
Order_Code VARCHAR(10)
)
INSERT dbo.Orders VALUES
('2021-12-01', 'SDF'), ('2021-12-02', 'ERT'), ('2022-01-01', 'CBFGRT'),
('2022-02-01', 'DGRET'), ('2022-11-01', 'DGRTA'), ('2022-12-25', 'XMAS'),
('2023-01-01', 'fhtysvf'), ('2023-02-01', 'SFWERR'), ('2023-02-07', 'MBBD')
DROP TABLE IF EXISTS dbo.SubTable
CREATE TABLE dbo.SubTable (YEAR_YYYY INT )
INSERT dbo.SubTable VALUES (2022), (NULL), (2021)
SELECT a.OrderID, a.Order_Date, a.Order_code
--DELETE a
FROM dbo.Orders AS a
WHERE YEAR(ORDER_DATE) IN (SELECT YEAR_YYYY FROM subtable)
OR NOT EXISTS (SELECT 1 FROM dbo.SubTable)
DELETE dbo.SubTable
SELECT a.OrderID, a.Order_Date, a.Order_code
--DELETE a
FROM dbo.Orders AS a
WHERE YEAR(a.ORDER_DATE) IN (SELECT YEAR_YYYY FROM subtable)
OR NOT EXISTS (SELECT 1 FROM dbo.SubTable)
DROP TABLE IF EXISTS dbo.Orders
DROP TABLE IF EXISTS dbo.SubTable
February 27, 2023 at 5:56 pm
Thank you very much Ed B.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply