SQL Server 2012 :: How To Delete Duplicate Rows Present In Parent Table
Oct 19, 2014
I have a master table and i need to import the rows into the parent and child table.
Master table name is Flatfile_Inventory
Parent Table name is INVENTORY
Child Tables name are INVENTORY_AMOUNT,INVENTORY_DETAILS,INVENTORY_VEHICLE,
Error details will be goes to LOG_INVENTORY_ERROR
I have 4 duplicate rows in the Flatfile_Inventory which i have already inserted in the Parent and child table.
Again when i run the query using stored procedure,its tells that all the 4 rows are duplicate and will move to the Log_Inventory_Error.
I need is if i have the duplicate rows in the flatfile_Inventory when i start inserting into the parent and child table the already inserted row have the unique ID i must identify it and delete that row in the both parent and chlid table.And latest row must get inserted into the Parent and child table from Flatfile_Inventory.
I have two tables that are related by keys. For instance,Table employee {last_name char(40) not null,first_name char(40) not null,department_name char(40) not null,age int not null,...}Employee table has a primary key (combination of last_name and first_name).Table address {last_name char(40) not null,first_name char(40) not null,street char(200) not null,city char(100) not null,...}Address table has a primary key (combination of last_name, first_name andstreet in which (last_name, first_name) reference (last_name, first_name) inemployee table.Now I want to delete some rows in Address table based on department_name inEmployee table. What is sql for this delete?I appreciate your help. Please ignore table design and I just use it for myproblem illustration.Jim
I've 2 tables QuestionAnswers and ConditionalQuestions and fetching data from them using CTE join and I'm seeing repetitive rows (not duplicate) like, If you have multiple answers for 1 question, the output is like
where london where paris where toronto
why us why japan why indonesia
I want to eliminate the repetitive question and group them as parent child items.
with cte as ( select cq.ConditionalQuestionID from ConditionalQuestions cq inner join QuestionAnswers qa on cq.QuestionID=qa.QuestionID where cq.QuestionID=5 and qa.IsConditional='Y') select distinct q.Question, a.Answer from QuestionAnswers qa inner join Answers a on a.AnswerID = qa.AnswerID inner join Questions q on q.QuestionID = qa.QuestionID inner join cte c on c.ConditionalQuestionID = qa.QuestionID;
I have the below query which in turn goes in the where clause
Code:
SELECT CASE WHEN EXISTS (SELECT child.id FROM InspectionType parent, InspectionType child WHERE parent.id = child.parentId AND parent.code = 'INSPECTION_TYPE_SAFETY_LIFE' AND parent.zoneId = 10129) THEN (SELECT child.id FROM InspectionType parent, InspectionType child WHERE parent.id = child.parentId AND parent.code ='INSPECTION_TYPE_SAFETY_LIFE' AND parent.zoneId = 10129) ELSE (SELECT id FROM InspectionType WHERE code = 'INSPECTION_TYPE_SAFETY_LIFE' AND zoneId = 10129) END
I am getting below error
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
I tried to solve this problem with If and else but it is not going well with in-clause
Code: select * from InspectionType where id in (select IF EXISTS (SELECT child.id FROM InspectionType parent, InspectionType child WHERE parent.id = child.parentId AND parent.code = 'INSPECTION_TYPE_SAFETY_LIFE' AND parent.zoneId = 10129) BEGIN (SELECT child.id FROM InspectionType parent, InspectionType child WHERE parent.id = child.parentId AND parent.code ='INSPECTION_TYPE_SAFETY_LIFE' AND parent.zoneId = 10129) END ELSE (SELECT id FROM InspectionType WHERE code = 'INSPECTION_TYPE_SAFETY_LIFE' AND zoneId = 10129) )
I am using select * from InspectionType where id in() for illustation only. The sub-query results will go another query
Code: SELECT child.id FROM InspectionType parent, InspectionType child WHERE parent.id = child.parentId AND parent.code ='INSPECTION_TYPE_SAFETY_LIFE' AND parent.zoneId = 10129
This returns more than one row.I know why is the error message but need to achieve this functionality.
I have a task to create a dts package, which will delete records in a SQL table, which are present on a sheet tab in Excel. I know how to transfer records from Excel to SQL table and vice versa, but not sure how I can delete the records in SQL table, which are present in Excel. Note that there are just 2 columns - one is a key and the other name.
I have a query that produces unique rows. However, some of the unique rows have the column called testname that has the same test listed more than once. All I want to do is drop the older testname and keep the testdate column.
Select Distinct TestID, TestDate, TestName
From third.test
I want to keep testdate in the query....guessing I need to put in to a temp table then drop the oldest one somehow by doing a subquery using Select Max....
CAN ANYBODY REPLY FOLLOWING QUESTIONS. I WANT TO DELETE DUPLICATE ROWS IN MY TABLE WITHOUT USING TRANSACTION TABLE. AND ONE MORE QUESTION HOW TO GET YESTERDAY DATE BY USING ISQL WINDOW.
declare @a1 table (id int not null identity(1,1), phone decimal(18,0), adress nvarchar(100)) insert @a1 (phone,adress)
[Code] ....
id phone address ----------- --------------------------------------- ---------------------------------------------------------------------------------------------------- 1 111 new york 2 111 new york 3 111 new york 4 222 maxico 5 222 mexico
select phone,count(phone) as say from @a1 group by phone having count(phone)>1
phone say --------------------------------------- ----------- 111 3 222 2
How can I remove duplicate phone. For example
after delete select*from @a1 select*from @a1
id phone address ----------- --------------------------------------- ---------------------------------------------------------------------------------------------------- 1 111 new york 4 222 maxico
I wrote this to show for example in my real table have 50000 rows and 1751 duplicate rows.
I have a table employee_test having the sample data. The rows with EmployeeID=6 are duplicate rows. I want to delete the duplicates retaining one row for the employeeid=6. Note :- I don't want to use a temporary table. I want to do this using a single query or at the most in a SP query batch. Please advise.
We have equipment table which stores Equipment_ID,Code,Parent_Id etc..for each Equipment_ID there is a Parent_Id. The PK is Equipment_ID Now i want to select the Code for the Parent_Id which also sits in the same table. All the Parent_Id's also are Equipment_ID's.
I am using SQL Server 2012 SE.I am trying to delete rows from a couple of tables (GetPersonValue has 250 million rows and I am trying to delete 50Million rows and GetPerson has 35 Million rows and I am trying to delete 20 million rows). These tables are in TX replication.The plan is to delete data older than 400 days old.
I tried to move data to new tables from the last 400 days and it took me like 11 hours. If I delete data in chunks of 500000 then its taking a long time to rebuild indexes(delete plus rebuild indexes 13 hours). Since I am using standard edition partition wont work.
find ddl below:
GO CREATE TABLE [dbo].[GetPerson]( [GetPersonId] [uniqueidentifier] NOT NULL, [LinedActivityPersonId] [uniqueidentifier] NOT NULL, [CTName] [nvarchar](100) NULL, [SNum] [nvarchar](50) NULL, [PHPrimary] [nvarchar](50) NULL,
I have a table which dont any Identity column. The data in the table has duplicate values, some rows are totally identical. I just want to select the row which has maximum Row Id/Row number if I am getting multiple rows in my select statement.
I have one table with 5 field, one of the field having unique values, and every month i append this table by using import/export wizard from excel, and that excel having some new value and some old value which is already in sql table, now i want such duplicate value delete automatic at the time of append the table or after that,
Currently i am doing this in excel, i delete duplicate data in excel and upload again whole data in sql.
I uploaded some data about 2 or 3 times and it keep appending it to thetable.Now I want to keep only first duplicate and delete rest of.Suppose part number 123 has been added 3 times so I want to keep only 1record.Thanks
Hello Frnds....Can anybody give the answer of this question as How to Delete duplicate records from Table ? I Know that with check option and also with Unique Constraint we can avoid to enter duplicate records in table but How to delete from table which does not have any constraints ?
I've following query which display the result as required, but I need to get the missing Primary Key Values which are not available in the result:
SELECTA.SignedByUserID, B.FullName, COUNT(A.OutletID) AS TotalSignups, DATENAME(Month, A.SignupDate) AS Month FROMdbo.tblMer_Outlet AS A LEFT OUTER JOIN dbo.tblGen_Users AS B ON A.SignedByUserID = B.UserID WHERE(A.SignupDate >= '2014-04-01 00:00:00' AND A.SignupDate <= '2014-04-30 23:59:59') GROUP BY A.SignedByUserID, B.FullName, DATENAME(Month, A.SignupDate)
This Query returns the following result:
SignedByUserID FullName TotalSignups Month -------------------------------------------------------- 9 Babu Raj 16 April 11 Faheem 19 April 39 Fasil Abbas 16 April 29 Hafiz Suleman 10 April
[code]....
which does not have a signup for the month of April, but I need it to be available for this or any upcoming month. I need this orr all users, which does not exists in the context needs to be displayed in the result.
I have a table with about 466 Million rows. In this table there is a int column called WeeksToRetain as well as a EventDate column containing the date the row was inserted. I am trying to delete all the rows that that should be deleted according to the WeeksToRetain. For example, if the EventDate is 5/07/15 with a 1 in the WeeksToRetain column the row should be removed by 5/14/15. I am not sure what days SQL considers the beginning and end of the week. However the core issue I am having is the sheer mass of deletions I must do and log growth.
So I am trying to do the delete in batches. More specifically I want to load a temporary table with a million rows, then use the temporary table to load a sub temporary table with 100,000 rows and join this temporary table to the table I want to delete from looping through 10 times to get 1 million. The Logging.EvenLog table which is the table I'm trying to purge has a clustered index on EventDate (ASC). I would like to run this in a schedule job with enough time between executions for log backups to run.
DECLARE @i int DECLARE @RowCount int DECLARE @NextBatchDate datetime CREATE TABLE #BatchProcess ( EventDate datetime, ApplicationID int,
I have a SQL script to insert data into a table as below:
INSERT into [SRV1INS2].BB.dbo.Agents2 select * from [SRV2INS14].DD.dbo.Agents
I just want to set a Trigger on Agents2 Table, which could delete all rows in the table , before carry out any Insert operation using above statement.I had below Table Trigger on [SRV1INS2].BB.dbo.Agents2 Table as below: But it did not perform what I intend to do.
USE [BB] GO /****** Object: Trigger Script Date: 24/07/2015 3:41:38 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON
