DENY permission,SQL server: table

Showing posts with label table. Show all posts

Thursday, March 29, 2012

deleting table phisically

hello
I delete a table from a DataBase in enterprise manager(drop table) ,
but it is not deleted phisically and hard's free space does not
increas.
How can I delete table phisically?
thanksYou need to shrink the database to recover disk space on the drive...
"nsh" <nsh5776@.yahoo.com> wrote in message
news:534bf457.0307091048.714766fe@.posting.google.com...
> hello
> I delete a table from a DataBase in enterprise manager(drop table) ,
> but it is not deleted phisically and hard's free space does not
> increas.
> How can I delete table phisically?
> thanks|||How do you know the table is not deleted phisically? because of the database
file (.mdf) file?
Most likely you just vacated space within the .mdf file.
Back up the database, then try to shrink it.
using Enterprise manager, Right mouse on the database -> All Tasks ->
Shrink dbase
You can also see how much space is being used in the .mdf file grafically
using Enterprise manager, Right mouse on the database -> View -> Task
Pad
Hope this helps
"nsh" <nsh5776@.yahoo.com> escribió en el mensaje
news:534bf457.0307091048.714766fe@.posting.google.com...
> hello
> I delete a table from a DataBase in enterprise manager(drop table) ,
> but it is not deleted phisically and hard's free space does not
> increas.
> How can I delete table phisically?
> thanks|||thanks for helping.
Before delete table .mdf & .ldf size are the same after delete table
(each table size is about 5GB ).
when I make Data Base ,I set Autoshrink .Is not it enough?
please help me ...
thanks
"Marcelo" <marcelo.no@.spam.santiago.cl> wrote in message news:<#YAUSvkRDHA.1324@.TK2MSFTNGP11.phx.gbl>...
> How do you know the table is not deleted phisically? because of the database
> file (.mdf) file?
> Most likely you just vacated space within the .mdf file.
> Back up the database, then try to shrink it.
> using Enterprise manager, Right mouse on the database -> All Tasks ->
> Shrink dbase
> You can also see how much space is being used in the .mdf file grafically
> using Enterprise manager, Right mouse on the database -> View -> Task
> Pad
> Hope this helps
> "nsh" <nsh5776@.yahoo.com> escribió en el mensaje
> news:534bf457.0307091048.714766fe@.posting.google.com...
> > hello
> > I delete a table from a DataBase in enterprise manager(drop table) ,
> > but it is not deleted phisically and hard's free space does not
> > increas.
> > How can I delete table phisically?
> > thanks|||> when I make Data Base ,I set Autoshrink .Is not it enough?
No, because it doesn't auto-shrink every single time the database changes --
this would cause a performance nightmare.

Deleting Table

I'm having a problem when i try to delete a table...I have run
dbcc newalloc
DBCC checkdb
on the database and the newalloc came back without errors...the
checkdb just hangs without returning anything...this is a developement
database with the live version off site, so whatever i need to do to
get things going is ok - but i'd like to just delete the table and
bring a new one down...
the problem is i can't do anything with this table...i can open it,
but i can't delete a row from it...i can't delete the whole table (it
just hangs if i try) and i can't copy a new table over the top or
rename it (again, hang)
Is there any way to just delete this specific table or figure out what
is wrong so i can fix it?
thanks in advance...
jake
Hi,
Might be locking. Can you kill all the users connected to this database and
then set the database to single user
sp_dboption 'dbname','single user',true
After that try executing the below command to delete the contents of the
table
truncate table <table_name>
This will fail if that table got any relations (Foreign key), in that case
use delete statement.
Thanks
Hari
MCDBA
"jake stahh" <junk@.anv.net> wrote in message
news:n2s2i0hogemkd32pp71tsm66mkofiqj0l8@.4ax.com...
> I'm having a problem when i try to delete a table...I have run
> dbcc newalloc
> DBCC checkdb
> on the database and the newalloc came back without errors...the
> checkdb just hangs without returning anything...this is a developement
> database with the live version off site, so whatever i need to do to
> get things going is ok - but i'd like to just delete the table and
> bring a new one down...
> the problem is i can't do anything with this table...i can open it,
> but i can't delete a row from it...i can't delete the whole table (it
> just hangs if i try) and i can't copy a new table over the top or
> rename it (again, hang)
> Is there any way to just delete this specific table or figure out what
> is wrong so i can fix it?
> thanks in advance...
> jake
|||you are a genius! thank you so much - it worked perfectly! the check
is in the mail...
jake stahh
On Tue, 17 Aug 2004 09:28:41 +0530, "Hari Prasad"
<hari_prasad_k@.hotmail.com> wrote:

>Hi,
>Might be locking. Can you kill all the users connected to this database and
>then set the database to single user
>sp_dboption 'dbname','single user',true
>After that try executing the below command to delete the contents of the
>table
>truncate table <table_name>
>This will fail if that table got any relations (Foreign key), in that case
>use delete statement.
>Thanks
>Hari
>MCDBA
>
>"jake stahh" <junk@.anv.net> wrote in message
>news:n2s2i0hogemkd32pp71tsm66mkofiqj0l8@.4ax.com.. .
>
sql

Deleting Table

I'm having a problem when i try to delete a table...I have run
dbcc newalloc
DBCC checkdb
on the database and the newalloc came back without errors...the
checkdb just hangs without returning anything...this is a developement
database with the live version off site, so whatever i need to do to
get things going is ok - but i'd like to just delete the table and
bring a new one down...
the problem is i can't do anything with this table...i can open it,
but i can't delete a row from it...i can't delete the whole table (it
just hangs if i try) and i can't copy a new table over the top or
rename it (again, hang)
Is there any way to just delete this specific table or figure out what
is wrong so i can fix it?
thanks in advance...
jakeHi,
Might be locking. Can you kill all the users connected to this database and
then set the database to single user
sp_dboption 'dbname','single user',true
After that try executing the below command to delete the contents of the
table
truncate table <table_name>
This will fail if that table got any relations (Foreign key), in that case
use delete statement.
Thanks
Hari
MCDBA
"jake stahh" <junk@.anv.net> wrote in message
news:n2s2i0hogemkd32pp71tsm66mkofiqj0l8@.
4ax.com...
> I'm having a problem when i try to delete a table...I have run
> dbcc newalloc
> DBCC checkdb
> on the database and the newalloc came back without errors...the
> checkdb just hangs without returning anything...this is a developement
> database with the live version off site, so whatever i need to do to
> get things going is ok - but i'd like to just delete the table and
> bring a new one down...
> the problem is i can't do anything with this table...i can open it,
> but i can't delete a row from it...i can't delete the whole table (it
> just hangs if i try) and i can't copy a new table over the top or
> rename it (again, hang)
> Is there any way to just delete this specific table or figure out what
> is wrong so i can fix it?
> thanks in advance...
> jake|||you are a genius! thank you so much - it worked perfectly! the check
is in the mail...
jake stahh
On Tue, 17 Aug 2004 09:28:41 +0530, "Hari Prasad"
<hari_prasad_k@.hotmail.com> wrote:

