December 29, 2016 at 7:44 am
Hi John, Thank you very much for delete query.. how can I ensure which records will get delete by this query? I want to verify data before deleting. Could you please comment delete statement in the script and add select statement to know which records it will fetch. after verifying i'll uncomment delete and comment select statement. Thanks
December 29, 2016 at 7:48 am
I've given you a SELECT statement and a DELETE statement. What more do you need?
John
December 29, 2016 at 9:05 am
yes.. SELECT statement is to see latest timestamp rows..
DELETE statement is for to delete older timestamp rows and which will keep latest timestamp rows.. this is fine.
before going to execute DELETE script, like to verify the data that is going to be deleted. Therefore, request to put additional SELECT in DELETE script by commenting the line --DELETE FROM MMDDS01_PORTFO_LIST. Thanks
December 30, 2016 at 4:21 am
Hi John,
Any luck?? Thanks
December 30, 2016 at 4:28 am
I could ask you the same question. To verify the data that's going to be deleted, use the SELECT statement. To delete the data, use the DELETE statement. If you prefer it all in one script with commented-out lines, do that little extra bit yourself. It's not difficult.
John
December 30, 2016 at 5:03 am
i tried below query .. this is not working .. Could you suggest correct change? Thanks
WITH LatestDates AS (
SELECT
[MDDS01_MDD_STORE_GU_D]
,[MDDS01_CATALOG_D]
,[MDDS01_LAST_UPDT_S]
,ROW_NUMBER() OVER (PARTITION BY [MDDS01_CATALOG_D] ORDER BY [MDDS01_LAST_UPDT_S] DESC) AS RowNo
FROM [GFORCEV3].[dbo].[MMDDS01_MDD_STORE]
)
SELECT FROM [GFORCEV3].[dbo].[MMDDS01_MDD_STORE]
WHERE [MDDS01_MDD_STORE_GU_D] NOT IN (
SELECT [MDDS01_MDD_STORE_GU_D]
FROM LatestDates
WHERE RowNo = 1
)
December 30, 2016 at 7:00 am
Not working as in what - returns the wrong results, or gives an error? If the results are wrong, please provide in tabular form the results that you would expect from the sample data you provided. Were the modifications I made to your sample data (removing leading spaces, taking NULLs out of quotes) correct?
John
Edit - also, why have the names of the columns changed yet again?
December 30, 2016 at 7:12 am
SQLserver_learner (12/30/2016)
i tried below query .. this is not working .. Could you suggest correct change? ThanksWITH LatestDates AS (
SELECT
[MDDS01_MDD_STORE_GU_D]
,[MDDS01_CATALOG_D]
,[MDDS01_LAST_UPDT_S]
,ROW_NUMBER() OVER (PARTITION BY [MDDS01_CATALOG_D] ORDER BY [MDDS01_LAST_UPDT_S] DESC) AS RowNo
FROM [GFORCEV3].[dbo].[MMDDS01_MDD_STORE]
)
SELECT FROM [GFORCEV3].[dbo].[MMDDS01_MDD_STORE]
WHERE [MDDS01_MDD_STORE_GU_D] NOT IN (
SELECT [MDDS01_MDD_STORE_GU_D]
FROM LatestDates
WHERE RowNo = 1
)
1. Find out who developed this naming conventions and have them shot, drawn and quartered, cremated and their ashes scattered to the 4 corners of the earth! I can't image coding these column names all day long every day. 😉
2. Barring the possibility of #1, learn to use aliases.
3. Learn to use ifcode when posting!
4. Don't use 3 level naming
This query works with your original DDL and data, after fixing it up.
USE GFORCEV3;
GO
CREATE TABLE [dbo].[MMDDS01_PORTFO_LIST](
[MDDS01_UUI_D] [varchar](64) NOT NULL,
[MDDS01_PORTFO_D] [nvarchar](64) NULL,
[MDDS01_LAST_UPDT_TIME] [datetime] NOT NULL
CONSTRAINT [MMDDS01_PK] PRIMARY KEY CLUSTERED
(
[MDDS01_UUI_D] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY];
INSERT INTO [dbo].[MMDDS01_PORTFO_LIST] values
('02ce8663-72c7-4259-ab90-87c856ff3', NULL, '2016-03-06 22:31:39.710'),
('01a5a15e-fda8-45dc-b590-d92f279e8',' WAAX-CWB-2016-201625', '2016-03-21 21:32:00.873'),
('023e364f-061a-4fb9-99f5-161639d08','WADX-CZY-2015-DEU', '2016-05-11 19:43:14.587'),
('02b615b9-6944-4b43-bfe6-b017ab49',' WAEX-TTG-2012-201725', '2016-09-21 11:57:22.637'),
('00042e5d-d4e1-49f8-b2d3-a9778262', NULL, '2016-08-15 18:46:43.803'),
('025cc224-9cc6-4927-83f6-a12aac9a3','WADX-CZY-2015-DEU', '2016-11-09 12:09:01.303'),
('01de37a5-8ebb-4c35-a31a-26f25df85','WAAX-CWB-2016-201625', '2016-07-20 20:30:09.663'),
('027a753d-ab94-4b0e-b049-b9902b42','WADX-CZY-2015-DEU', '2016-12-06 18:59:12.540'),
('00432cd2-3d46-4f6b-95ff-12776c4e1',NULL, '2016-03-17 21:33:59.120'),
('a8a71e9a-77d6-400c-80de-08fd3a903','WAIX-CCN-2012-201650', '2016-12-12 20:35:04.830'),
('46671907-9eb5-4a2f-bf6b-1e3750902',NULL, '2016-12-17 16:41:32.807'),
('c2380517-4f9f-4a11-9012-53ebd1113', 'WAIX-CCN-2012-201650', '2016-12-09 12:05:49.643'),
('1381c3df-763a-47a8-947c-ebb31b2f1',NULL, '2016-11-28 13:17:26.000'),
('a029e35b-e6e3-482d-b834-3bf95b3ad','WAIX-CCN-2012-201650', '2016-12-09 04:12:40.267'),
('d10e6c6c-b172-435a-9ea2-1ac29c7ea','WAIX-CCN-2012-201650', '2016-12-09 11:41:00.670'),
('6913e1a5-5f0b-4c31-9d41-22e3df975','WAEX-CB2-2010-B-201600', '2016-12-01 18:53:38.893'),
('4407bf04-62d9-4f2d-a364-a31a751de','WAEX-CB2-2010-B-201600', '2016-11-30 15:26:45.253'),
('8c268304-181e-4f86-92ff-f26cacb3df','WAEX-CB2-2010-B-201600', '2016-11-30 17:44:55.827'),
('9f9cca48-a065-486d-944a-8e32d4cc6','WAEX-CB2-2010-B-201600', '2016-12-01 18:12:18.520'),
('04eb6b53-60fd-482a-9133-db549df38','WAEX-CB2-2010-B-201600', '2016-12-01 11:37:34.750');
To Delete from this data, uncomment the Delete and comment out the Select. To Select the data, uncomment the Select and comment out the Delete!
with LatestDates as
(
select PL.MDDS01_UUI_D UUI,
PL.MDDS01_PORTFO_D PORT,
PL.MDDS01_LAST_UPDT_TIME UpdateTime,
Row_Number() over(partition by MDDS01_PORTFO_D order by mdds01_LAST_UPDT_TIME desc) RowNum
from MMDDS01_PORTFO_LIST PL
)
select * from latestDates where RowNum > 1;
--Delete from LatestDate where RowNum > 1;
To see how this code works, comment out the where clause on the Select and then examine the output.
Note the use of aliases. Though not really helpful with this particular query, anything with a join or subquery and aliases will decrease the amount of typing and hopefully decrease typos and errors!
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
December 30, 2016 at 8:09 am
very nice.. very good query.. outstanding .. good knowledgeable.. thank you very much:-):-):-)
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply