Deleting rows

Hi there,
can you help me?
I'm trying to delete rows in a single table.
When i exec select , rows returne are 6098 (right number)
SELECT distinct FLD_USERID FROM
(
SELECT distinct FLD_USERID, MIN(FLD_LEVEL)as a
FROM TBL_CHARACTERbackup
GROUP BY FLD_USERID
HAVING (COUNT(*) > 2)
) f
When i try to delete that records rows deleted are 13708 (wrong)
DELETE TBL_CHARACTERbackup WHERE FLD_USERID IN
(
SELECT distinct FLD_USERID FROM
(
SELECT distinct FLD_USERID, MIN(FLD_LEVEL)as a
FROM TBL_CHARACTERbackup
GROUP BY FLD_USERID
HAVING (COUNT(*) > 2)
) f
)
Donno why, please help
TksThe DELETE statement will delete every row in your table which has a
FLD_USERID value that is duplicated, whereas the SELECT will only show
ONE row per FLD_USERID.
Possibly this is what you intended:
DELETE FROM TBL_CHARACTERbackup
WHERE EXISTS
(SELECT *
FROM TBL_CHARACTERbackup AS T
WHERE T.fld_userid = TBL_CHARACTERbackup.fld_userid
AND T.fld_level < TBL_CHARACTERbackup.fld_level)
but that's only a guess.
David Portas
SQL Server MVP
--|||
"David Portas" ha scritto:

> The DELETE statement will delete every row in your table which has a
> FLD_USERID value that is duplicated, whereas the SELECT will only show
> ONE row per FLD_USERID.
> Possibly this is what you intended:
> DELETE FROM TBL_CHARACTERbackup
> WHERE EXISTS
> (SELECT *
> FROM TBL_CHARACTERbackup AS T
> WHERE T.fld_userid = TBL_CHARACTERbackup.fld_userid
> AND T.fld_level < TBL_CHARACTERbackup.fld_level)
> but that's only a guess.
> --
> David Portas
> SQL Server MVP
Ok what i need is to delete all fld_userid with lower fld_level
just to have
only one records for each fld_userid with highest fld_level
How can i do?
Please help.
> --
>|||You haven't told us what the key of your table is. Assuming
(fld_userid, fld_level) is unique then just turn around the sign in my
first attempt:
DELETE FROM TBL_CHARACTERbackup
WHERE EXISTS
(SELECT *
FROM TBL_CHARACTERbackup AS T
WHERE T.fld_userid = TBL_CHARACTERbackup.fld_userid
AND T.fld_level > TBL_CHARACTERbackup.fld_level)
(still untested)
If (fld_userid, fld_level) isn't unique that means there may be more
than one row with the same maximum value of fld_level so you will have
to add some other criteria to the WHERE clause. If you need more help
then the following article explains the best way to describe the
details of your problem for the group:
http://www.aspfaq.com/etiquette.asp?id=5006
Do you have any say over naming conventions in your design? Why the
useless tbl_ and fld_ prefixes? They tell us nothing (we know what
tables and columns are) and just make the names harder to read. Also,
the usual convention is to use the term "column" not "field" when
referring to relational data. Some people find this distinction more
important than others and associate the two terms with totally
different concepts but almost everyone loathes to see prefixes on
column names.
Hope this helps.
David Portas
SQL Server MVP
--|||
"David Portas" wrote:

> You haven't told us what the key of your table is. Assuming
> (fld_userid, fld_level) is unique then just turn around the sign in my
> first attempt:
> DELETE FROM TBL_CHARACTERbackup
> WHERE EXISTS
> (SELECT *
> FROM TBL_CHARACTERbackup AS T
> WHERE T.fld_userid = TBL_CHARACTERbackup.fld_userid
> AND T.fld_level > TBL_CHARACTERbackup.fld_level)
> (still untested)
> If (fld_userid, fld_level) isn't unique that means there may be more
> than one row with the same maximum value of fld_level so you will have
> to add some other criteria to the WHERE clause. If you need more help
> then the following article explains the best way to describe the
> details of your problem for the group:
> http://www.aspfaq.com/etiquette.asp?id=5006
> Do you have any say over naming conventions in your design? Why the
> useless tbl_ and fld_ prefixes? They tell us nothing (we know what
> tables and columns are) and just make the names harder to read. Also,
> the usual convention is to use the term "column" not "field" when
> referring to relational data. Some people find this distinction more
> important than others and associate the two terms with totally
> different concepts but almost everyone loathes to see prefixes on
> column names.
> Hope this helps.
> --
> David Portas
> SQL Server MVP
I'll explain :
each row_USERID corrisponds more records
ex
FLD_USERID FLD_LEVEL CHARACTERS
daniele 1 PIPPO
daniele 27 PLUTO
daniele 37 Paperino
I must keep in table
FLD_USERID FLD_LEVEL CHARACTERS
daniele 27 PLUTO
daniele 37 Paperino
That means only 2 records for FLD_USERID wiyh 2 higher FLD_LEVEL levels
Please help
> --
>|||Try:
DELETE FROM TBL_CHARACTERbackup
WHERE fld_level =
(SELECT MIN(fld_level)
FROM TBL_CHARACTERbackup AS T
WHERE fld_userid = TBL_CHARACTERbackup.fld_userid)
David Portas
SQL Server MVP
--