>Hi,
>Might be locking. Can you kill all the users connected to this database and
>then set the database to single user
>sp_dboption 'dbname','single user',true
>After that try executing the below command to delete the contents of the
>table
>truncate table <table_name>
>This will fail if that table got any relations (Foreign key), in that case
>use delete statement.
>Thanks
>Hari
>MCDBA
>
>"jake stahh" <junk@.anv.net> wrote in message
> news:n2s2i0hogemkd32pp71tsm66mkofiqj0l8@.
4ax.com...
>

Deleting Table

Deleting semi duplicates

Suppose that I have a table that contains a lot of records that are
identical except for an id field and a date-time-stamp field. For
example

Id Unit Price DTS
1 A 1.00 Date 1
2 A 1.00 Date 2
3 A 1.00 Date 3
4 B 1.25 Date 4
5 B 1.50 Date 5
6 B 1.50 Date 6
7 C 2.75 Date 7
8 C 2.75 Date 8
9 C 2.75 Date 9
10 C 3.00 Date 10

I want to cull out records that are duplicates in the units and price
fields. I want to use the max DTS as the criteria for which record in
a set of "duplicates" will remain. So, If I get the right query, I
should return with

Id Unit Price DTS
1 A 1.00 Date 1
4 B 1.25 Date 4
5 B 1.50 Date 5
7 C 2.75 Date 7
10 C 3.00 Date 10

Is this possible using a single query? If so, how? I am sure that I
can do this using code, but it will involve a bunch of loops and
process time. I would prefer a cleaner, more elegant way. Thanks for
any help.

JerryAssuming the combination of (unit,price,dts) is unique and non-NULL:

DELETE FROM Sometable
WHERE EXISTS
(SELECT *
FROM Sometable AS S
WHERE unit = Sometable.unit
AND price = Sometable.price
AND dts > Sometable.dts)

--
David Portas
----
Please reply only to the newsgroup
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message news:<zemdnRnYsNSQopHdRVn-vw@.giganews.com>...
> Assuming the combination of (unit,price,dts) is unique and non-NULL:
> DELETE FROM Sometable
> WHERE EXISTS
> (SELECT *
> FROM Sometable AS S
> WHERE unit = Sometable.unit
> AND price = Sometable.price
> AND dts > Sometable.dts)

Thanks. I'll give this a try.

J|||Remember that names are resoved to the nearer containing table
reference! You meant:

DELETE FROM Sometable
WHERE EXISTS
(SELECT *
FROM Sometable AS S
WHERE S.unit = Sometable.unit
AND S.price = Sometable.price
AND S.dts > Sometable.dts);|||> Remember that names are resoved to the nearer containing table
> reference!

Precisely. That's why the S isn't needed here - the alias ensures that
"Sometable" refers to the outer reference and the other columns to the inner
reference. Your statement is equivalent to mine.

--
David Portas
----
Please reply only to the newsgroup
--sql

DELETING ROWS with REFERENTIAL INTEGRITY

hi there!

im having problems deleting rows in a reference table. is there any tools which tables to delete first before deleting the rows in the table which contains the primary key?

i have a lot of tables let say over 300 so its hard for me to guess which comes first... what should i keep in mind deleting rows with a referential integrity?

thank...

1. You can use sys.foreign_keys view to query and follow the data constraints in your table

2. You may try to use cascading referential integrity constraints.

By using cascading referential integrity constraints, you can define the

actions that the SQL Server 2005 takes when a user tries to delete or update a

key to which existing foreign keys point.

The REFERENCES clauses of the CREATE TABLE and

ALTER

TABLE statements support the ON DELETE and ON UPDATE clauses:

[ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT }

]

|||

hi carlop!

thank you for reply.... well the table in our database are not set to ON DELETE CASCADE ON due to security reason. so there is no way for me to delete the rows easily, i guess i should track all the tables for their foreign keys and dependent tables :-(

thanks, novelle

|||

hi there...

do u want to delete data from ur selected tables, and dont want their parent tables(PK tables) , to give foreign key errors......if thats the case, u can disable the foreign keys..perform the operation, then enable them again..

else if u want to delete from primary table first...or want to know the related tables, either use database digrams...or maybe this query will help u..

select a.name,c.name as pk_table ,b.name fk_table

from sys.foreign_keys a

inner join sys.sysobjects b on b.id = a.parent_object_id

inner join sys.sysobjects c on c.id = a.referenced_object_id

|||

hi nitin!

thank u for ur quick reply!

the thing is, im copying data from server to server, after copying the data i wanted to deleted this rows i've copied to the source database and offcourse i want to delete correctly.

i dont want to disable the foreignkeys because if my delete script is wrong , i wont able to delete the data correctly.

by the way does this script works on the SQL 2000? because i've tried it and it doesnt work.

thanks novelle.

|||

hi...for 2000 it'll be like

select a.name,c.name as pk_table ,b.name fk_table

from sys.foreign_keys a -- for this pls check the table sysconstraints/sysreferences...i dont quite remember the fields..

inner join sysobjects b on b.id = a.parent_object_id

inner join sysobjects c on c.id = a.referenced_object_id

|||A sql 2k compliant view of foreign keys and primary keys is the following:

CREATE view dbo.foreign_keys as
select cast(f.name as varchar(255)) as fk_name
, r.keycnt
, cast(ft.name as varchar(255)) as foreign_table
, cast(f1.name as varchar(255)) as foreign_col1
, cast(f2.name as varchar(255)) as foreign_col2
, cast(pt.name as varchar(255)) as primary_table
, cast(p1.name as varchar(255)) as primary_col1
, cast(p2.name as varchar(255)) as primary_col2
from sysobjects f
inner join sysobjects ft on f.parent_obj = ft.id
inner join sysreferences r on f.id = r.constid
inner join sysobjects pt on r.rkeyid = pt.id
inner join syscolumns p1 on r.rkeyid = p1.id and r.rkey1 = p1.colid
inner join syscolumns f1 on r.fkeyid = f1.id and r.fkey1 = f1.colid
left join syscolumns p2 on r.rkeyid = p2.id and r.rkey2 = p1.colid
left join syscolumns f2 on r.fkeyid = f2.id and r.fkey2 = f1.colid
where f.type = 'F'
GO

CREATE view dbo.primary_keys as
select distinct
tbl.name TableName,
constrId.name PkName,
col.name ColName,
sik.keyno,
case when ix.indid = 1 then 1 else 0 end IsClustered
from sysobjects tbl
join sysconstraints constr on ( tbl.id = constr.id and tbl.xtype = 'U' and constr.status & 0x0001 = 0x0001 )
join sysobjects constrId on constrId.parent_obj = tbl.id and constrId.xtype = 'PK'
join sysindexes ix on constrId.name = ix.name and ix.id = tbl.id
join sysindexkeys sik on sik.id = tbl.id and sik.indid = ix.indid
join syscolumns col on col.id = tbl.id and sik.colid = col.colid
GO

Deleting rows with OPENXML

Hi there,
I'm trying to delete some rows from a table using an xml document (in
memory) and OpenXML in SQL Server. I have the following code, but it will
only delete the first record. Any ideas?
CREATE PROCEDURE [dbo]. [CompanyBusinessUnit_DeleteCompanyBusine
ssUnit_Delete]
@.CompanyHeirarchy nvarchar(4000)
AS
SET NOCOUNT ON;
DECLARE @.hDoc int
EXEC sp_xml_preparedocument @.hDoc OUTPUT, @.CompanyHeirarchy
DELETE FROM CompanyBusinessUnit
WHERE CompanyBusinessUnitID IN (Select CompanyBusinessUnitID
FROM OPENXML (@.hdoc, '/NewDataSet/DeletedNodes',2) WITH
(CompanyBusinessUnitID Integer))
EXEC sp_xml_removedocument @.hDoc
GO
Thanks,
WesTry,
DELETE
CompanyBusinessUnit
FROM
CompanyBusinessUnit
inner join
(
Select
CompanyBusinessUnitID
FROM
OPENXML (@.hdoc, '/NewDataSet/DeletedNodes',2)
WITH (CompanyBusinessUnitID Integer)
) as t
on CompanyBusinessUnit.CompanyBusinessUnitID = t.CompanyBusinessUnitID
AMB
"Wes" wrote:

