Hi all..
I have a dataset named "Primas" that returns 3 records with 3 fields each.
I want to display a list of those records with a header and a summarize row.
To do that, I placed a Table control into the layout. Assigned "Primas" as
the dataset and I placed a textbox control into it with the value of
=Fields!CODIGOPRIMA.value (CODIGOPRIMA belongs to Primas dataset)
When I build the report, I get the error:
"Expression value of object CODIGOPRIMA reference field CODIGOPRIMA. Report
element expressions can only reference a field in actual dataset scope or, if
they are inside an aggregate, the specified dataset scope"
(I translated the messaege from Spanish, so I'm not sure if it is accurate,
but that's the idea).
The question.. why I get that message although I have the dataset specified
for that table? When I go to a field property inside the table, under
expressions, system shows me only the fields from a dataset that is the
parent of the table (a List)
Any help will be greately appreciated,
Thanks
JaimeYou might check that the Dataset is aware of the field you are trying to
use. You can do this by clicking on the Refresh button on the Data tab with
that dataset selected. I get that same message if I've added or changed a
field in the underlying database and forget to refresh the dataset.
Jared
"Jaime Stuardo" <JaimeStuardo@.discussions.microsoft.com> wrote in message
news:8C285D68-701D-451F-A2A8-363B80874025@.microsoft.com...
> Hi all..
> I have a dataset named "Primas" that returns 3 records with 3 fields each.
> I want to display a list of those records with a header and a summarize
> row.
> To do that, I placed a Table control into the layout. Assigned "Primas" as
> the dataset and I placed a textbox control into it with the value of
> =Fields!CODIGOPRIMA.value (CODIGOPRIMA belongs to Primas dataset)
> When I build the report, I get the error:
> "Expression value of object CODIGOPRIMA reference field CODIGOPRIMA.
> Report
> element expressions can only reference a field in actual dataset scope or,
> if
> they are inside an aggregate, the specified dataset scope"
> (I translated the messaege from Spanish, so I'm not sure if it is
> accurate,
> but that's the idea).
> The question.. why I get that message although I have the dataset
> specified
> for that table? When I go to a field property inside the table, under
> expressions, system shows me only the fields from a dataset that is the
> parent of the table (a List)
> Any help will be greately appreciated,
> Thanks
> Jaime|||Hi Tom...
I have done so but the same problem happens. And when I go to the field
value combobox, only dataset fields associated with the List are shown, not
table daaset fields.
Jaime
"Tom Rocco" wrote:
> You might check that the Dataset is aware of the field you are trying to
> use. You can do this by clicking on the Refresh button on the Data tab with
> that dataset selected. I get that same message if I've added or changed a
> field in the underlying database and forget to refresh the dataset.
> Jared
> "Jaime Stuardo" <JaimeStuardo@.discussions.microsoft.com> wrote in message
> news:8C285D68-701D-451F-A2A8-363B80874025@.microsoft.com...
> > Hi all..
> >
> > I have a dataset named "Primas" that returns 3 records with 3 fields each.
> >
> > I want to display a list of those records with a header and a summarize
> > row.
> > To do that, I placed a Table control into the layout. Assigned "Primas" as
> > the dataset and I placed a textbox control into it with the value of
> > =Fields!CODIGOPRIMA.value (CODIGOPRIMA belongs to Primas dataset)
> >
> > When I build the report, I get the error:
> > "Expression value of object CODIGOPRIMA reference field CODIGOPRIMA.
> > Report
> > element expressions can only reference a field in actual dataset scope or,
> > if
> > they are inside an aggregate, the specified dataset scope"
> > (I translated the messaege from Spanish, so I'm not sure if it is
> > accurate,
> > but that's the idea).
> >
> > The question.. why I get that message although I have the dataset
> > specified
> > for that table? When I go to a field property inside the table, under
> > expressions, system shows me only the fields from a dataset that is the
> > parent of the table (a List)
> >
> > Any help will be greately appreciated,
> > Thanks
> > Jaime
>
>
Showing posts with label records. Show all posts
Showing posts with label records. Show all posts
Friday, March 30, 2012
Wednesday, March 28, 2012
How to Determine the unique IDs of duplicated records
> This is a common problem with some solution
/************************************************** *********************************
*
* Problem:
* Determine the Duplicated Records in a table using single SELECT.
*
* We shall be using Northwind database, add some duplicate records.
*
* Here we want to know if 2 columns (CompanyName,
* PHone) are duplicated in a table.
*
*
* ShipperID CompanyName Phone
* ---- -------- ------
* 1 Speedy Express (503) 555-9831
* 2 United Package (503) 555-3199
* 3 Federal Shipping (503) 555-9931
* 4 Federal Shipping (503) 555-9931
* 5 Speedy Express (503) 555-9831
* 6 Federal Shipping (503) 555-9931
*
*
*
************************************************** **/
==================================================
SOLUTION 1: Gives me the IDs that are duplicated.
==================================================
SELECT
ShipperID, CompanyName, Phone
FROM
SHIPPERS
WHERE
EXISTS (
SELECT
NULL
FROM
SHIPPERS b
WHERE
b.CompanyName = SHIPPERS.CompanyName
AND b.Phone = SHIPPERS.Phone
GROUP BY
b.CompanyName, b.Phone
HAVING
SHIPPERS.ShipperID < MAX( b.ShipperID )
)
/* ********************
* Output results
********************/
ShipperID CompanyName Phone
---- ------------
--------
1 Speedy Express (503) 555-9831
3 Federal Shipping (503) 555-9931
4 Federal Shipping (503) 555-9931
(3 row(s) affected)
================================================== ===========
SOLUTION 2: Gives me the data which are duplicate but
not the IDs
================================================== ===========
SELECT
CompanyName, Phone
FROM
SHIPPERS
GROUP BY
CompanyName, Phone
HAVING
COUNT(*) > 1
/* ********************
* Output results
********************/
CompanyName Phone
------------ --------
Speedy Express (503) 555-9831
Federal Shipping (503) 555-9931
(2 row(s) affected)anonieko@.hotmail.com wrote:
> > This is a common problem with some solution
> /************************************************** *********************************
> *
> * Problem:
> * Determine the Duplicated Records in a table using single SELECT.
> *
> * We shall be using Northwind database, add some duplicate records.
> *
> * Here we want to know if 2 columns (CompanyName,
> * PHone) are duplicated in a table.
> *
> *
> * ShipperID CompanyName Phone
> * ---- -------- ------
> * 1 Speedy Express (503) 555-9831
> * 2 United Package (503) 555-3199
> * 3 Federal Shipping (503) 555-9931
> * 4 Federal Shipping (503) 555-9931
> * 5 Speedy Express (503) 555-9831
> * 6 Federal Shipping (503) 555-9931
> *
> *
> *
> ************************************************** **/
> ==================================================
> SOLUTION 1: Gives me the IDs that are duplicated.
> ==================================================
> SELECT
> ShipperID, CompanyName, Phone
> FROM
> SHIPPERS
> WHERE
> EXISTS (
> SELECT
> NULL
> FROM
> SHIPPERS b
> WHERE
> b.CompanyName = SHIPPERS.CompanyName
> AND b.Phone = SHIPPERS.Phone
> GROUP BY
> b.CompanyName, b.Phone
> HAVING
> SHIPPERS.ShipperID < MAX( b.ShipperID )
> )
> /* ********************
> * Output results
> ********************/
> ShipperID CompanyName Phone
> ---- ------------
> --------
> 1 Speedy Express (503) 555-9831
> 3 Federal Shipping (503) 555-9931
> 4 Federal Shipping (503) 555-9931
> (3 row(s) affected)
>
> ================================================== ===========
> SOLUTION 2: Gives me the data which are duplicate but
> not the IDs
> ================================================== ===========
>
> SELECT
> CompanyName, Phone
> FROM
> SHIPPERS
> GROUP BY
> CompanyName, Phone
> HAVING
> COUNT(*) > 1
>
> /* ********************
> * Output results
> ********************/
>
> CompanyName Phone
> ------------ --------
> Speedy Express (503) 555-9831
> Federal Shipping (503) 555-9931
> (2 row(s) affected)
Those aren't solutions, they are diagnostics. The solution is to fix
the stupid design of the Shippers table by adding a proper key.
:-)
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--sql
how to determine the best timeout value
Hi,
I am trying to insert 75000+ records into a table via a stored procedure
(the table is empty) - the information for all those records is contained in
an xml document that is passed to the stored procedure in a string. I am
using openxml to read the xml data and to insert the records into the table:
The statement is really simple and along the lines of the example below
INSERT INTO TableA
{
SELECT CustomerId,
CustomerName
FROM
OPENXML (@.XMLDataDocHandle,'Customers/Customer',2)
WITH
(
CustomerId int 'CustomerId',
CustomerName varchar(100) 'CustomerName'
)
}
The stored procedure is executed by an application using ADO.
Sometimes the execution of this stored procedure exceeds the connection time
out (30s) and a Timeout expired exception is thrown. This happens
intermittently - so I cannot re-produce this problem at will.
It would probably best to insert the records in batches but this cannot be
done for various reasons. The only other option that I can see is to increas
e
the timeout value - however how do I determine the best value for the
timeout?
If I run the code that executes the stored procedure it executes fine within
the given timeout period (and then sometimes it doesn't and I cannot find th
e
determinant that would cause it to happen! .v.) ...also I cannot execute
the stored procedure from query analyser etc. as the xml document string is
too long to be supplied as a parameter there - and using a small document
does not cause the problem...
BTW - Has anyone an idea what could be causing the time out in the first
place?
This is driving me insane - Please help anyone?!Are you sure you are not being blocked when you timeout? Use sp_who2
periodically as the insert is happening to ensure you are not being blocked.
But you should really look at using BULK INSERT instead. This would require
you to convert the format of the file from XML to some type of delimited
file but should yield dramatically faster results.
Andrew J. Kelly SQL MVP
"jalie" <jalie@.discussions.microsoft.com> wrote in message
news:FAF5E01F-F305-4F01-AE7F-1063214EF685@.microsoft.com...
> Hi,
> I am trying to insert 75000+ records into a table via a stored procedure
> (the table is empty) - the information for all those records is contained
> in
> an xml document that is passed to the stored procedure in a string. I am
> using openxml to read the xml data and to insert the records into the
> table:
> The statement is really simple and along the lines of the example below
> INSERT INTO TableA
> {
> SELECT CustomerId,
> CustomerName
> FROM
> OPENXML (@.XMLDataDocHandle,'Customers/Customer',2)
> WITH
> (
> CustomerId int 'CustomerId',
> CustomerName varchar(100) 'CustomerName'
> )
> }
> The stored procedure is executed by an application using ADO.
> Sometimes the execution of this stored procedure exceeds the connection
> time
> out (30s) and a Timeout expired exception is thrown. This happens
> intermittently - so I cannot re-produce this problem at will.
> It would probably best to insert the records in batches but this cannot be
> done for various reasons. The only other option that I can see is to
> increase
> the timeout value - however how do I determine the best value for the
> timeout?
> If I run the code that executes the stored procedure it executes fine
> within
> the given timeout period (and then sometimes it doesn't and I cannot find
> the
> determinant that would cause it to happen! .v.) ...also I cannot execute
> the stored procedure from query analyser etc. as the xml document string
> is
> too long to be supplied as a parameter there - and using a small document
> does not cause the problem...
> BTW - Has anyone an idea what could be causing the time out in the first
> place?
> This is driving me insane - Please help anyone?!
>
I am trying to insert 75000+ records into a table via a stored procedure
(the table is empty) - the information for all those records is contained in
an xml document that is passed to the stored procedure in a string. I am
using openxml to read the xml data and to insert the records into the table:
The statement is really simple and along the lines of the example below
INSERT INTO TableA
{
SELECT CustomerId,
CustomerName
FROM
OPENXML (@.XMLDataDocHandle,'Customers/Customer',2)
WITH
(
CustomerId int 'CustomerId',
CustomerName varchar(100) 'CustomerName'
)
}
The stored procedure is executed by an application using ADO.
Sometimes the execution of this stored procedure exceeds the connection time
out (30s) and a Timeout expired exception is thrown. This happens
intermittently - so I cannot re-produce this problem at will.
It would probably best to insert the records in batches but this cannot be
done for various reasons. The only other option that I can see is to increas
e
the timeout value - however how do I determine the best value for the
timeout?
If I run the code that executes the stored procedure it executes fine within
the given timeout period (and then sometimes it doesn't and I cannot find th
e
determinant that would cause it to happen! .v.) ...also I cannot execute
the stored procedure from query analyser etc. as the xml document string is
too long to be supplied as a parameter there - and using a small document
does not cause the problem...
BTW - Has anyone an idea what could be causing the time out in the first
place?
This is driving me insane - Please help anyone?!Are you sure you are not being blocked when you timeout? Use sp_who2
periodically as the insert is happening to ensure you are not being blocked.
But you should really look at using BULK INSERT instead. This would require
you to convert the format of the file from XML to some type of delimited
file but should yield dramatically faster results.
Andrew J. Kelly SQL MVP
"jalie" <jalie@.discussions.microsoft.com> wrote in message
news:FAF5E01F-F305-4F01-AE7F-1063214EF685@.microsoft.com...
> Hi,
> I am trying to insert 75000+ records into a table via a stored procedure
> (the table is empty) - the information for all those records is contained
> in
> an xml document that is passed to the stored procedure in a string. I am
> using openxml to read the xml data and to insert the records into the
> table:
> The statement is really simple and along the lines of the example below
> INSERT INTO TableA
> {
> SELECT CustomerId,
> CustomerName
> FROM
> OPENXML (@.XMLDataDocHandle,'Customers/Customer',2)
> WITH
> (
> CustomerId int 'CustomerId',
> CustomerName varchar(100) 'CustomerName'
> )
> }
> The stored procedure is executed by an application using ADO.
> Sometimes the execution of this stored procedure exceeds the connection
> time
> out (30s) and a Timeout expired exception is thrown. This happens
> intermittently - so I cannot re-produce this problem at will.
> It would probably best to insert the records in batches but this cannot be
> done for various reasons. The only other option that I can see is to
> increase
> the timeout value - however how do I determine the best value for the
> timeout?
> If I run the code that executes the stored procedure it executes fine
> within
> the given timeout period (and then sometimes it doesn't and I cannot find
> the
> determinant that would cause it to happen! .v.) ...also I cannot execute
> the stored procedure from query analyser etc. as the xml document string
> is
> too long to be supplied as a parameter there - and using a small document
> does not cause the problem...
> BTW - Has anyone an idea what could be causing the time out in the first
> place?
> This is driving me insane - Please help anyone?!
>
how to determine the best timeout value
Hi,
I am trying to insert 75000+ records into a table via a stored procedure
(the table is empty) - the information for all those records is contained in
an xml document that is passed to the stored procedure in a string. I am
using openxml to read the xml data and to insert the records into the table:
The statement is really simple and along the lines of the example below
INSERT INTO TableA
{
SELECT CustomerId,
CustomerName
FROM
OPENXML (@.XMLDataDocHandle,'Customers/Customer',2)
WITH
(
CustomerId int 'CustomerId',
CustomerName varchar(100) 'CustomerName'
)
}
The stored procedure is executed by an application using ADO.
Sometimes the execution of this stored procedure exceeds the connection time
out (30s) and a Timeout expired exception is thrown. This happens
intermittently - so I cannot re-produce this problem at will.
It would probably best to insert the records in batches but this cannot be
done for various reasons. The only other option that I can see is to increase
the timeout value - however how do I determine the best value for the
timeout?
If I run the code that executes the stored procedure it executes fine within
the given timeout period (and then sometimes it doesn't and I cannot find the
determinant that would cause it to happen! .v.) ...also I cannot execute
the stored procedure from query analyser etc. as the xml document string is
too long to be supplied as a parameter there - and using a small document
does not cause the problem...
BTW - Has anyone an idea what could be causing the time out in the first
place?
This is driving me insane - Please help anyone?!
Are you sure you are not being blocked when you timeout? Use sp_who2
periodically as the insert is happening to ensure you are not being blocked.
But you should really look at using BULK INSERT instead. This would require
you to convert the format of the file from XML to some type of delimited
file but should yield dramatically faster results.
Andrew J. Kelly SQL MVP
"jalie" <jalie@.discussions.microsoft.com> wrote in message
news:FAF5E01F-F305-4F01-AE7F-1063214EF685@.microsoft.com...
> Hi,
> I am trying to insert 75000+ records into a table via a stored procedure
> (the table is empty) - the information for all those records is contained
> in
> an xml document that is passed to the stored procedure in a string. I am
> using openxml to read the xml data and to insert the records into the
> table:
> The statement is really simple and along the lines of the example below
> INSERT INTO TableA
> {
> SELECT CustomerId,
> CustomerName
> FROM
> OPENXML (@.XMLDataDocHandle,'Customers/Customer',2)
> WITH
> (
> CustomerId int 'CustomerId',
> CustomerName varchar(100) 'CustomerName'
> )
> }
> The stored procedure is executed by an application using ADO.
> Sometimes the execution of this stored procedure exceeds the connection
> time
> out (30s) and a Timeout expired exception is thrown. This happens
> intermittently - so I cannot re-produce this problem at will.
> It would probably best to insert the records in batches but this cannot be
> done for various reasons. The only other option that I can see is to
> increase
> the timeout value - however how do I determine the best value for the
> timeout?
> If I run the code that executes the stored procedure it executes fine
> within
> the given timeout period (and then sometimes it doesn't and I cannot find
> the
> determinant that would cause it to happen! .v.) ...also I cannot execute
> the stored procedure from query analyser etc. as the xml document string
> is
> too long to be supplied as a parameter there - and using a small document
> does not cause the problem...
> BTW - Has anyone an idea what could be causing the time out in the first
> place?
> This is driving me insane - Please help anyone?!
>
I am trying to insert 75000+ records into a table via a stored procedure
(the table is empty) - the information for all those records is contained in
an xml document that is passed to the stored procedure in a string. I am
using openxml to read the xml data and to insert the records into the table:
The statement is really simple and along the lines of the example below
INSERT INTO TableA
{
SELECT CustomerId,
CustomerName
FROM
OPENXML (@.XMLDataDocHandle,'Customers/Customer',2)
WITH
(
CustomerId int 'CustomerId',
CustomerName varchar(100) 'CustomerName'
)
}
The stored procedure is executed by an application using ADO.
Sometimes the execution of this stored procedure exceeds the connection time
out (30s) and a Timeout expired exception is thrown. This happens
intermittently - so I cannot re-produce this problem at will.
It would probably best to insert the records in batches but this cannot be
done for various reasons. The only other option that I can see is to increase
the timeout value - however how do I determine the best value for the
timeout?
If I run the code that executes the stored procedure it executes fine within
the given timeout period (and then sometimes it doesn't and I cannot find the
determinant that would cause it to happen! .v.) ...also I cannot execute
the stored procedure from query analyser etc. as the xml document string is
too long to be supplied as a parameter there - and using a small document
does not cause the problem...
BTW - Has anyone an idea what could be causing the time out in the first
place?
This is driving me insane - Please help anyone?!
Are you sure you are not being blocked when you timeout? Use sp_who2
periodically as the insert is happening to ensure you are not being blocked.
But you should really look at using BULK INSERT instead. This would require
you to convert the format of the file from XML to some type of delimited
file but should yield dramatically faster results.
Andrew J. Kelly SQL MVP
"jalie" <jalie@.discussions.microsoft.com> wrote in message
news:FAF5E01F-F305-4F01-AE7F-1063214EF685@.microsoft.com...
> Hi,
> I am trying to insert 75000+ records into a table via a stored procedure
> (the table is empty) - the information for all those records is contained
> in
> an xml document that is passed to the stored procedure in a string. I am
> using openxml to read the xml data and to insert the records into the
> table:
> The statement is really simple and along the lines of the example below
> INSERT INTO TableA
> {
> SELECT CustomerId,
> CustomerName
> FROM
> OPENXML (@.XMLDataDocHandle,'Customers/Customer',2)
> WITH
> (
> CustomerId int 'CustomerId',
> CustomerName varchar(100) 'CustomerName'
> )
> }
> The stored procedure is executed by an application using ADO.
> Sometimes the execution of this stored procedure exceeds the connection
> time
> out (30s) and a Timeout expired exception is thrown. This happens
> intermittently - so I cannot re-produce this problem at will.
> It would probably best to insert the records in batches but this cannot be
> done for various reasons. The only other option that I can see is to
> increase
> the timeout value - however how do I determine the best value for the
> timeout?
> If I run the code that executes the stored procedure it executes fine
> within
> the given timeout period (and then sometimes it doesn't and I cannot find
> the
> determinant that would cause it to happen! .v.) ...also I cannot execute
> the stored procedure from query analyser etc. as the xml document string
> is
> too long to be supplied as a parameter there - and using a small document
> does not cause the problem...
> BTW - Has anyone an idea what could be causing the time out in the first
> place?
> This is driving me insane - Please help anyone?!
>
Wednesday, March 7, 2012
How To Delete Rows In SQL
Does the SQL DELETE command actually delete records or just mark them for deletion.
And if so, how do you force a physical deletion? (code required). Thanks.
Dim command As SqlClient.SqlCommand = New SqlClient.SqlCommand("DELETE FROM Table WHERE Day=" & "Monday", connection)
connection.Open()
command.ExecuteNonQuery()
connection.Close()
Yes, the delete command physically deletes data.|||It's funny how my dB size increased after I ran the Delete command.
It's up 29MB, but I'm not sure how many records I deleted and what's the root cause of the increase and what's in that increase. I did do a test preview and it did reflect the correct data. Oh well.
|||at first u execute DBCC SHRINKDATABASE command.
Then you check your database size.
|||Do you have an example... wouldn't want to ruin a database...|||Just use the command mentioned above:DBCC SHRINKDatabase(<name>,<RestofthecurrentSize>)
Information about that can be found int he BOL (Books Online, the help of SQL Server)
HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||I wouldn't just arbitrarily shrink the database.
First of all, you should understand that your database is made of 2 types of files, data files and log files. 1 of each by default.
When you delete data, it physically deletes it from the data file, but the data file is not resized, it just has more free space. Meanwhile, the delete transaction is written to your log file. If your log file was full at the time of the transaction, the log file is expanded to make room. The file isn't grown just enough to fit the new transaction, it is expanded by a pre-determined amount. By default, your log file expands by 10% when it needs to expand. So if the log file is 100 MB, it expands by 10 MB to have a new size of 110MB. If your log file is 10 GB, it expands by 1 GB to a size of 11 GB.
Data and log file size is very important. It is key to keep enough free space in them that the files do not need to be resized frequently. Resizing files, whether expanding or shrinking, has a big impact on performance. You want to avoid resizing arbitrarily. And you want to limit sizing when possible.
How to Delete Records that are Linked with Relationships
Hello,
I am writing to ask if someone can tell me what the
command is to delete rows in an SQL 2000 database that are
linked through a foreign key relationship.
For example, I have a row in a "Persons" table that has a
primary key "Person ID". "Person ID" is then a foreign
key in two other tables. I would like to be able to
delete a person row from the "Persons" table and then
automatically have all associated rows based on
that "Person ID" in the other two tables deleted.
Thanks in advance!
MikeIn the design for the Persons table, open the relationship and make sure
'cascade delete' is on. This should do what you're asking...
Hope this helps...
"Mike Rogan" <mrogan@.carolinawebdev.com> wrote in message
news:046101c35559$bd142450$a401280a@.phx.gbl...
> Hello,
> I am writing to ask if someone can tell me what the
> command is to delete rows in an SQL 2000 database that are
> linked through a foreign key relationship.
> For example, I have a row in a "Persons" table that has a
> primary key "Person ID". "Person ID" is then a foreign
> key in two other tables. I would like to be able to
> delete a person row from the "Persons" table and then
> automatically have all associated rows based on
> that "Person ID" in the other two tables deleted.
> Thanks in advance!
> Mike
I am writing to ask if someone can tell me what the
command is to delete rows in an SQL 2000 database that are
linked through a foreign key relationship.
For example, I have a row in a "Persons" table that has a
primary key "Person ID". "Person ID" is then a foreign
key in two other tables. I would like to be able to
delete a person row from the "Persons" table and then
automatically have all associated rows based on
that "Person ID" in the other two tables deleted.
Thanks in advance!
MikeIn the design for the Persons table, open the relationship and make sure
'cascade delete' is on. This should do what you're asking...
Hope this helps...
"Mike Rogan" <mrogan@.carolinawebdev.com> wrote in message
news:046101c35559$bd142450$a401280a@.phx.gbl...
> Hello,
> I am writing to ask if someone can tell me what the
> command is to delete rows in an SQL 2000 database that are
> linked through a foreign key relationship.
> For example, I have a row in a "Persons" table that has a
> primary key "Person ID". "Person ID" is then a foreign
> key in two other tables. I would like to be able to
> delete a person row from the "Persons" table and then
> automatically have all associated rows based on
> that "Person ID" in the other two tables deleted.
> Thanks in advance!
> Mike
How to delete records on dependent tables? Thank You.
Hello,
I am creating my first procedures in SQL using SQL 2005.
I have 3 tables, with the following columns:
Surveys - [SurveyId](PK) and [SurveyName]
Questions - [SurveyId](FK), [SurveyQuestionId](PK) and [SurveyQuestion]
Answers - [SurveyQuestionId](FK), [SurveyAnswerId](PK) and
[SurveyAnswer]
Each survay can include various questions and each question can include
several answers.
This is way I am using the Foreign Keys in both Questions and Answers
tables. To relate the tables.
I created a procedure which deletes a Survey given its SurveyId. This is
part is done.
I also need to delete all the questions dependent on that survey and all
the answers dependent on those questions.
How can I delete survey, its questions and their answers when receiving
the SurveyId?
Thank You Very Much,
Miguel
Here is the code of the procedure that I created which in this moment
only deletes the survey from the Surveys table:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[Surveys_DeleteSurvey]
-- Procedure Parameters
@.SurveyId As uniqueidentifier
AS
BEGIN
-- Check if SurveyId is null
IF( @.SurveyId IS NULL )
RETURN -1
ELSE
BEGIN
-- Return '-1' if a survey with SurveyId given value is not found
IF( NOT EXISTS( SELECT @.SurveyId FROM dbo.Surveys WHERE @.SurveyId =
SurveyId ) )
RETURN -1
END
-- Delete the survey with SurveyId given value
DELETE FROM dbo.Surveys WHERE @.SurveyId = SurveyId
-- Return '0' when successful
RETURN 0
ENDYou just need to add cascade delete to your foreign key constraints and the
database will do this automatically.
This assumes that you always want to delete the related records.
"Miguel Dias Moura" <md*REMOVE*moura@.gmail*NOSPAM*.com> wrote in message
news:%232tUEB1TGHA.6048@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I am creating my first procedures in SQL using SQL 2005.
> I have 3 tables, with the following columns:
> Surveys - [SurveyId](PK) and [SurveyName]
> Questions - [SurveyId](FK), [SurveyQuestionId](PK) and [SurveyQuestion]
> Answers - [SurveyQuestionId](FK), [SurveyAnswerId](PK) and
> [SurveyAnswer]
> Each survay can include various questions and each question can include
> several answers.
> This is way I am using the Foreign Keys in both Questions and Answers
> tables. To relate the tables.
> I created a procedure which deletes a Survey given its SurveyId. This is
> part is done.
> I also need to delete all the questions dependent on that survey and all
> the answers dependent on those questions.
> How can I delete survey, its questions and their answers when receiving
> the SurveyId?
> Thank You Very Much,
> Miguel
> Here is the code of the procedure that I created which in this moment
> only deletes the survey from the Surveys table:
> set ANSI_NULLS ON
> set QUOTED_IDENTIFIER ON
> go
>
> ALTER PROCEDURE [dbo].[Surveys_DeleteSurvey]
> -- Procedure Parameters
> @.SurveyId As uniqueidentifier
> AS
> BEGIN
> -- Check if SurveyId is null
> IF( @.SurveyId IS NULL )
> RETURN -1
> ELSE
> BEGIN
> -- Return '-1' if a survey with SurveyId given value is not found
> IF( NOT EXISTS( SELECT @.SurveyId FROM dbo.Surveys WHERE @.SurveyId =
> SurveyId ) )
> RETURN -1
> END
> -- Delete the survey with SurveyId given value
> DELETE FROM dbo.Surveys WHERE @.SurveyId = SurveyId
> -- Return '0' when successful
> RETURN 0
> END
>|||Hi,
Could you, please, explain how to add cascade delete to my foreign key
constraints.
I am starting with SQL and I have no idea how to do that.
Thanks,
Miguel
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:OsssqG1TGHA.2656@.TK2MSFTNGP10.phx.gbl:
> You just need to add cascade delete to your foreign key constraints and th
e
> database will do this automatically.
> This assumes that you always want to delete the related records.
> "Miguel Dias Moura" <md*REMOVE*moura@.gmail*NOSPAM*.com> wrote in message
> news:%232tUEB1TGHA.6048@.TK2MSFTNGP11.phx.gbl...|||It is best to look it up in Books OnLine, or check with your DBA.
Here is an example of the syntax, however.
ALTER TABLE [owner].[tablename] ADD CONSTRAINT
[constraintname] Foreign KEY
(
[Columnname]
) REFERENCES [owner].[OtherTablename] (
[Columnname]
) ON DELETE CASCADE ON UPDATE CASCADE
GO
"Miguel Dias Moura" <md*REMOVE*moura@.gmail*NOSPAM*.com> wrote in message
news:ujxYNCOVGHA.4300@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Could you, please, explain how to add cascade delete to my foreign key
> constraints.
> I am starting with SQL and I have no idea how to do that.
> Thanks,
> Miguel
> "Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
> news:OsssqG1TGHA.2656@.TK2MSFTNGP10.phx.gbl:
>
the
[SurveyQuestion]
include
is
all
receiving
>
I am creating my first procedures in SQL using SQL 2005.
I have 3 tables, with the following columns:
Surveys - [SurveyId](PK) and [SurveyName]
Questions - [SurveyId](FK), [SurveyQuestionId](PK) and [SurveyQuestion]
Answers - [SurveyQuestionId](FK), [SurveyAnswerId](PK) and
[SurveyAnswer]
Each survay can include various questions and each question can include
several answers.
This is way I am using the Foreign Keys in both Questions and Answers
tables. To relate the tables.
I created a procedure which deletes a Survey given its SurveyId. This is
part is done.
I also need to delete all the questions dependent on that survey and all
the answers dependent on those questions.
How can I delete survey, its questions and their answers when receiving
the SurveyId?
Thank You Very Much,
Miguel
Here is the code of the procedure that I created which in this moment
only deletes the survey from the Surveys table:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[Surveys_DeleteSurvey]
-- Procedure Parameters
@.SurveyId As uniqueidentifier
AS
BEGIN
-- Check if SurveyId is null
IF( @.SurveyId IS NULL )
RETURN -1
ELSE
BEGIN
-- Return '-1' if a survey with SurveyId given value is not found
IF( NOT EXISTS( SELECT @.SurveyId FROM dbo.Surveys WHERE @.SurveyId =
SurveyId ) )
RETURN -1
END
-- Delete the survey with SurveyId given value
DELETE FROM dbo.Surveys WHERE @.SurveyId = SurveyId
-- Return '0' when successful
RETURN 0
ENDYou just need to add cascade delete to your foreign key constraints and the
database will do this automatically.
This assumes that you always want to delete the related records.
"Miguel Dias Moura" <md*REMOVE*moura@.gmail*NOSPAM*.com> wrote in message
news:%232tUEB1TGHA.6048@.TK2MSFTNGP11.phx.gbl...
> Hello,
> I am creating my first procedures in SQL using SQL 2005.
> I have 3 tables, with the following columns:
> Surveys - [SurveyId](PK) and [SurveyName]
> Questions - [SurveyId](FK), [SurveyQuestionId](PK) and [SurveyQuestion]
> Answers - [SurveyQuestionId](FK), [SurveyAnswerId](PK) and
> [SurveyAnswer]
> Each survay can include various questions and each question can include
> several answers.
> This is way I am using the Foreign Keys in both Questions and Answers
> tables. To relate the tables.
> I created a procedure which deletes a Survey given its SurveyId. This is
> part is done.
> I also need to delete all the questions dependent on that survey and all
> the answers dependent on those questions.
> How can I delete survey, its questions and their answers when receiving
> the SurveyId?
> Thank You Very Much,
> Miguel
> Here is the code of the procedure that I created which in this moment
> only deletes the survey from the Surveys table:
> set ANSI_NULLS ON
> set QUOTED_IDENTIFIER ON
> go
>
> ALTER PROCEDURE [dbo].[Surveys_DeleteSurvey]
> -- Procedure Parameters
> @.SurveyId As uniqueidentifier
> AS
> BEGIN
> -- Check if SurveyId is null
> IF( @.SurveyId IS NULL )
> RETURN -1
> ELSE
> BEGIN
> -- Return '-1' if a survey with SurveyId given value is not found
> IF( NOT EXISTS( SELECT @.SurveyId FROM dbo.Surveys WHERE @.SurveyId =
> SurveyId ) )
> RETURN -1
> END
> -- Delete the survey with SurveyId given value
> DELETE FROM dbo.Surveys WHERE @.SurveyId = SurveyId
> -- Return '0' when successful
> RETURN 0
> END
>|||Hi,
Could you, please, explain how to add cascade delete to my foreign key
constraints.
I am starting with SQL and I have no idea how to do that.
Thanks,
Miguel
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:OsssqG1TGHA.2656@.TK2MSFTNGP10.phx.gbl:
> You just need to add cascade delete to your foreign key constraints and th
e
> database will do this automatically.
> This assumes that you always want to delete the related records.
> "Miguel Dias Moura" <md*REMOVE*moura@.gmail*NOSPAM*.com> wrote in message
> news:%232tUEB1TGHA.6048@.TK2MSFTNGP11.phx.gbl...|||It is best to look it up in Books OnLine, or check with your DBA.
Here is an example of the syntax, however.
ALTER TABLE [owner].[tablename] ADD CONSTRAINT
[constraintname] Foreign KEY
(
[Columnname]
) REFERENCES [owner].[OtherTablename] (
[Columnname]
) ON DELETE CASCADE ON UPDATE CASCADE
GO
"Miguel Dias Moura" <md*REMOVE*moura@.gmail*NOSPAM*.com> wrote in message
news:ujxYNCOVGHA.4300@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Could you, please, explain how to add cascade delete to my foreign key
> constraints.
> I am starting with SQL and I have no idea how to do that.
> Thanks,
> Miguel
> "Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
> news:OsssqG1TGHA.2656@.TK2MSFTNGP10.phx.gbl:
>
the
[SurveyQuestion]
include
is
all
receiving
>
Friday, February 24, 2012
How to delete duplicate rows
Hi,
Currently we are having one large table in that we are having more than
1 lacs record but most of the records are duplicates(All columns are having
same values).
How can i delete the particular duplicate row?
Please give me a solution as soon as possible
Thanks,
Herbert
Hi
This script has written by Itzik Ben-Gan
CREATE TABLE #Demo (
idNo int identity(1,1),
colA int,
colB int
)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (2,4)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (4,2)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (5,1)
INSERT INTO #Demo(colA,colB) VALUES (8,1)
PRINT 'Table'
SELECT * FROM #Demo
PRINT 'Duplicates in Table'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo <> B.idNo
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Duplicates to Delete'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
DELETE FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Cleaned-up Table'
SELECT * FROM #Demo
DROP TABLE #Demo
"Herbert" <Herbert@.discussions.microsoft.com> wrote in message
news:BE1F27AD-B81D-4CF6-94B7-630BC7979FF0@.microsoft.com...
> Hi,
> Currently we are having one large table in that we are having more
than
> 1 lacs record but most of the records are duplicates(All columns are
having
> same values).
> How can i delete the particular duplicate row?
> Please give me a solution as soon as possible
> Thanks,
> Herbert
|||delete tablename
WHERE (((tablename.dupfield) In (SELECT dupfield FROM tablename As Tmp GROUP
BY dupfield HAVING Count(*)>1 )))
"Herbert" wrote:
> Hi,
> Currently we are having one large table in that we are having more than
> 1 lacs record but most of the records are duplicates(All columns are having
> same values).
> How can i delete the particular duplicate row?
> Please give me a solution as soon as possible
> Thanks,
> Herbert
|||INF: How to Remove Duplicate Rows From a Table
http://support.microsoft.com/default...44&Product=sql
AMB
"Herbert" wrote:
> Hi,
> Currently we are having one large table in that we are having more than
> 1 lacs record but most of the records are duplicates(All columns are having
> same values).
> How can i delete the particular duplicate row?
> Please give me a solution as soon as possible
> Thanks,
> Herbert
|||Hi,
Suppose if the table doesn't have any identity column and all the
remaining columns are equal and i want to have only one row and delete all
duplicate rows.
thanks.,
herbert
"Uri Dimant" wrote:
> Hi
> This script has written by Itzik Ben-Gan
> CREATE TABLE #Demo (
> idNo int identity(1,1),
> colA int,
> colB int
> )
> INSERT INTO #Demo(colA,colB) VALUES (1,6)
> INSERT INTO #Demo(colA,colB) VALUES (1,6)
> INSERT INTO #Demo(colA,colB) VALUES (2,4)
> INSERT INTO #Demo(colA,colB) VALUES (3,3)
> INSERT INTO #Demo(colA,colB) VALUES (4,2)
> INSERT INTO #Demo(colA,colB) VALUES (3,3)
> INSERT INTO #Demo(colA,colB) VALUES (5,1)
> INSERT INTO #Demo(colA,colB) VALUES (8,1)
> PRINT 'Table'
> SELECT * FROM #Demo
> PRINT 'Duplicates in Table'
> SELECT * FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo <> B.idNo
> AND A.colA = B.colA
> AND A.colB = B.colB)
> PRINT 'Duplicates to Delete'
> SELECT * FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo < B.idNo -- < this time, not <>
> AND A.colA = B.colA
> AND A.colB = B.colB)
> DELETE FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo < B.idNo -- < this time, not <>
> AND A.colA = B.colA
> AND A.colB = B.colB)
> PRINT 'Cleaned-up Table'
> SELECT * FROM #Demo
> DROP TABLE #Demo
> "Herbert" <Herbert@.discussions.microsoft.com> wrote in message
> news:BE1F27AD-B81D-4CF6-94B7-630BC7979FF0@.microsoft.com...
> than
> having
>
>
Currently we are having one large table in that we are having more than
1 lacs record but most of the records are duplicates(All columns are having
same values).
How can i delete the particular duplicate row?
Please give me a solution as soon as possible
Thanks,
Herbert
Hi
This script has written by Itzik Ben-Gan
CREATE TABLE #Demo (
idNo int identity(1,1),
colA int,
colB int
)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (2,4)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (4,2)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (5,1)
INSERT INTO #Demo(colA,colB) VALUES (8,1)
PRINT 'Table'
SELECT * FROM #Demo
PRINT 'Duplicates in Table'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo <> B.idNo
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Duplicates to Delete'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
DELETE FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Cleaned-up Table'
SELECT * FROM #Demo
DROP TABLE #Demo
"Herbert" <Herbert@.discussions.microsoft.com> wrote in message
news:BE1F27AD-B81D-4CF6-94B7-630BC7979FF0@.microsoft.com...
> Hi,
> Currently we are having one large table in that we are having more
than
> 1 lacs record but most of the records are duplicates(All columns are
having
> same values).
> How can i delete the particular duplicate row?
> Please give me a solution as soon as possible
> Thanks,
> Herbert
|||delete tablename
WHERE (((tablename.dupfield) In (SELECT dupfield FROM tablename As Tmp GROUP
BY dupfield HAVING Count(*)>1 )))
"Herbert" wrote:
> Hi,
> Currently we are having one large table in that we are having more than
> 1 lacs record but most of the records are duplicates(All columns are having
> same values).
> How can i delete the particular duplicate row?
> Please give me a solution as soon as possible
> Thanks,
> Herbert
|||INF: How to Remove Duplicate Rows From a Table
http://support.microsoft.com/default...44&Product=sql
AMB
"Herbert" wrote:
> Hi,
> Currently we are having one large table in that we are having more than
> 1 lacs record but most of the records are duplicates(All columns are having
> same values).
> How can i delete the particular duplicate row?
> Please give me a solution as soon as possible
> Thanks,
> Herbert
|||Hi,
Suppose if the table doesn't have any identity column and all the
remaining columns are equal and i want to have only one row and delete all
duplicate rows.
thanks.,
herbert
"Uri Dimant" wrote:
> Hi
> This script has written by Itzik Ben-Gan
> CREATE TABLE #Demo (
> idNo int identity(1,1),
> colA int,
> colB int
> )
> INSERT INTO #Demo(colA,colB) VALUES (1,6)
> INSERT INTO #Demo(colA,colB) VALUES (1,6)
> INSERT INTO #Demo(colA,colB) VALUES (2,4)
> INSERT INTO #Demo(colA,colB) VALUES (3,3)
> INSERT INTO #Demo(colA,colB) VALUES (4,2)
> INSERT INTO #Demo(colA,colB) VALUES (3,3)
> INSERT INTO #Demo(colA,colB) VALUES (5,1)
> INSERT INTO #Demo(colA,colB) VALUES (8,1)
> PRINT 'Table'
> SELECT * FROM #Demo
> PRINT 'Duplicates in Table'
> SELECT * FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo <> B.idNo
> AND A.colA = B.colA
> AND A.colB = B.colB)
> PRINT 'Duplicates to Delete'
> SELECT * FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo < B.idNo -- < this time, not <>
> AND A.colA = B.colA
> AND A.colB = B.colB)
> DELETE FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo < B.idNo -- < this time, not <>
> AND A.colA = B.colA
> AND A.colB = B.colB)
> PRINT 'Cleaned-up Table'
> SELECT * FROM #Demo
> DROP TABLE #Demo
> "Herbert" <Herbert@.discussions.microsoft.com> wrote in message
> news:BE1F27AD-B81D-4CF6-94B7-630BC7979FF0@.microsoft.com...
> than
> having
>
>
How to delete duplicate rows
Hi,
Currently we are having one large table in that we are having more than
1 lacs record but most of the records are duplicates(All columns are having
same values).
How can i delete the particular duplicate row?
Please give me a solution as soon as possible
Thanks,
HerbertHi
This script has written by Itzik Ben-Gan
CREATE TABLE #Demo (
idNo int identity(1,1),
colA int,
colB int
)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (2,4)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (4,2)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (5,1)
INSERT INTO #Demo(colA,colB) VALUES (8,1)
PRINT 'Table'
SELECT * FROM #Demo
PRINT 'Duplicates in Table'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo <> B.idNo
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Duplicates to Delete'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
DELETE FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Cleaned-up Table'
SELECT * FROM #Demo
DROP TABLE #Demo
"Herbert" <Herbert@.discussions.microsoft.com> wrote in message
news:BE1F27AD-B81D-4CF6-94B7-630BC7979FF0@.microsoft.com...
> Hi,
> Currently we are having one large table in that we are having more
than
> 1 lacs record but most of the records are duplicates(All columns are
having
> same values).
> How can i delete the particular duplicate row?
> Please give me a solution as soon as possible
> Thanks,
> Herbert|||delete tablename
WHERE (((tablename.dupfield) In (SELECT dupfield FROM tablename As Tmp GROUP
BY dupfield HAVING Count(*)>1 )))
"Herbert" wrote:
> Hi,
> Currently we are having one large table in that we are having more tha
n
> 1 lacs record but most of the records are duplicates(All columns are havin
g
> same values).
> How can i delete the particular duplicate row?
> Please give me a solution as soon as possible
> Thanks,
> Herbert|||INF: How to Remove Duplicate Rows From a Table
http://support.microsoft.com/defaul...444&Product=sql
AMB
"Herbert" wrote:
> Hi,
> Currently we are having one large table in that we are having more tha
n
> 1 lacs record but most of the records are duplicates(All columns are havin
g
> same values).
> How can i delete the particular duplicate row?
> Please give me a solution as soon as possible
> Thanks,
> Herbert|||Hi,
Suppose if the table doesn't have any identity column and all the
remaining columns are equal and i want to have only one row and delete all
duplicate rows.
thanks.,
herbert
"Uri Dimant" wrote:
> Hi
> This script has written by Itzik Ben-Gan
> CREATE TABLE #Demo (
> idNo int identity(1,1),
> colA int,
> colB int
> )
> INSERT INTO #Demo(colA,colB) VALUES (1,6)
> INSERT INTO #Demo(colA,colB) VALUES (1,6)
> INSERT INTO #Demo(colA,colB) VALUES (2,4)
> INSERT INTO #Demo(colA,colB) VALUES (3,3)
> INSERT INTO #Demo(colA,colB) VALUES (4,2)
> INSERT INTO #Demo(colA,colB) VALUES (3,3)
> INSERT INTO #Demo(colA,colB) VALUES (5,1)
> INSERT INTO #Demo(colA,colB) VALUES (8,1)
> PRINT 'Table'
> SELECT * FROM #Demo
> PRINT 'Duplicates in Table'
> SELECT * FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo <> B.idNo
> AND A.colA = B.colA
> AND A.colB = B.colB)
> PRINT 'Duplicates to Delete'
> SELECT * FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo < B.idNo -- < this time, not <>
> AND A.colA = B.colA
> AND A.colB = B.colB)
> DELETE FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo < B.idNo -- < this time, not <>
> AND A.colA = B.colA
> AND A.colB = B.colB)
> PRINT 'Cleaned-up Table'
> SELECT * FROM #Demo
> DROP TABLE #Demo
> "Herbert" <Herbert@.discussions.microsoft.com> wrote in message
> news:BE1F27AD-B81D-4CF6-94B7-630BC7979FF0@.microsoft.com...
> than
> having
>
>
Currently we are having one large table in that we are having more than
1 lacs record but most of the records are duplicates(All columns are having
same values).
How can i delete the particular duplicate row?
Please give me a solution as soon as possible
Thanks,
HerbertHi
This script has written by Itzik Ben-Gan
CREATE TABLE #Demo (
idNo int identity(1,1),
colA int,
colB int
)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (2,4)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (4,2)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (5,1)
INSERT INTO #Demo(colA,colB) VALUES (8,1)
PRINT 'Table'
SELECT * FROM #Demo
PRINT 'Duplicates in Table'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo <> B.idNo
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Duplicates to Delete'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
DELETE FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Cleaned-up Table'
SELECT * FROM #Demo
DROP TABLE #Demo
"Herbert" <Herbert@.discussions.microsoft.com> wrote in message
news:BE1F27AD-B81D-4CF6-94B7-630BC7979FF0@.microsoft.com...
> Hi,
> Currently we are having one large table in that we are having more
than
> 1 lacs record but most of the records are duplicates(All columns are
having
> same values).
> How can i delete the particular duplicate row?
> Please give me a solution as soon as possible
> Thanks,
> Herbert|||delete tablename
WHERE (((tablename.dupfield) In (SELECT dupfield FROM tablename As Tmp GROUP
BY dupfield HAVING Count(*)>1 )))
"Herbert" wrote:
> Hi,
> Currently we are having one large table in that we are having more tha
n
> 1 lacs record but most of the records are duplicates(All columns are havin
g
> same values).
> How can i delete the particular duplicate row?
> Please give me a solution as soon as possible
> Thanks,
> Herbert|||INF: How to Remove Duplicate Rows From a Table
http://support.microsoft.com/defaul...444&Product=sql
AMB
"Herbert" wrote:
> Hi,
> Currently we are having one large table in that we are having more tha
n
> 1 lacs record but most of the records are duplicates(All columns are havin
g
> same values).
> How can i delete the particular duplicate row?
> Please give me a solution as soon as possible
> Thanks,
> Herbert|||Hi,
Suppose if the table doesn't have any identity column and all the
remaining columns are equal and i want to have only one row and delete all
duplicate rows.
thanks.,
herbert
"Uri Dimant" wrote:
> Hi
> This script has written by Itzik Ben-Gan
> CREATE TABLE #Demo (
> idNo int identity(1,1),
> colA int,
> colB int
> )
> INSERT INTO #Demo(colA,colB) VALUES (1,6)
> INSERT INTO #Demo(colA,colB) VALUES (1,6)
> INSERT INTO #Demo(colA,colB) VALUES (2,4)
> INSERT INTO #Demo(colA,colB) VALUES (3,3)
> INSERT INTO #Demo(colA,colB) VALUES (4,2)
> INSERT INTO #Demo(colA,colB) VALUES (3,3)
> INSERT INTO #Demo(colA,colB) VALUES (5,1)
> INSERT INTO #Demo(colA,colB) VALUES (8,1)
> PRINT 'Table'
> SELECT * FROM #Demo
> PRINT 'Duplicates in Table'
> SELECT * FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo <> B.idNo
> AND A.colA = B.colA
> AND A.colB = B.colB)
> PRINT 'Duplicates to Delete'
> SELECT * FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo < B.idNo -- < this time, not <>
> AND A.colA = B.colA
> AND A.colB = B.colB)
> DELETE FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo < B.idNo -- < this time, not <>
> AND A.colA = B.colA
> AND A.colB = B.colB)
> PRINT 'Cleaned-up Table'
> SELECT * FROM #Demo
> DROP TABLE #Demo
> "Herbert" <Herbert@.discussions.microsoft.com> wrote in message
> news:BE1F27AD-B81D-4CF6-94B7-630BC7979FF0@.microsoft.com...
> than
> having
>
>
How to delete duplicate rows
Hi,
Currently we are having one large table in that we are having more than
1 lacs record but most of the records are duplicates(All columns are having
same values).
How can i delete the particular duplicate row?
Please give me a solution as soon as possible
Thanks,
HerbertHi
This script has written by Itzik Ben-Gan
CREATE TABLE #Demo (
idNo int identity(1,1),
colA int,
colB int
)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (2,4)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (4,2)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (5,1)
INSERT INTO #Demo(colA,colB) VALUES (8,1)
PRINT 'Table'
SELECT * FROM #Demo
PRINT 'Duplicates in Table'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo <> B.idNo
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Duplicates to Delete'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
DELETE FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Cleaned-up Table'
SELECT * FROM #Demo
DROP TABLE #Demo
"Herbert" <Herbert@.discussions.microsoft.com> wrote in message
news:BE1F27AD-B81D-4CF6-94B7-630BC7979FF0@.microsoft.com...
> Hi,
> Currently we are having one large table in that we are having more
than
> 1 lacs record but most of the records are duplicates(All columns are
having
> same values).
> How can i delete the particular duplicate row?
> Please give me a solution as soon as possible
> Thanks,
> Herbert|||delete tablename
WHERE (((tablename.dupfield) In (SELECT dupfield FROM tablename As Tmp GROUP
BY dupfield HAVING Count(*)>1 )))
"Herbert" wrote:
> Hi,
> Currently we are having one large table in that we are having more than
> 1 lacs record but most of the records are duplicates(All columns are having
> same values).
> How can i delete the particular duplicate row?
> Please give me a solution as soon as possible
> Thanks,
> Herbert|||INF: How to Remove Duplicate Rows From a Table
http://support.microsoft.com/default.aspx?scid=kb;en-us;139444&Product=sql
AMB
"Herbert" wrote:
> Hi,
> Currently we are having one large table in that we are having more than
> 1 lacs record but most of the records are duplicates(All columns are having
> same values).
> How can i delete the particular duplicate row?
> Please give me a solution as soon as possible
> Thanks,
> Herbert|||Hi,
Suppose if the table doesn't have any identity column and all the
remaining columns are equal and i want to have only one row and delete all
duplicate rows.
thanks.,
herbert
"Uri Dimant" wrote:
> Hi
> This script has written by Itzik Ben-Gan
> CREATE TABLE #Demo (
> idNo int identity(1,1),
> colA int,
> colB int
> )
> INSERT INTO #Demo(colA,colB) VALUES (1,6)
> INSERT INTO #Demo(colA,colB) VALUES (1,6)
> INSERT INTO #Demo(colA,colB) VALUES (2,4)
> INSERT INTO #Demo(colA,colB) VALUES (3,3)
> INSERT INTO #Demo(colA,colB) VALUES (4,2)
> INSERT INTO #Demo(colA,colB) VALUES (3,3)
> INSERT INTO #Demo(colA,colB) VALUES (5,1)
> INSERT INTO #Demo(colA,colB) VALUES (8,1)
> PRINT 'Table'
> SELECT * FROM #Demo
> PRINT 'Duplicates in Table'
> SELECT * FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo <> B.idNo
> AND A.colA = B.colA
> AND A.colB = B.colB)
> PRINT 'Duplicates to Delete'
> SELECT * FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo < B.idNo -- < this time, not <>
> AND A.colA = B.colA
> AND A.colB = B.colB)
> DELETE FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo < B.idNo -- < this time, not <>
> AND A.colA = B.colA
> AND A.colB = B.colB)
> PRINT 'Cleaned-up Table'
> SELECT * FROM #Demo
> DROP TABLE #Demo
> "Herbert" <Herbert@.discussions.microsoft.com> wrote in message
> news:BE1F27AD-B81D-4CF6-94B7-630BC7979FF0@.microsoft.com...
> > Hi,
> > Currently we are having one large table in that we are having more
> than
> > 1 lacs record but most of the records are duplicates(All columns are
> having
> > same values).
> > How can i delete the particular duplicate row?
> >
> > Please give me a solution as soon as possible
> >
> > Thanks,
> > Herbert
>
>
Currently we are having one large table in that we are having more than
1 lacs record but most of the records are duplicates(All columns are having
same values).
How can i delete the particular duplicate row?
Please give me a solution as soon as possible
Thanks,
HerbertHi
This script has written by Itzik Ben-Gan
CREATE TABLE #Demo (
idNo int identity(1,1),
colA int,
colB int
)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (2,4)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (4,2)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (5,1)
INSERT INTO #Demo(colA,colB) VALUES (8,1)
PRINT 'Table'
SELECT * FROM #Demo
PRINT 'Duplicates in Table'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo <> B.idNo
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Duplicates to Delete'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
DELETE FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Cleaned-up Table'
SELECT * FROM #Demo
DROP TABLE #Demo
"Herbert" <Herbert@.discussions.microsoft.com> wrote in message
news:BE1F27AD-B81D-4CF6-94B7-630BC7979FF0@.microsoft.com...
> Hi,
> Currently we are having one large table in that we are having more
than
> 1 lacs record but most of the records are duplicates(All columns are
having
> same values).
> How can i delete the particular duplicate row?
> Please give me a solution as soon as possible
> Thanks,
> Herbert|||delete tablename
WHERE (((tablename.dupfield) In (SELECT dupfield FROM tablename As Tmp GROUP
BY dupfield HAVING Count(*)>1 )))
"Herbert" wrote:
> Hi,
> Currently we are having one large table in that we are having more than
> 1 lacs record but most of the records are duplicates(All columns are having
> same values).
> How can i delete the particular duplicate row?
> Please give me a solution as soon as possible
> Thanks,
> Herbert|||INF: How to Remove Duplicate Rows From a Table
http://support.microsoft.com/default.aspx?scid=kb;en-us;139444&Product=sql
AMB
"Herbert" wrote:
> Hi,
> Currently we are having one large table in that we are having more than
> 1 lacs record but most of the records are duplicates(All columns are having
> same values).
> How can i delete the particular duplicate row?
> Please give me a solution as soon as possible
> Thanks,
> Herbert|||Hi,
Suppose if the table doesn't have any identity column and all the
remaining columns are equal and i want to have only one row and delete all
duplicate rows.
thanks.,
herbert
"Uri Dimant" wrote:
> Hi
> This script has written by Itzik Ben-Gan
> CREATE TABLE #Demo (
> idNo int identity(1,1),
> colA int,
> colB int
> )
> INSERT INTO #Demo(colA,colB) VALUES (1,6)
> INSERT INTO #Demo(colA,colB) VALUES (1,6)
> INSERT INTO #Demo(colA,colB) VALUES (2,4)
> INSERT INTO #Demo(colA,colB) VALUES (3,3)
> INSERT INTO #Demo(colA,colB) VALUES (4,2)
> INSERT INTO #Demo(colA,colB) VALUES (3,3)
> INSERT INTO #Demo(colA,colB) VALUES (5,1)
> INSERT INTO #Demo(colA,colB) VALUES (8,1)
> PRINT 'Table'
> SELECT * FROM #Demo
> PRINT 'Duplicates in Table'
> SELECT * FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo <> B.idNo
> AND A.colA = B.colA
> AND A.colB = B.colB)
> PRINT 'Duplicates to Delete'
> SELECT * FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo < B.idNo -- < this time, not <>
> AND A.colA = B.colA
> AND A.colB = B.colB)
> DELETE FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo < B.idNo -- < this time, not <>
> AND A.colA = B.colA
> AND A.colB = B.colB)
> PRINT 'Cleaned-up Table'
> SELECT * FROM #Demo
> DROP TABLE #Demo
> "Herbert" <Herbert@.discussions.microsoft.com> wrote in message
> news:BE1F27AD-B81D-4CF6-94B7-630BC7979FF0@.microsoft.com...
> > Hi,
> > Currently we are having one large table in that we are having more
> than
> > 1 lacs record but most of the records are duplicates(All columns are
> having
> > same values).
> > How can i delete the particular duplicate row?
> >
> > Please give me a solution as soon as possible
> >
> > Thanks,
> > Herbert
>
>
How to delete Duplicate records from table
i have table and in that i have number of records which are duplicated.
i want to delete the duplicate records from this table.
pls help me.CREATE TABLE #Demo (
idNo int identity(1,1),
colA int,
colB int
)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (2,4)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (4,2)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (5,1)
INSERT INTO #Demo(colA,colB) VALUES (8,1)
PRINT 'Table'
SELECT * FROM #Demo
PRINT 'Duplicates in Table'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo <> B.idNo
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Duplicates to Delete'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
DELETE FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Cleaned-up Table'
SELECT * FROM #Demo
DROP TABLE #Demo
"shiva" <bany.shanker@.gmail.com> wrote in message
news:1133445445.278560.128440@.g14g2000cwa.googlegroups.com...
>i have table and in that i have number of records which are duplicated.
> i want to delete the duplicate records from this table.
> pls help me.
>|||Unfrotunately, I have not found a clean way to do this. Uri's method will
work great, but technically, his table doesn't have duplicate records (as th
e
identity column prevents that).
It you have true duplicates, here's what I do:
1) Create a table containing all the table's fields plus a count field.
CREATE TABLE #xxx
(
Field1 int,
Field2 int,
NumToDelete int
)
2) Select the records which are duplicate:
INSERT INTO #xxx
SELECT *, COUNT(*) - 1
FROM dupedtable
GROUP BY Field1, Field2
HAVING COUNT(*) > 1
3) Either loop or generate a cursor and go through each record in #xxx
perfoming the following (note, if you loop, you'll need an identity column i
n
#xxx)
SET ROWCOUNT @.NumToDelete
DELETE FROM dupedtable
WHERE Field1 = @.Field1 AND Field2 = @.Field2
That's about it.
Marc
"shiva" wrote:
> i have table and in that i have number of records which are duplicated.
> i want to delete the duplicate records from this table.
> pls help me.
>|||Marc L. Allen wrote:
> Unfrotunately, I have not found a clean way to do this. Uri's method will
> work great, but technically, his table doesn't have duplicate records (as
the
> identity column prevents that).
> It you have true duplicates, here's what I do:
> 1) Create a table containing all the table's fields plus a count field.
> CREATE TABLE #xxx
> (
> Field1 int,
> Field2 int,
> NumToDelete int
> )
> 2) Select the records which are duplicate:
> INSERT INTO #xxx
> SELECT *, COUNT(*) - 1
> FROM dupedtable
> GROUP BY Field1, Field2
> HAVING COUNT(*) > 1
> 3) Either loop or generate a cursor and go through each record in #xxx
> perfoming the following (note, if you loop, you'll need an identity column
in
> #xxx)
> SET ROWCOUNT @.NumToDelete
> DELETE FROM dupedtable
> WHERE Field1 = @.Field1 AND Field2 = @.Field2
> That's about it.
> Marc
>
> "shiva" wrote:
>
delete #demo from #demo inner join #Demo d on #demo.idno < d.idno and
#demo.cola = d.cola and #demo.colb = d.colb
Regards|||Here is the process to delete the duplicate records posted some days back by
some body.
1. SELECT * INTO #Temp1 FROM [Table1]
2. TRUNCATE TABLE [Table1]
3. CREATE UNIQUE INDEX [Index1] ON [Table1] (Unique Column Names) WITH
IGNORE_DUP_KEY
4. INSERT INTO [Table1] (Column Names) SELECT (Column Names) FROM [Table1]
5. DROP INDEX [Table1].[Index1]
Note: In 4 th step,it will take the first row in list of duplicates ,rest of
them will be ignored
Thanks
Kumar
"shiva" wrote:
> i have table and in that i have number of records which are duplicated.
> i want to delete the duplicate records from this table.
> pls help me.
>
i want to delete the duplicate records from this table.
pls help me.CREATE TABLE #Demo (
idNo int identity(1,1),
colA int,
colB int
)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (2,4)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (4,2)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (5,1)
INSERT INTO #Demo(colA,colB) VALUES (8,1)
PRINT 'Table'
SELECT * FROM #Demo
PRINT 'Duplicates in Table'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo <> B.idNo
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Duplicates to Delete'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
DELETE FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Cleaned-up Table'
SELECT * FROM #Demo
DROP TABLE #Demo
"shiva" <bany.shanker@.gmail.com> wrote in message
news:1133445445.278560.128440@.g14g2000cwa.googlegroups.com...
>i have table and in that i have number of records which are duplicated.
> i want to delete the duplicate records from this table.
> pls help me.
>|||Unfrotunately, I have not found a clean way to do this. Uri's method will
work great, but technically, his table doesn't have duplicate records (as th
e
identity column prevents that).
It you have true duplicates, here's what I do:
1) Create a table containing all the table's fields plus a count field.
CREATE TABLE #xxx
(
Field1 int,
Field2 int,
NumToDelete int
)
2) Select the records which are duplicate:
INSERT INTO #xxx
SELECT *, COUNT(*) - 1
FROM dupedtable
GROUP BY Field1, Field2
HAVING COUNT(*) > 1
3) Either loop or generate a cursor and go through each record in #xxx
perfoming the following (note, if you loop, you'll need an identity column i
n
#xxx)
SET ROWCOUNT @.NumToDelete
DELETE FROM dupedtable
WHERE Field1 = @.Field1 AND Field2 = @.Field2
That's about it.
Marc
"shiva" wrote:
> i have table and in that i have number of records which are duplicated.
> i want to delete the duplicate records from this table.
> pls help me.
>|||Marc L. Allen wrote:
> Unfrotunately, I have not found a clean way to do this. Uri's method will
> work great, but technically, his table doesn't have duplicate records (as
the
> identity column prevents that).
> It you have true duplicates, here's what I do:
> 1) Create a table containing all the table's fields plus a count field.
> CREATE TABLE #xxx
> (
> Field1 int,
> Field2 int,
> NumToDelete int
> )
> 2) Select the records which are duplicate:
> INSERT INTO #xxx
> SELECT *, COUNT(*) - 1
> FROM dupedtable
> GROUP BY Field1, Field2
> HAVING COUNT(*) > 1
> 3) Either loop or generate a cursor and go through each record in #xxx
> perfoming the following (note, if you loop, you'll need an identity column
in
> #xxx)
> SET ROWCOUNT @.NumToDelete
> DELETE FROM dupedtable
> WHERE Field1 = @.Field1 AND Field2 = @.Field2
> That's about it.
> Marc
>
> "shiva" wrote:
>
delete #demo from #demo inner join #Demo d on #demo.idno < d.idno and
#demo.cola = d.cola and #demo.colb = d.colb
Regards|||Here is the process to delete the duplicate records posted some days back by
some body.
1. SELECT * INTO #Temp1 FROM [Table1]
2. TRUNCATE TABLE [Table1]
3. CREATE UNIQUE INDEX [Index1] ON [Table1] (Unique Column Names) WITH
IGNORE_DUP_KEY
4. INSERT INTO [Table1] (Column Names) SELECT (Column Names) FROM [Table1]
5. DROP INDEX [Table1].[Index1]
Note: In 4 th step,it will take the first row in list of duplicates ,rest of
them will be ignored
Thanks
Kumar
"shiva" wrote:
> i have table and in that i have number of records which are duplicated.
> i want to delete the duplicate records from this table.
> pls help me.
>
How to delete duplicate records from a table ?
I uploaded some data about 2 or 3 times and it keep appending it to the
table.
table.
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 1
record.
ThanksRefer to following url
http://support.microsoft.com/defaul...B;en-us;q139444
--
- Vishal
How to delete duplicate record
I have table by mistake i have lot of duplicate records. How do i delete it
.?
Thanks
Jayhttp://www.aspfaq.com/2431
Then
http://www.aspfaq.com/2509
"Jay Villa" <jayvilla@.community.nospam> wrote in message
news:OFz5HZonFHA.2080@.TK2MSFTNGP14.phx.gbl...
>I have table by mistake i have lot of duplicate records. How do i delete it
>.?
> Thanks
> Jay
>|||Jay,
Could you post the structure of your table. It would be helpful to know the
column names and primary key columns involved.
Thanks,
Frank Castora
"Jay Villa" <jayvilla@.community.nospam> wrote in message
news:OFz5HZonFHA.2080@.TK2MSFTNGP14.phx.gbl...
>I have table by mistake i have lot of duplicate records. How do i delete it
>.?
> Thanks
> Jay
>|||Frank
Table looks like this
cbbdacc_account_id --> PK
cbbdacc_desc
cbbdacc_resp_pidm
cbbdacc_balance
-Jay
"Frank Castora" <fccsql@.hotmail.com> wrote in message
news:%23BFoubonFHA.860@.TK2MSFTNGP12.phx.gbl...
> Jay,
> Could you post the structure of your table. It would be helpful to know
> the column names and primary key columns involved.
> Thanks,
> Frank Castora
> "Jay Villa" <jayvilla@.community.nospam> wrote in message
> news:OFz5HZonFHA.2080@.TK2MSFTNGP14.phx.gbl...
>|||> cbbdacc_account_id --> PK
Is this an IDENTITY column? Do you need to maintain existing values? If
so, how do you decide which ID # you need to keep?
> cbbdacc_desc
> cbbdacc_resp_pidm
> cbbdacc_balance
Is a row considered a "duplicate" when all three of these columns are
identical in the two rows, or some subset?|||I respectfully defer to Aaron, as the articles he pointed you too are quite
sufficient. :)
Thanks,
Frank Castora
"Jay Villa" <jayvilla@.community.nospam> wrote in message
news:%23AN4KionFHA.4056@.TK2MSFTNGP10.phx.gbl...
> Frank
> Table looks like this
> cbbdacc_account_id --> PK
> cbbdacc_desc
> cbbdacc_resp_pidm
> cbbdacc_balance
>
> -Jay
>
> "Frank Castora" <fccsql@.hotmail.com> wrote in message
> news:%23BFoubonFHA.860@.TK2MSFTNGP12.phx.gbl...
>|||Hi Aaron,
You may want to add:
WITH JustDups AS
(
SELECT * FROM T1 AS A
WHERE surkey <
(SELECT MAX(surkey) FROM T1 AS B
WHERE B.wannabekey = A.wannabekey)
)
DELETE FROM JustDups;
:)
--
BG, SQL Server MVP
www.SolidQualityLearning.com
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23zpiXaonFHA.1968@.TK2MSFTNGP14.phx.gbl...
> http://www.aspfaq.com/2431
> Then
> http://www.aspfaq.com/2509
>
>
> "Jay Villa" <jayvilla@.community.nospam> wrote in message
> news:OFz5HZonFHA.2080@.TK2MSFTNGP14.phx.gbl...
>
.?
Thanks
Jayhttp://www.aspfaq.com/2431
Then
http://www.aspfaq.com/2509
"Jay Villa" <jayvilla@.community.nospam> wrote in message
news:OFz5HZonFHA.2080@.TK2MSFTNGP14.phx.gbl...
>I have table by mistake i have lot of duplicate records. How do i delete it
>.?
> Thanks
> Jay
>|||Jay,
Could you post the structure of your table. It would be helpful to know the
column names and primary key columns involved.
Thanks,
Frank Castora
"Jay Villa" <jayvilla@.community.nospam> wrote in message
news:OFz5HZonFHA.2080@.TK2MSFTNGP14.phx.gbl...
>I have table by mistake i have lot of duplicate records. How do i delete it
>.?
> Thanks
> Jay
>|||Frank
Table looks like this
cbbdacc_account_id --> PK
cbbdacc_desc
cbbdacc_resp_pidm
cbbdacc_balance
-Jay
"Frank Castora" <fccsql@.hotmail.com> wrote in message
news:%23BFoubonFHA.860@.TK2MSFTNGP12.phx.gbl...
> Jay,
> Could you post the structure of your table. It would be helpful to know
> the column names and primary key columns involved.
> Thanks,
> Frank Castora
> "Jay Villa" <jayvilla@.community.nospam> wrote in message
> news:OFz5HZonFHA.2080@.TK2MSFTNGP14.phx.gbl...
>|||> cbbdacc_account_id --> PK
Is this an IDENTITY column? Do you need to maintain existing values? If
so, how do you decide which ID # you need to keep?
> cbbdacc_desc
> cbbdacc_resp_pidm
> cbbdacc_balance
Is a row considered a "duplicate" when all three of these columns are
identical in the two rows, or some subset?|||I respectfully defer to Aaron, as the articles he pointed you too are quite
sufficient. :)
Thanks,
Frank Castora
"Jay Villa" <jayvilla@.community.nospam> wrote in message
news:%23AN4KionFHA.4056@.TK2MSFTNGP10.phx.gbl...
> Frank
> Table looks like this
> cbbdacc_account_id --> PK
> cbbdacc_desc
> cbbdacc_resp_pidm
> cbbdacc_balance
>
> -Jay
>
> "Frank Castora" <fccsql@.hotmail.com> wrote in message
> news:%23BFoubonFHA.860@.TK2MSFTNGP12.phx.gbl...
>|||Hi Aaron,
You may want to add:
WITH JustDups AS
(
SELECT * FROM T1 AS A
WHERE surkey <
(SELECT MAX(surkey) FROM T1 AS B
WHERE B.wannabekey = A.wannabekey)
)
DELETE FROM JustDups;
:)
--
BG, SQL Server MVP
www.SolidQualityLearning.com
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23zpiXaonFHA.1968@.TK2MSFTNGP14.phx.gbl...
> http://www.aspfaq.com/2431
> Then
> http://www.aspfaq.com/2509
>
>
> "Jay Villa" <jayvilla@.community.nospam> wrote in message
> news:OFz5HZonFHA.2080@.TK2MSFTNGP14.phx.gbl...
>
How to delete all rowa in all tables of a schema in Oracle?
Hi all,
I want to delete all records of all tables of a schema and think there should be some statement for this but I dont know how?
may you help?As this question is Oracle specific, I'd suggest that you post it in the Oracle (http://www.dbforums.com/f4) forum. One of the Oracle folks can probably answer your question definitively without even needing to look it up!
-PatP|||There is no simple statement available to delete only the tables.
U can instead use
DROP USER <username> CASCADE.
But caution....this will delete everything belonging to the user tables, views, sequences..etc.
If u want to delete only the table of a schema
then u can write a PL/SQL which will query for all the table from user_objects and then execute statements to delete the data from the tables|||PL/SQL procedure would do the work indeed.
Perhaps another suggestion - write a query and spool its output to an .sql file and then run it. Such as:
> set heading off;
> set feedback off;
>
> spool truncall.sql
>
> select 'truncate table ' || tname ||';' from tab where tabtype = 'TABLE';
>
> spool off;
>
> @.truncall
Why 'truncate' and not 'delete'? Delete saves all the deleted records in rollback segment(s) which slows things down.
However, you might need to run this script several times due to referential integrity constraints which might prevent some tables to be truncated (you can't delete parent while child exists).|||Thanx all for help,
but I want to delete all the records from all my tables not truncating all tables.Any idea?|||What difference do you see between deleting all of the rows and truncating the table?
-PatP
I want to delete all records of all tables of a schema and think there should be some statement for this but I dont know how?
may you help?As this question is Oracle specific, I'd suggest that you post it in the Oracle (http://www.dbforums.com/f4) forum. One of the Oracle folks can probably answer your question definitively without even needing to look it up!
-PatP|||There is no simple statement available to delete only the tables.
U can instead use
DROP USER <username> CASCADE.
But caution....this will delete everything belonging to the user tables, views, sequences..etc.
If u want to delete only the table of a schema
then u can write a PL/SQL which will query for all the table from user_objects and then execute statements to delete the data from the tables|||PL/SQL procedure would do the work indeed.
Perhaps another suggestion - write a query and spool its output to an .sql file and then run it. Such as:
> set heading off;
> set feedback off;
>
> spool truncall.sql
>
> select 'truncate table ' || tname ||';' from tab where tabtype = 'TABLE';
>
> spool off;
>
> @.truncall
Why 'truncate' and not 'delete'? Delete saves all the deleted records in rollback segment(s) which slows things down.
However, you might need to run this script several times due to referential integrity constraints which might prevent some tables to be truncated (you can't delete parent while child exists).|||Thanx all for help,
but I want to delete all the records from all my tables not truncating all tables.Any idea?|||What difference do you see between deleting all of the rows and truncating the table?
-PatP
Subscribe to:
Posts (Atom)