Deleting duplicate rows within a single table

I was wondering if anyone had a suggestion as to how to delete duplicate rows from a table. I have been doing this:

SELECT * INTO TempUsersNoRepeats
FROM TempUsers2
UNION
SELECT * FROM TempUsers3

This way I end up with a total of four tables (the fourth table being the original Users table) and I was hoping that there was a way that I could do this all within the the original Users table and not have to create the three TempUsers tables.

Thanks,
RonDo you have a primary key on the table?|||Douglas,

Thanks for the reply. These tables are staging tables and not part of my asp.net application as they are made from .csv files that I get from FOCUS jobs from a mainframe. I cannot add a PK as there are so many duplicate rows. The table cols are:

FacultyID,FacultyPW,FacultyFName,FacultyLName,FacultyEmailID

I could add a Identity col to get some unique values assoc. with each row though.

Thanks,
Ronald|||If you add an IDENTITY field (lets say, named ID):

DELETE FROM Faculty WHERE ID IN (
SELECT MAX(ID) FROM Faculty
GROUP BY
FacultyID,FacultyPW,FacultyFName,FacultyLName,FacultyEmailID
HAVING COUNT(*)>1
)

This will delete one row of the duplicates that are complete dups except the new ID field. Please try this on a test database first! This is untested SQL, but I believe it will work.|||Douglas,

Thanks that was pretty cool. If you could possibly answer one more question, some of the rows are duplicated multiple times, there could be one Faculty row 7 times (they are teaching 7 courses that semester) or some are just teaching 5 times thus only showing 5 dupes.

What would the proper syntax be for the HAVING COUNT(*)>1 so that I either catch a range (say 1-20) or for it to loop maybe.

I kept running the above script and was able to clear out all the duplicates after several runs.

Thanks very much,
Ronald|||This will delete one duplicate for each exact duplicate. If you have 20 identical duplicates, you would need to use the query 20 times (run it until 0 rows are effected).

Alternately, you could play and make it a query that deletes those dupes that are NOT MAX(ID).

Deleting duplicate rows from a table, having no primary key

I am looking at various methods to delete records from a table which has duplicate records and does not have a foriegn key, in a single query.
One of the methods is using the rowid.
Looking for more.
VisheetalThat would be the easiest.sql

DELETE without transaction?