> Hi there,
> I'm trying to delete some rows from a table using an xml document (in
> memory) and OpenXML in SQL Server. I have the following code, but it will
> only delete the first record. Any ideas?
> CREATE PROCEDURE [dbo]. [CompanyBusinessUnit_DeleteCompanyBusine
ssUnit_Delete]
> @.CompanyHeirarchy nvarchar(4000)
> AS
> SET NOCOUNT ON;
> DECLARE @.hDoc int
> EXEC sp_xml_preparedocument @.hDoc OUTPUT, @.CompanyHeirarchy
> DELETE FROM CompanyBusinessUnit
> WHERE CompanyBusinessUnitID IN (Select CompanyBusinessUnitID
> FROM OPENXML (@.hdoc, '/NewDataSet/DeletedNodes',2) WITH
> (CompanyBusinessUnitID Integer))
> EXEC sp_xml_removedocument @.hDoc
> GO
>
> Thanks,
> Wes|||Hi there,
Thanks for answering my post so quickly. Unfortunately your solution did
the same thing as the ones I've tried. It simply deleted the first row
matched from the XML. I have posted my XML below, maybe there is something
wrong with it.
<NewDataSet>
<DeletedNodes>
<CompanyBusinessUnitID>17</CompanyBusinessUnitID>
<CompanyBusinessUnitID>18</CompanyBusinessUnitID>
</DeletedNodes>
</NewDataSet>
Thanks,
Wes
"Alejandro Mesa" wrote:
> Try,
> DELETE
> CompanyBusinessUnit
> FROM
> CompanyBusinessUnit
> inner join
> (
> Select
> CompanyBusinessUnitID
> FROM
> OPENXML (@.hdoc, '/NewDataSet/DeletedNodes',2)
> WITH (CompanyBusinessUnitID Integer)
> ) as t
> on CompanyBusinessUnit.CompanyBusinessUnitID = t.CompanyBusinessUnitID
>
> AMB
>
> "Wes" wrote:
>|||Try,
DELETE
a
FROM
CompanyBusinessUnit as a
inner join
(
Select
CompanyBusinessUnitID
FROM
OPENXML (@.hdoc, '/NewDataSet/DeletedNodes',2)
WITH (CompanyBusinessUnitID Integer)
) as t
on a.CompanyBusinessUnitID = t.CompanyBusinessUnitID
AMB
"Wes" wrote:
> Hi there,
> Thanks for answering my post so quickly. Unfortunately your solution did
> the same thing as the ones I've tried. It simply deleted the first row
> matched from the XML. I have posted my XML below, maybe there is somethin
g
> wrong with it.
> <NewDataSet>
> <DeletedNodes>
> <CompanyBusinessUnitID>17</CompanyBusinessUnitID>
> <CompanyBusinessUnitID>18</CompanyBusinessUnitID>
> </DeletedNodes>
> </NewDataSet>
> Thanks,
> Wes
> "Alejandro Mesa" wrote:
>|||I thought the problem was with the DELETE statement, but no, it was the
"SELECT ... FROM OPENXML ..." that was selecting just the first row. Can you
try changing this select by:
Select
cast(cast([text] as nvarchar(25)) as int) as CompanyBusinessUnitID
FROM
OPENXML (@.hdoc, '/NewDataSet/DeletedNodes')
where
nodetype = 3
AMB
"Alejandro Mesa" wrote:
> Try,
> DELETE
> a
> FROM
> CompanyBusinessUnit as a
> inner join
> (
> Select
> CompanyBusinessUnitID
> FROM
> OPENXML (@.hdoc, '/NewDataSet/DeletedNodes',2)
> WITH (CompanyBusinessUnitID Integer)
> ) as t
> on a.CompanyBusinessUnitID = t.CompanyBusinessUnitID
>
> AMB
> "Wes" wrote:
>|||Your firts post will also work if you change the layout of the xml document
to:
<NewDataSet>
<DeletedNodes>
<CompanyBusinessUnitID>17</CompanyBusinessUnitID>
</DeletedNodes>
<DeletedNodes>
<CompanyBusinessUnitID>18</CompanyBusinessUnitID>
</DeletedNodes>
</NewDataSet>
AMB
"Alejandro Mesa" wrote:
> I thought the problem was with the DELETE statement, but no, it was the
> "SELECT ... FROM OPENXML ..." that was selecting just the first row. Can y
ou
> try changing this select by:
> Select
> cast(cast([text] as nvarchar(25)) as int) as CompanyBusinessUnitID
> FROM
> OPENXML (@.hdoc, '/NewDataSet/DeletedNodes')
> where
> nodetype = 3
>
> AMB
>
> "Alejandro Mesa" wrote:
>|||Excellent! Thank you very much as this one works perfectly. Now I'll have
to analyze the code and see what I was doing differently.
Thanks for your help.
Wes
"Alejandro Mesa" wrote:
> I thought the problem was with the DELETE statement, but no, it was the
> "SELECT ... FROM OPENXML ..." that was selecting just the first row. Can y
ou
> try changing this select by:
> Select
> cast(cast([text] as nvarchar(25)) as int) as CompanyBusinessUnitID
> FROM
> OPENXML (@.hdoc, '/NewDataSet/DeletedNodes')
> where
> nodetype = 3
>
> AMB
>
> "Alejandro Mesa" wrote:
>

deleting rows from one table when they exist in another

A project I'm working on has a daily update of a table and then reapplies
each day a table of updates called EditHistory.
What's the best way to do this while avoiding duplicate rows.
I want to copy all rows from the table that's been updated, then remove any
records that just came in that are also in EditHistory and then copy the
ones from EditHistory in. This way I'll be left with a combination of the
records that were never edited along with the ones that have been edited but
the ones have been changed would be gone because the ones from EditHistory
override them.
Thanks for your help.Hi Bob
The better option would be not to insert existing rows
UPDATE m
SET x = O.x, y=O.y
FROM MyTable m
JOIN MyOtherTable O ON m.PK = O.PK
INSERT MyTable ( PK, x , y )
SELECT O.PK, O.x, O.y
FROM myOtherTable O
LEFT JOIN MyTable m ON m.PK = O.PK
WHERE m.PK IS NULL
John
"bob" wrote:

> A project I'm working on has a daily update of a table and then reapplies
> each day a table of updates called EditHistory.
> What's the best way to do this while avoiding duplicate rows.
> I want to copy all rows from the table that's been updated, then remove an
y
> records that just came in that are also in EditHistory and then copy the
> ones from EditHistory in. This way I'll be left with a combination of the
> records that were never edited along with the ones that have been edited b
ut
> the ones have been changed would be gone because the ones from EditHistory
> override them.
> Thanks for your help.
>
>sql

deleting rows from one table when they exist in another

A project I'm working on has a daily update of a table and then reapplies
each day a table of updates called EditHistory.
What's the best way to do this while avoiding duplicate rows.
I want to copy all rows from the table that's been updated, then remove any
records that just came in that are also in EditHistory and then copy the
ones from EditHistory in. This way I'll be left with a combination of the
records that were never edited along with the ones that have been edited but
the ones have been changed would be gone because the ones from EditHistory
override them.
Thanks for your help.Hi Bob
The better option would be not to insert existing rows
UPDATE m
SET x = O.x, y=O.y
FROM MyTable m
JOIN MyOtherTable O ON m.PK = O.PK
INSERT MyTable ( PK, x , y )
SELECT O.PK, O.x, O.y
FROM myOtherTable O
LEFT JOIN MyTable m ON m.PK = O.PK
WHERE m.PK IS NULL
John
"bob" wrote:
> A project I'm working on has a daily update of a table and then reapplies
> each day a table of updates called EditHistory.
> What's the best way to do this while avoiding duplicate rows.
> I want to copy all rows from the table that's been updated, then remove any
> records that just came in that are also in EditHistory and then copy the
> ones from EditHistory in. This way I'll be left with a combination of the
> records that were never edited along with the ones that have been edited but
> the ones have been changed would be gone because the ones from EditHistory
> override them.
> Thanks for your help.
>
>

Deleting rows from many tables

What I'm trying to do is delete a user and all their related information within the other tables. I'm not wanting to delete the table, just one column with that user and their related information. So my Primary_Key is UserID within the table [alumni] and my three Foreign_Keys are CommentID, PhotoID, and AlbumID within the tables [comments], [photos], and [albums]. Here is some of the code that I have:

<asp:SqlDataSourceID="SqlDataSource2"runat="server"ConnectionString="<%$ ConnectionStrings:SoderquistString %>"DeleteCommand="DELETE FROM [alumni] WHERE [UserID] = @.UserID"SelectCommand="SELECT [UserID], [UserName], [FirstName], [LastName], [State] FROM [alumni] WHERE ([State] = @.State)"><DeleteParameters><asp:ParameterName="UserID"Type="Int32"/></DeleteParameters><SelectParameters><asp:ControlParameterControlID="DropDownList1"Name="state"PropertyName="SelectedValue"Type="String"/></SelectParameters></asp:SqlDataSource>

The users are set up in GridView form. Is there some type of DELETE command that I need to be writing that is different than the one above? I have tried adding onto the following DELETE statment:

DeleteCommand="DELETE FROM [alumni] WHERE [UserID] = @.UserID

DELETE FROM [photo] WHERE [UserID] = @.UserID;

DELETE FROM [album] WHERE [UserID] = @.UserID;

DELETE FROM [comment] WHERE [UserID] = @.UserID;

...but that doesn't work...and doesn't look right. I would really appreciate anyones suggestions or help that you may be able to provide. Thank you!

Try this. I tested for two tables

DeleteCommand="DELETE FROM [alumni] WHERE [UserID] = @.UserID;DELETE FROM [photo] WHERE [UserID] = @.UserID;DELETE FROM [album] WHERE [UserID] = @.UserID;DELETE FROM [comment] WHERE [UserID] = @.UserID"

|||

Thanks for the info. I've tried that already and I still get an error:

Invalid column name 'UserID'.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Invalid column name 'UserID'.

Any suggestions?

|||

Do you have UserID column for all tables you are going to delete?

Since I tested the syntax for only two tables, I will test for more tables to confirm whether the syntax works for multiple tables.

|||

This DeleteCommand works on the dummy test tables in SQL Server 2005:

DeleteCommand="DELETE FROM [T1] WHERE [ID1] = @.ID;DELETE FROM [T2] WHERE [ID1] = @.ID;DELETE FROM [T3] WHERE [ID1] = @.ID;DELETE FROM [T4] WHERE [ID1] = @.ID;DELETE FROM [T5] WHERE [ID1] = @.ID"<DeleteParameters><asp:ParameterName="ID"Type="Int32"/></DeleteParameters>