Hi,
I have a database with about 50 GB - most of the data in one single table
with only three fields. I need to clean up this table frequently (executing
DELETE stantements). My problem is that this locks the table so that BULK
INSERT jobs cannot execute. Also the transaction lok fills up a lot (15 GB
and more) while deleting the data.
Is there a way to delete without using the transaction log and while doing a
bulk insert?
CU,
SvenHi,
I think TRUNCATE TABLE is what you are looking for.
I'm not sure you can do anything else while truncating, but
truncate is very fast.
Check BOL for further information.
Ciao,
Cosmin.
"Sven Erik Matzen" <sven.matzen@.dontspamme.com> wrote in message
news:editI8xYDHA.1004@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I have a database with about 50 GB - most of the data in one single table
> with only three fields. I need to clean up this table frequently
(executing
> DELETE stantements). My problem is that this locks the table so that BULK
> INSERT jobs cannot execute. Also the transaction lok fills up a lot (15 GB
> and more) while deleting the data.
> Is there a way to delete without using the transaction log and while doing
a
> bulk insert?
> CU,
> Sven
>|||truncate table is a good way of cleaning out the entire
table, assuming there are no foreign key constraints
for deleting large number of rows, try doing it in small
batches, say 1000-10,000 rows at a time, adjust the row
count to keep the batch run time sufficiently short
>--Original Message--
>Hi,
>I have a database with about 50 GB - most of the data in
one single table
>with only three fields. I need to clean up this table
frequently (executing
>DELETE stantements). My problem is that this locks the
table so that BULK
>INSERT jobs cannot execute. Also the transaction lok
fills up a lot (15 GB
>and more) while deleting the data.
>Is there a way to delete without using the transaction
log and while doing a
>bulk insert?
>CU,
>Sven
>
>.
>|||The problem is that I need a where statement - this is not supported by
TRUNCATE TABLE (that's one reason, why it's so fast).
"Cosmin" <cosmin.onea@.infoworld.ro> wrote in message
news:O1NbTJyYDHA.2476@.tk2msftngp13.phx.gbl...
> Hi,
> I think TRUNCATE TABLE is what you are looking for.
> I'm not sure you can do anything else while truncating, but
> truncate is very fast.
> Check BOL for further information.
> Ciao,
> Cosmin.
> "Sven Erik Matzen" <sven.matzen@.dontspamme.com> wrote in message
> news:editI8xYDHA.1004@.TK2MSFTNGP12.phx.gbl...
> > Hi,
> >
> > I have a database with about 50 GB - most of the data in one single
table
> > with only three fields. I need to clean up this table frequently
> (executing
> > DELETE stantements). My problem is that this locks the table so that
BULK
> > INSERT jobs cannot execute. Also the transaction lok fills up a lot (15
GB
> > and more) while deleting the data.
> >
> > Is there a way to delete without using the transaction log and while
doing
> a
> > bulk insert?
> >
> > CU,
> > Sven
> >
> >
>

Delete Records from Two Tables

Hi,

Is it possible to delete records from two tables with single DELETE statement.

Thanks,

Regards,
Nakkeeran Rengasamy

You can't do it directly.

You have to use the INSTEAD OF TRIGGER (works on Both 2000 & 2005) or OUTPUT clause (if you use SQL Server 2005).

Using Output Clause:

Code Snippet

Create Table #A (

[AId] int ,

[Name] Varchar(100)

);

Insert Into #A Values('1','One');

Insert Into #A Values('2','Two');

Insert Into #A Values('3','Three');

Create Table #B (

[BId] int ,

[AId] int ,

[Desc] Varchar(100)

);

Insert Into #B Values('1','1','SomeText 1.1');

Insert Into #B Values('2','1','SomeText 1.2');

Insert Into #B Values('3','2','SomeText 2.1');

Insert Into #B Values('4','3','SomeText 3.1');

DECLARE @.MyTableVar table (ID int)

Delete #A Output Deleted.AID into @.MyTableVar Where AID=1

Delete From #B Where AId in (Select ID from @.MyTableVar)

Delete on a single filegroup