But if you click delete rows too fast, you may get error message for the parameter. You should be able to delete from a stored procedure with error checking from there.

|||

Limno, thanks and I appreciate the help you have given me. I was looking over my tables and noticed that there is a difference in one of them. I'm going to write out the four tables and a few columns that I have within them and hopefully this will help clear a few things up that I'm having trouble with:

alumni - UserID (PK), FirstName, LastName, UserName

photo - PhotoID (FK), AlbumID, PhotoPath

album - AlbumID (FK), UserID, PhotoPath, AlbumName

comments - CommentID (FK), UserID, Comment, UserName, CommentDate

What I have is one primary_key, which is UserID and the other three are foreign_keys (PhotoID, AlbumID, and CommentID). All of the foreign keys have UserID in them except for the photo table because it associates with the album table that has UserID in it. That was the one difference that I forgot about.

So is it possible, with the way I have this set up for me to be able to delete a user from the alumni table along with the other information in the other tables that are associated with that user? I can delete a user if there are no albums, photos or comments connected with that person, but it's when they have something connected to them a problem comes up because of certain constraints.

Thank you in advanced!

|||

Hello:

I tested with the following DELETECommand:

DeleteCommand="DELETE FROM [photo] WHERE ([phtoID] IN (SELECT photoID FROM album WHERE userID = @.userID)); DELETE FROM [comments] WHERE [userID] = @.userID;DELETE FROM [alumni] WHERE [userID] = @.userID;DELETE FROM [album] WHERE [userID] = @.userID"

It seems that the cascading delete through a trigger would be a perfect fit for your case. However, I rearrange the delete action sequence for your case in this DeleteCommand. I tested with success. But you need to test on your data to make sure it works properly. If you want to control the flow, you may need to go with the trigger and/or stored procedure to handle your multiple cascading deletion.

|||

It worked! Thank you for your time, I really appreciate it. It looks like my problem was with the way I was deleting the information from the photo table and how it was related to the albums. This peace of code here...

DELETE FROM [photo] WHERE ([phtoID] IN (SELECT photoID FROM album WHERE userID = @.userID));

...did the trick.

Just for my understanding, what did you mean by "If you want to control the flow, you may need to go with the trigger and/or stored procedure to handle your multiple cascading deletion"? Thanks again!

|||

The code works but you have no way to know if something wrong with it during deletion. I did run into a few times during my test. That is what I mean. If the project becomes critical, you can go with the trigger and stored procedure. Or You can use your own error catch logic from code behind. Just my thought for your situation. I want to know what will happen if there is an error for learning purpose.

I am glad this hack works for you now. If you are interested, you can search for the topic on cascading deleting. The constrains on the foreign keys are very usful to maintain the integrity of your data set.

deleting rows based on nvarchar data

I have a table that contains rows that I would like to delete based on a field and it's contents.
What is the correct syntax to script the removal of these rows based field parameter?delete tableA where fieldB = ?

Is that what you mean?|||Kinda. I only have one table and want to delete specific rows from that table that have a specific data within a certain field.

Let be more specific. I have a table (tableA) with 10 fields. Field 3 has data that does not conform to a datetime format and I would like to remove it. The field is currently a nvarchar(50) type (2003-10-10).

I want to remove rows that contain data that is looks like this
(0020-10-10). Make sense?|||delete from table where field3 ='0020-10-10'|||Perhaps you can use the ISDATE() function, which returns 1 if a string can be converted to a valid date, and 0 if it cannot.

Try this query:

select *
from YourTable
where ISDATE([Column3]) = 0

If this returns the rows you want deleted, then change the query to a delete query:

delete
from YourTable
where ISDATE([Column3]) = 0|||right but I forgot to mention, there are all kinds of variation of that date.

I ran a script that reads as follows to help identify data within a field that does not fit a date format->

SELECT * FROM findet WHERE ISDATE(servfrom) = 0

This gave me a list of records that are not in proper date format. Now, I would like to remove them from my table. Can I use the same,

delete findet where ISDATE(servfrom)=0|||See previous post.|||That worked like a charm, thank you!!

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 rows

What tool are you using?

>--Original Message--
>I have a table wherein previous entries on some rows were
deleted but the
>fields doesn't go away ex.
>tbl_name (one column table only)
>row1 name 1
>row2 (the entry is deleted but this is still showing a
blank space)
>row3 (the entry is deleted but this is still showing a
blank space)
>row4 (the entry is deleted but this is still showing a
blank space)
>row5 name 2
>How do i delete rows 2-4 in tbl_name so that it will only
show two entries
>row1 and row5 which should move into position 2 basically
deleting rows 2-4
>which contains no data and wont allow me to enter data in
them?
>thanks....
>.
>
When you delete the rows, it sounds to me like you are really only updating
the value of the data to a blank rather than actually removing the row.
The procedure in your application is probably doing an update like:
UPDATE tblName
SET columnName = ''
WHERE columnName = '<some criteria>'
The procedure in your application should be doing a DELETE like:
DELETE tblName
WHERE columnName = '<some criteria>'
To get rid of the currently empty rows, you could run something like:
DELETE tblName
WHERE LENGTH(columnName) = 0
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:08ff01c47afa$4c7e3a10$a301280a@.phx.gbl...[vbcol=seagreen]
> What tool are you using?
> deleted but the
> blank space)
> blank space)
> blank space)
> show two entries
> deleting rows 2-4
> them?
sql

Deleting rows

What tool are you using?

>--Original Message--
>I have a table wherein previous entries on some rows were
deleted but the
>fields doesn't go away ex.
>tbl_name (one column table only)
>row1 name 1
>row2 (the entry is deleted but this is still showing a
blank space)
>row3 (the entry is deleted but this is still showing a
blank space)
>row4 (the entry is deleted but this is still showing a
blank space)
>row5 name 2
>How do i delete rows 2-4 in tbl_name so that it will only
show two entries
>row1 and row5 which should move into position 2 basically
deleting rows 2-4
>which contains no data and wont allow me to enter data in
them?
>thanks....
>.
>When you delete the rows, it sounds to me like you are really only updating
the value of the data to a blank rather than actually removing the row.
The procedure in your application is probably doing an update like:
UPDATE tblName
SET columnName = ''
WHERE columnName = '<some criteria>'
The procedure in your application should be doing a DELETE like:
DELETE tblName
WHERE columnName = '<some criteria>'
To get rid of the currently empty rows, you could run something like:
DELETE tblName
WHERE LENGTH(columnName) = 0
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"ChrisR" <anonymous@.discussions.microsoft.com> wrote in message
news:08ff01c47afa$4c7e3a10$a301280a@.phx.gbl...[vbcol=seagreen]
> What tool are you using?
>
> deleted but the
> blank space)
> blank space)
> blank space)
> show two entries
> deleting rows 2-4
> them?