I am running SQL 2005 SP2.
I have a partitioned table and was wondering if there is a view that could
tell me the data that resides on a specific filegroup. What I would like to
do is to delete only the data that resides on a specific filegroup. Thx.
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200802/1Perhaps the $PARTITION() function will help you?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message news:801e77dbe7979@.uwe...
>I am running SQL 2005 SP2.
> I have a partitioned table and was wondering if there is a view that could
> tell me the data that resides on a specific filegroup. What I would like to
> do is to delete only the data that resides on a specific filegroup. Thx.
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200802/1
>|||> I have a partitioned table and was wondering if there is a view that could
> tell me the data that resides on a specific filegroup. What I would like
> to
> do is to delete only the data that resides on a specific filegroup. Thx.
The query below will list partitions by filegroup. You can then delete the
data using $PARTITION function like Tibor mentioned or specify partitioning
column values that map to the partitions on the filegroup. If you have a
lot of data, you might consider switching partitions into a staging table
and then truncating the staging table.
SELECT
s.name AS SchemaName,
o.name AS TableName,
ds.name AS PartitionScheme,
p.partition_number AS PartitionNumber,
fg.name AS FileGroupName,
prv_left.value AS LowerBoundaryValue,
prv_right.value AS UpperBoundaryValue,
CASE pf.boundary_value_on_right WHEN 1 THEN 'RIGHT' ELSE 'LEFT' END AS
Boundary,
p.rows AS Rows
FROM sys.schemas AS s
JOIN sys.objects AS o ON
o.schema_id = s.schema_id
AND o.type = 'U'
JOIN sys.indexes AS i ON
i.object_id = o.object_id AND
i.index_id IN(0,1)
JOIN sys.partitions p ON
p.object_id = i.object_id AND
p.index_id = i.index_id
JOIN sys.data_spaces AS ds ON
ds.data_space_id = i.data_space_id
JOIN sys.partition_schemes AS ps ON
ps.data_space_id = ds.data_space_id
JOIN sys.partition_functions AS pf ON
pf.function_id = ps.function_id
JOIN sys.destination_data_spaces AS dds2 ON
dds2.partition_scheme_id = ps.data_space_id
AND dds2.destination_id = p.partition_number
JOIN sys.filegroups AS fg ON
fg.data_space_id = dds2.data_space_id
LEFT JOIN sys.partition_range_values AS prv_left ON
ps.function_id = prv_left.function_id
AND prv_left.boundary_id = p.partition_number - 1
LEFT JOIN sys.partition_range_values AS prv_right ON
ps.function_id = prv_right.function_id
AND prv_right.boundary_id = p.partition_number
ORDER BY
SchemaName,
TableName,
FileGroupName,
PartitionNumber;
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:801e77dbe7979@.uwe...
>I am running SQL 2005 SP2.
> I have a partitioned table and was wondering if there is a view that could
> tell me the data that resides on a specific filegroup. What I would like
> to
> do is to delete only the data that resides on a specific filegroup. Thx.
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200802/1
>

Delete on a single filegroup

I am running SQL 2005 SP2.
I have a partitioned table and was wondering if there is a view that could
tell me the data that resides on a specific filegroup. What I would like to
do is to delete only the data that resides on a specific filegroup. Thx.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200802/1
> I have a partitioned table and was wondering if there is a view that could
> tell me the data that resides on a specific filegroup. What I would like
> to
> do is to delete only the data that resides on a specific filegroup. Thx.
The query below will list partitions by filegroup. You can then delete the
data using $PARTITION function like Tibor mentioned or specify partitioning
column values that map to the partitions on the filegroup. If you have a
lot of data, you might consider switching partitions into a staging table
and then truncating the staging table.
SELECT
s.name AS SchemaName,
o.name AS TableName,
ds.name AS PartitionScheme,
p.partition_number AS PartitionNumber,
fg.name AS FileGroupName,
prv_left.value AS LowerBoundaryValue,
prv_right.value AS UpperBoundaryValue,
CASE pf.boundary_value_on_right WHEN 1 THEN 'RIGHT' ELSE 'LEFT' END AS
Boundary,
p.rows AS Rows
FROM sys.schemas AS s
JOIN sys.objects AS o ON
o.schema_id = s.schema_id
AND o.type = 'U'
JOIN sys.indexes AS i ON
i.object_id = o.object_id AND
i.index_id IN(0,1)
JOIN sys.partitions p ON
p.object_id = i.object_id AND
p.index_id = i.index_id
JOIN sys.data_spaces AS ds ON
ds.data_space_id = i.data_space_id
JOIN sys.partition_schemes AS ps ON
ps.data_space_id = ds.data_space_id
JOIN sys.partition_functions AS pf ON
pf.function_id = ps.function_id
JOIN sys.destination_data_spaces AS dds2 ON
dds2.partition_scheme_id = ps.data_space_id
AND dds2.destination_id = p.partition_number
JOIN sys.filegroups AS fg ON
fg.data_space_id = dds2.data_space_id
LEFT JOIN sys.partition_range_values AS prv_left ON
ps.function_id = prv_left.function_id
AND prv_left.boundary_id = p.partition_number - 1
LEFT JOIN sys.partition_range_values AS prv_right ON
ps.function_id = prv_right.function_id
AND prv_right.boundary_id = p.partition_number
ORDER BY
SchemaName,
TableName,
FileGroupName,
PartitionNumber;
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"cbrichards via droptable.com" <u3288@.uwe> wrote in message
news:801e77dbe7979@.uwe...
>I am running SQL 2005 SP2.
> I have a partitioned table and was wondering if there is a view that could
> tell me the data that resides on a specific filegroup. What I would like
> to
> do is to delete only the data that resides on a specific filegroup. Thx.
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums.aspx/sql-server/200802/1
>