deleting rows

I have a table with approx 5 million rows and 36 columns. It takes approx
4 minutes to delete 1 row. The table has 3 indexes in addition to it's primary key and has twelve foreign key constraints. We are still using sequel 7.
There is a backup run every night as part of the nightly maintenence that
reorg/reindexes and checks the database integrity. Any thoughts?

Thankswell I depends what your deleting on. Make sure that you're using an indexed column, and check the execution plan to make sure that it's doing a table seek and not a table scan.

What are you stats for this table set to ?|||I am using a simple delete such as delete from tablename where transsk = 1002
with transsk being the primary key. This has only become a problem once the table grew over a mil rows.|||what kind of index, clustered or non-clustered ?

and did you set a fill factor on the table ?

setting your index properly should bring down your delete to a few seconds.
I've got tables that are 6mil+ rows and a delete takes < 15 secs.|||The primary key is non-clustered with a fill factor of 90.
There are also three indexes. Two non-clustered and one clustered, all three with a fill factor of 90. It's also odd to me that inserting rows is not a problem.|||Inserting a row shouldn't be much of a problem as you don't have to seek to insert a row. If you've got a clustered index, there is a little bit of overhead as the data needs to be arranged logically. IE, it may have to shuffle other rows around to properly fit in the one you are inserting. With a non-clustered index, it can just append the row to the logical group and add an entry into the tree.

What you may want to try for benchmarking purposes is to remove the clustered index and see if you get a performance increase when inserting or deleting. I don't think you'll get much, but it's worth a shot...

have you taken a look at the execution plan for a simple delete like the one you posted ?|||Thanks, I will give that a try by removing the clustered index.
Do you have tables with as many foreign key constraints? I didn't know if 12 was a unusually large amount.

Also, I guess I'm an idiot, what do you mean by execution plan?|||If you open query analyzer, there is a button at the top that will show you the proposed execution plan that SQL server will use when you run that SQL. The execution plan is created based on statistics.

Also, I think 12 FK constraints on one table is *a lot*. You should really only have 1 to 3. That's likely the reason it's taking so long to delete anything, it's got many constraints to check before deleting a row.

Cheers,
-Kilka|||Use this sample and apply your own code and cut and paste what it returns

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(Col1 int IDENTITY PRIMARY KEY, Col2 char(1))
GO

INSERT INTO myTable99(Col2)
SELECT 'A' UNION ALL
SELECT 'B' UNION ALL
SELECT 'C'
GO

SET SHOWPLAN_TEXT ON
GO

DELETE FROM myTable99 WHERE Col1 = 2
GO

SET SHOWPLAN_TEXT OFF
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO

Deleting repeated data

The table contains data as follows,

SAP_CUS TOMER_NBRSAP_CUSTOMER_NBR1
A B
B A
C D
D C
C E
E C

A=B is same as B=A

The final table should be as follows,

SAP_CUSTOMER_NBRSAP_CUSTOMER_NBR1
A B
C D
C E

please help me in doing this

Quote:

Originally Posted by venkat81

The table contains data as follows,

SAP_CUS TOMER_NBRSAP_CUSTOMER_NBR1
A B
B A
C D
D C
C E
E C

A=B is same as B=A

The final table should be as follows,

SAP_CUSTOMER_NBRSAP_CUSTOMER_NBR1
A B
C D
C E

please help me in doing this

Provided the duplication set is as you say a single character swapped round like you indicate, the principle is to extract the SAME AS records as you point out and remove them. The logic being to identify the row to extract and leave you with the result set you want. So.. how can this be done?.

Each character will have an ASCII representation, a numeric value. 'A'=65 and B='66' Added together they make 131 so over two rows we are going to see two values of 131.

If we created a third column to store the ASCII value and worked on that we could extract out the MAXIMUM record ID identifying the row, extract those rows into a temporary table compare the tables against each other and remove the rows by the relevant SQL comparison on a LEFT INNER JOIN between the relevant tables.

One word of CAUTION here, The ascii values when summed will give a value remember that 8+5=12 so does 5+7 so the actual theory has limited scope
and is absolutely based on your data provided above, You do NOT want a summation giving you an erronous result value on which you base your delete.

Anyway below is a script I knocked up to assist you in demonstrating the HOWS!! not intended as the SOLUTION obviously as that is for you to deal with If you create a physical table ..like so

CREATE TABLE mytable (
[recid] [int] null ,
[sap_customer_nbr] [char] (1)null ,
[sap_customer_nbr1] [char] (1) null ,
[asciivalue] [int] null )

and populate it with data of the type you provided above and then run the script below in query analyser you will see that it works only on temporary tables, which you can adjust to suit your production environment when you are ready and happy it works for you.

--USE whateverdatabasenamehere --<<<replace database name with yours
SET NOCOUNT ON
--create a temporary table to store the values
CREATE TABLE #mytable (
[recid] [int] null ,
[sap_customer_nbr] [char] (1)null ,
[sap_customer_nbr1] [char] (1) null ,
[asciivalue] [int] null )
--and the insert into this table
INSERT #mytable
--values from the main production table
SELECT * from mytable --<<substitute your table name here the column orders must match
--then update the temporary table with --the ACSII numeric values representing the characters ie A and B summed together=131
UPDATE #mytable
SET asciivalue=ASCII(sap_customer_nbr) + ASCII(sap_customer_nbr1)
--create another temporary table
CREATE TABLE #mylink (
[asciivalue] [int] null ,
[recid] [int] null )
--into which we will insert
INSERT #mylink
-- the MAXIMUM RecordID (RecID) for each Asciivalue which essential only gives
-- ACTUAL recordID to identify the row that we wish ultimately to Delete from the table
SELECT #mytable.asciivalue, MAX(#mytable.recid) AS linkkey
FROM #mytable
GROUP BY #mytable.asciivalue

--the next two lines are left in to merely show you in query analyser the resultsets
SELECT * FROM #mytable
SELECT * FROM #mylink

--we now delete from the first temporary table the rows we wish to remove
DELETE #mytable
FROM #mylink INNER JOIN #mytable ON #mylink.recid = #mytable.recid
--and to finalise we show the results again in query analyser
SELECT * FROM #mytable
--and finally drop the temporary explicitly
DROP TABLE #mylink
DROP TABLE #mytable|||

Quote:

Originally Posted by Jim Doherty

Each character will have an ASCII representation, a numeric value. 'A'=65 and B='66' Added together they make 131 so over two rows we are going to see two values of 131.

Anyway below is a script I knocked up to assist you in demonstrating the HOWS!! not intended as the SOLUTION obviously as that is for you to deal with If you create a physical table ..like so

--the next two lines are left in to merely show you in query analyser the resultsets
SELECT * FROM #mytable
SELECT * FROM #mylink

i have not read the entire solution, but i have one comment, 8 + 5 <> 12...|||

Quote:

Originally Posted by ck9663

i have not read the entire solution, but i have one comment, 8 + 5 <> 12...

It is where I come from hahahaha

deleting records older than 180 days

I have table with over 1 billion rows, which I'd like to purge by deleting
all records older than 180 days from the current date (using the getdate()
function). Any help here would be great. Thanks."Rob" <Rob@.discussions.microsoft.com> wrote in message
news:92F6F0D2-78AE-403B-8178-16ACA35E4CF3@.microsoft.com...
>I have table with over 1 billion rows, which I'd like to purge by deleting
> all records older than 180 days from the current date (using the getdate()
> function). Any help here would be great. Thanks.
If you have a datetime stamp in the table, then this is relatively simple.
DELETE <table name>
WHERE DATEDIFF( dd, <datetime column name> , GETDATE() ) > 180
If you don't have a datetime stamp in the table, then maybe one of your
related tables can give this information.
If you supply the DDL for your table and some sample data, we can help you
out with a better query.
See the following link for more information:
http://www.aspfaq.com/etiquette.asp?id=5006
Rick Sawtell
MCT, MCSD, MCDBA|||Well Rob, I'm assuming that you have a date field that you can use to
reference?
Assuming you have, the process is quiet simple, but depending on how many
rows you are trying to delete in one go, you may have problems with your
log files.
The syntax would be something like,
delete from table where YOURdatetimefield < (dd, -180, getdate())
Like i say, you should keep an eye on your log space and also, the first
time you run this process could take a while.
Immy
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:92F6F0D2-78AE-403B-8178-16ACA35E4CF3@.microsoft.com...
>I have table with over 1 billion rows, which I'd like to purge by deleting
> all records older than 180 days from the current date (using the getdate()
> function). Any help here would be great. Thanks.|||excuse the missing sytax...
delete from table where YOURdatetimefield < dateadd(d, -180, getdate())
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:uvH7FD8OGHA.3944@.tk2msftngp13.phx.gbl...
> Well Rob, I'm assuming that you have a date field that you can use to
> reference?
> Assuming you have, the process is quiet simple, but depending on how many
> rows you are trying to delete in one go, you may have problems with your
> log files.
> The syntax would be something like,
>
> Like i say, you should keep an eye on your log space and also, the first
> time you run this process could take a while.
> Immy
> "Rob" <Rob@.discussions.microsoft.com> wrote in message
> news:92F6F0D2-78AE-403B-8178-16ACA35E4CF3@.microsoft.com...
>|||Hope you have a date field in your table...
In that case, try
select * from tablename where DATEDIFF(day,DATEfield,getdate())>=180
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"Rob" wrote:

> I have table with over 1 billion rows, which I'd like to purge by deleting
> all records older than 180 days from the current date (using the getdate()
> function). Any help here would be great. Thanks.|||In addition to the other replies about how to implement the date comparison,
keep the following in mind:
For performance reasons, you will probably want to delete the rows in blocks
of 10,000 rather than one large transactions.
http://groups.google.com/group/micr...br />
22014423
Rather than purging the data from your database, you may want to migrate the
data to another table and then join it using a partitioned view.
http://www.microsoft.com/technet/pr.../2005/spdw.mspx
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:92F6F0D2-78AE-403B-8178-16ACA35E4CF3@.microsoft.com...
>I have table with over 1 billion rows, which I'd like to purge by deleting
> all records older than 180 days from the current date (using the getdate()
> function). Any help here would be great. Thanks.|||>For performance reasons, you will probably want to delete the rows in blocks
>of 10,000 rather than one large transactions.
>http://groups.google.com/group/micr...201442
3
Good point, JT.
When I have been faced with this sort of massive purge of old data in
the past I found it easiest to process a single day at a time, in a
loop, oldest to newest. This keeps the rows per DELETE down to a very
conservative number. It also allowed me to add a pause in there
(WAITFOR DELAY) to let the system do other things, and the log purge
(this was using what is now called the simple recovery model, where
logs are truncated). This approach also let me interupt it and
restart it later if I had to, since it was written to start with the
oldest date currently in the file, and cancelling only wasted the
current "day" it was working on.
Roy Harvey
Beacon Falls, CT|||Thanks JT.
Through several responses to my post, I was able to construct and parse the
appropriate delete stmt. I found your suggestion of deleting in batches to b
e
recommendable. However, I'm struggling with putting a viable criteria for th
e
WHILE clause in order for the loop to start and NOT continue infinitely.
Thanks again.
"JT" wrote:

> In addition to the other replies about how to implement the date compariso
n,
> keep the following in mind:
> For performance reasons, you will probably want to delete the rows in bloc
ks
> of 10,000 rather than one large transactions.
> http://groups.google.com/group/micr... />
6922014423
> Rather than purging the data from your database, you may want to migrate t
he
> data to another table and then join it using a partitioned view.
> http://www.microsoft.com/technet/pr.../2005/spdw.mspx
> "Rob" <Rob@.discussions.microsoft.com> wrote in message
> news:92F6F0D2-78AE-403B-8178-16ACA35E4CF3@.microsoft.com...
>
>|||Hi Rob,
In the example below, the WHERE clause would simply need to reference the
DateEntered column. The "set rowcount 1000" statement limits each iteration
to 1000 or fewer rows, and what prevents an infinite loop is the statement
"if @.@.rowcount = 0 break". When no more rows are available for deletion,
@.@.rowcount will be 0 and the loop will be terminated with the "break"
statement.
set rowcount 1000
while
delete from mytable where DateEntered < '2005/10/22'
if @.@.rowcount = 0 break
checkpoint
end
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:EE27EBFD-428E-465F-9EC1-CA5E03700458@.microsoft.com...
> Thanks JT.
> Through several responses to my post, I was able to construct and parse
> the
> appropriate delete stmt. I found your suggestion of deleting in batches to
> be
> recommendable. However, I'm struggling with putting a viable criteria for
> the
> WHILE clause in order for the loop to start and NOT continue infinitely.
> Thanks again.
> "JT" wrote:
>sql

deleting records in associated foeign key table

Hi, i need the suggestion here in very familiar db situation ..i have a main table and a primary key of that table is used in many other table as foreign key.If i am deleting a record in a main table,how do i make sure that all the corresponding record in the associated tables,where that foreign key is used, gets deleted too?What are my options?Thanks

There are a few options, but the best two, IMO, are:

Write a SQL statement that first deletes the associated records and then deletes the parent record. Imagine that you had a Categories table and a Products table, and there is a one to many relationship from Categories to Products. Now, imagine that we wanted to delete a category that has associated products. We'd first need to delete the Products - DELETE FROM Products WHERE CategoryID = @.CategoryIDToDelete - then we'd delete the category: DELETE FROM Categories WHERE CategoryID = @.CategoryIDToDelete.
|||

Thank a lot Scott for quick response...i have got multiple tables and all these table are kind of chained up with each other using multiple foreign key .i am liking the idea of 'cascade delete',I have already set up the foreign key constraints in all these table.I will read up on cascade delete,,have no idea about it rightnow...

|||

Scott is correct on the two main options. Given that this question is a "newbie" question, it's probably best to clarify that the first option is to write TWO sql statements, not one - a delete for each table.

|||

David, do you mean,two sql statements(or as many delete as reqd for linked up tables) within one stored procedures or two(or multiple depends?) separate sql statements ?i am heavily using SP in my application.thanks

|||

I mean 1 delete statement per table that needs deleting. Those delete statements can be in one stored procedure.

My comments were to avoid some one new to sql trying to issue a delete statement like this (because it will never work):

delete department and employee where department_id = 5

Deleting Records in a Table

Hi,
I have a table which has some columns which have repititive values. I want to keep the first value(record) of column(which has the repitive values) and then delete the other records which repeat. Please let me know.
Thanks.
Example:
Table:
column 1 Column2 Column3 Column4
1 10 23 15
2 12 26 14
3 13 25 14
4 100 250 14
I want to delete records number 3-4 but retain the 2nd record.Hello!

Is column1 an ID-column in this table? If yes, you can identify the record to delete with the following query

select * from table_b t1
where t1.column1 not in (select min(t2.column1)
from dbo.table_b t2
where t2.column4 = t1.column4)

If no, you should look up this post http://www.dbforums.com/t926686.html.

If there are more quetions, post again!

Greetings,
Carsten|||Originally posted by CarstenK
Hello!

Is column1 an ID-column in this table? If yes, you can identify the record to delete with the following query

select * from table_b t1
where t1.column1 not in (select min(t2.column1)
from dbo.table_b t2
where t2.column4 = t1.column4)

If no, you should look up this post http://www.dbforums.com/t926686.html.

If there are more quetions, post again!

Greetings,
Carsten
Hi Carsten.
Thanks for the SQL, but I am a little bit confused about the Query! I have just one table, but in your query you have stated Table_t1 and Table_t2...Please let me know.
Thanks.|||Hi,

the only table used in this query should be "table_b". But this one twice! If you have a query accessing the same table more than once (like here, in the query and sub query) you should use the synonyms to ensure which table you exactly mean. The use of synonyms is like this:

<synonym>.<column name>

So, just one table is used, but in two different forms.

Greetings,
Carsten|||Originally posted by CarstenK
Hi,

the only table used in this query should be "table_b". But this one twice! If you have a query accessing the same table more than once (like here, in the query and sub query) you should use the synonyms to ensure which table you exactly mean. The use of synonyms is like this:

<synonym>.<column name>

So, just one table is used, but in two different forms.

Greetings,
Carsten

Where and How do we Delete the repititive records from the Original Table?
Thanks Again!|||Now that you know (and see) which record to delete, you only need to exchange the "SELECT *"-statement for the "DELETE"-statement.

Carsten|||Originally posted by CarstenK
Now that you know (and see) which record to delete, you only need to exchange the "SELECT *"-statement for the "DELETE"-statement.

Carsten
Thank you, Mr. Carsten!!!!!|||Originally posted by CarstenK
Now that you know (and see) which record to delete, you only need to exchange the "SELECT *"-statement for the "DELETE"-statement.

Carsten
Hi Carsten,
I am using Sybase Central and as a result only the Query with Select statement in it is working but when I replace it with Delete...it is not working! Any suggestions for this??|||Hi there,

the normal syntax for delete looks like

delete from <table_name>
[where <where_condition>

Carsten|||You probably left the * after the delete statement, which is MS Access syntax but is not acceptable in SQL Server.

Here is my preferred method, using joins instead of where clause:

delete
from YourTable
inner join
(select column4, min(column1) column1
from YourTable
group by column4) FirstValues
on YourYable.column4 = FirstValues.column4
where YourTable.column1 > FirstValues.column1

WHERE clauses with subquerys and NOT IN statements are not as efficient as table joins, although in some cases the optimizer can convert the syntax to a JOIN prior to developing an execution plan.

blindman|||Hi, I used the owner moon:

create table moon.tempt
(tid integer primary key,
value integer)

insert into moon.tempt values( 1, 15)
insert into moon.tempt values( 2, 14)
insert into moon.tempt values( 3, 14)
insert into moon.tempt values( 4, 14)

delete t1
from moon.tempt t1
where
t1.tid <> (select min(t2.tid)
from moon.tempt t2
where t2.value = t1.value)

finally:

select * from moon.tempt

tid value
---- ----
1 15
2 14|||Again, while the optimizer might be able to convert this into standard JOIN syntax, if it cannot then you are essentially asking SQL Server to run

select min(t2.tid) from moon.tempt t2 where t2.value = t1.value

...once for every record in table tempt. Not as efficient as a JOIN clause which only executes the subquery once.

Also, the "<>" operator is particularly ineffecient, because it is generally non-sargable and cannot take advantage of indexes. As a matter of fact, it is the least efficient of all the comparison operators.

blindman

Thursday, March 29, 2012

deleting table phisically

Deleting Table

Deleting Table

Deleting Table

Deleting Table

Deleting semi duplicates

DELETING ROWS with REFERENTIAL INTEGRITY

Deleting rows with OPENXML

deleting rows from one table when they exist in another

deleting rows from one table when they exist in another

Deleting rows from many tables

Invalid column name 'UserID'.

deleting rows based on nvarchar data

Deleting rows

Deleting rows

Deleting rows

deleting rows

Deleting repeated data

deleting records older than 180 days

deleting records in associated foeign key table

Deleting Records in a Table

DENY permission,SQL server

Blog Archive

About Me