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 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

Deleting records from a table takes a long time

I have a table A that has about 35000 rows. Table B has about 2 million rows
.
Three columns colX,colY and colZ in table B have referential integrity
constraints with the primary key of table A. i.e. fk_1 for colX referencing
pk of table A, fk_2 for colY referencing pk of table B,fk_3 for colZ
referencing pk of table C ( There are other fks also on tableB and indexes)
When I delete rows from table A the delete takes a very long time sometimes
about 40 minutes for about 10 rows( if I allow the delete sql to run) Is
there a way I can speed up the delete from table A? Or any other things I
should look into?Are your statistics up to date? See UPDATE STATISTICS in BOL.
"Frank1213" <Frank1213@.discussions.microsoft.com> wrote in message
news:6EDF8E2A-8AEE-439B-9ACB-A36CE3F7956B@.microsoft.com...
>I have a table A that has about 35000 rows. Table B has about 2 million
>rows.
> Three columns colX,colY and colZ in table B have referential integrity
> constraints with the primary key of table A. i.e. fk_1 for colX
> referencing
> pk of table A, fk_2 for colY referencing pk of table B,fk_3 for colZ
> referencing pk of table C ( There are other fks also on tableB and
> indexes)
> When I delete rows from table A the delete takes a very long time
> sometimes
> about 40 minutes for about 10 rows( if I allow the delete sql to run) Is
> there a way I can speed up the delete from table A? Or any other things I
> should look into?|||Frank1213 wrote:
> I have a table A that has about 35000 rows. Table B has about 2
> million rows. Three columns colX,colY and colZ in table B have
> referential integrity constraints with the primary key of table A.
> i.e. fk_1 for colX referencing pk of table A, fk_2 for colY
> referencing pk of table B,fk_3 for colZ referencing pk of table C (
> There are other fks also on tableB and indexes) When I delete rows
> from table A the delete takes a very long time sometimes about 40
> minutes for about 10 rows( if I allow the delete sql to run) Is there
> a way I can speed up the delete from table A? Or any other things I
> should look into?
If table A has FK values in table B, then you can't delete from table A
unless you have set up cascading deletes. Have you? The other way to
delete is to manually remove the table B rows that match the PK in table
A, then delete from table A.
It's impossible to really guess where the holdup is in your testing. I
assume you have an index on the FK col1X in tableB, right?
David Gugick
Imceda Software
www.imceda.com|||Do you have an index on the foreign key in table B that references table A?
If not then when you delete from A it will probably be doing table scans of
table B to perform the referential integrity action (validate, cascade
delete/update).
"Frank1213" wrote:

> I have a table A that has about 35000 rows. Table B has about 2 million ro
ws.
> Three columns colX,colY and colZ in table B have referential integrity
> constraints with the primary key of table A. i.e. fk_1 for colX referenci
ng
> pk of table A, fk_2 for colY referencing pk of table B,fk_3 for colZ
> referencing pk of table C ( There are other fks also on tableB and indexes
)
> When I delete rows from table A the delete takes a very long time sometime
s
> about 40 minutes for about 10 rows( if I allow the delete sql to run) Is
> there a way I can speed up the delete from table A? Or any other things I
> should look into?|||Run the delete with showplan and statistics io to see where your slowdown is
at. Have you done this yet? Post the results, the original query, and the
ddl. Maybe we can help you out with a more educated guess. :)
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:O10aVwQKFHA.3552@.TK2MSFTNGP12.phx.gbl...
> Frank1213 wrote:
> If table A has FK values in table B, then you can't delete from table A
> unless you have set up cascading deletes. Have you? The other way to
> delete is to manually remove the table B rows that match the PK in table
> A, then delete from table A.
> It's impossible to really guess where the holdup is in your testing. I
> assume you have an index on the FK col1X in tableB, right?
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>|||Like Derrick, I would guess that putting an index on the foreign key in the
child table would help. Can you define "sometimes"? Does it sometimes run
in 40 ms? How busy is the system at the time? Do you have adequate
hardware?
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Frank1213" <Frank1213@.discussions.microsoft.com> wrote in message
news:6EDF8E2A-8AEE-439B-9ACB-A36CE3F7956B@.microsoft.com...
>I have a table A that has about 35000 rows. Table B has about 2 million
>rows.
> Three columns colX,colY and colZ in table B have referential integrity
> constraints with the primary key of table A. i.e. fk_1 for colX
> referencing
> pk of table A, fk_2 for colY referencing pk of table B,fk_3 for colZ
> referencing pk of table C ( There are other fks also on tableB and
> indexes)
> When I delete rows from table A the delete takes a very long time
> sometimes
> about 40 minutes for about 10 rows( if I allow the delete sql to run) Is
> there a way I can speed up the delete from table A? Or any other things I
> should look into?

Deleting duplicates

I have a table with 5 columns.
Column 1 is the ID and is unique
Column 2 is a number and has many duplicates
Column 3-5 are just desicrptions
It holds 60,000 products but many man of them are duplicates, the way I know
is that they have the same code in column # 2
How can I select only non-duplicates ?
Column 1: ID
Column 2: SKU
Column3: Description
Column 4: Price
Column5: Quantity
I need to select all columns, that's why I could not : select distinct SKU
from table, because it would only select one column, how can
I select all columns where sku is unique ?
ASELECT id, sku, description, price, quantity
FROM YourTable AS T
WHERE id =
(SELECT MIN(id)
FROM YourTable
WHERE sku = T.sku)
David Portas
SQL Server MVP
--|||Excelent !
Thanks David
A
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1112881987.619604.173390@.z14g2000cwz.googlegroups.com...
> SELECT id, sku, description, price, quantity
> FROM YourTable AS T
> WHERE id =
> (SELECT MIN(id)
> FROM YourTable
> WHERE sku = T.sku)
> --
> David Portas
> SQL Server MVP
> --
>sql

Deleting duplicate entries in nightmare-table

Hello
I was given the task to "filter away" duplicate rows in a 10 million row
table with about 30 columns :S And the table has no primary key, no
constrains, nothing, and I need to find a way to clear that mess up, in a
way... sigh
You guys must think, alright, this is easy, just group by, well well, that
would be too easy, since each row isnt really "unique", lets continue the
mess....
Lets say the table contains 5 columns, A B C D E
In the new table there shall be a CHECK(A,B,C), that combination is unique.
But in the old table there are duplicates of that, and the values of D and E
may be any value on each row. (Headache yet?) Ppl who create these kinds of
heaptables should be lined up and shot :(
Lemme post some test DLL:
CREATE TABLE #Test (
A int,
B int,
C int,
D int,
E int
)
INSERT INTO #Test(A,B,C,D,E)VALUES(1,1,1,9,8)
INSERT INTO #Test(A,B,C,D,E)VALUES(1,1,1,-1,6)
INSERT INTO #Test(A,B,C,D,E)VALUES(1,2,1,1,4)
INSERT INTO #Test(A,B,C,D,E)VALUES(3,2,1,1,4)
INSERT INTO #Test(A,B,C,D,E)VALUES(3,2,1,3,4)
INSERT INTO #Test(A,B,C,D,E)VALUES(3,2,1,3,4)
SELECT * FROM #Test
/*
DESIRED RESULT
A B C D E
1,1,1,9,8
1,2,1,1,4
3,2,1,1,4
*/
DROP TABLE #Test
And everything is so screwed up it doesnt "matter" which values D and E has
(or the other 20 columns) as long as they had the value it had before. And
all columns are like varchar(x) in the table, so keep that in mind :S> And everything is so screwed up it doesnt "matter" which values D and E
> has
> (or the other 20 columns) as long as they had the value it had before.
This doesn't make sense to me, which value did it have before?
Anyway, I'll try, can you do this:
SELECT A,B,C,MAX(D),MAX(E)
FROM table
GROUP BY A,B,C;
?
There is no ANY() function, so you either need to choose an aggregate, or
maybe you could use a correlated subquery with ORDER BY CHECKSUM(NEWID())
but I'm not clear that would work, nor without better requirements am I
inclined to try.
A|||Lasse Edsvik wrote:
> Hello
> I was given the task to "filter away" duplicate rows in a 10 million row
> table with about 30 columns :S And the table has no primary key, no
> constrains, nothing, and I need to find a way to clear that mess up, in a
> way... sigh
> You guys must think, alright, this is easy, just group by, well well, that
> would be too easy, since each row isnt really "unique", lets continue the
> mess....
> Lets say the table contains 5 columns, A B C D E
> In the new table there shall be a CHECK(A,B,C), that combination is unique
.
> But in the old table there are duplicates of that, and the values of D and
E
> may be any value on each row. (Headache yet?) Ppl who create these kinds o
f
> heaptables should be lined up and shot :(
> Lemme post some test DLL:
>
> CREATE TABLE #Test (
> A int,
> B int,
> C int,
> D int,
> E int
> )
> INSERT INTO #Test(A,B,C,D,E)VALUES(1,1,1,9,8)
> INSERT INTO #Test(A,B,C,D,E)VALUES(1,1,1,-1,6)
> INSERT INTO #Test(A,B,C,D,E)VALUES(1,2,1,1,4)
> INSERT INTO #Test(A,B,C,D,E)VALUES(3,2,1,1,4)
> INSERT INTO #Test(A,B,C,D,E)VALUES(3,2,1,3,4)
> INSERT INTO #Test(A,B,C,D,E)VALUES(3,2,1,3,4)
>
> SELECT * FROM #Test
> /*
> DESIRED RESULT
>
> A B C D E
> 1,1,1,9,8
> 1,2,1,1,4
> 3,2,1,1,4
> */
> DROP TABLE #Test
>
> And everything is so screwed up it doesnt "matter" which values D and E ha
s
> (or the other 20 columns) as long as they had the value it had before. And
> all columns are like varchar(x) in the table, so keep that in mind :S
Always tell us what version of SQL Server you are using.
In SQL Server 2005:
WITH T (row_num) AS
(SELECT ROW_NUMBER() OVER
(PARTITION BY a,b,c ORDER BY a,b,c,d,e)
FROM #Test)
DELETE FROM T
WHERE row_num > 1;
Google for "delete duplicates" and you'll find lots of other solutions
in the archives of this group.
Test it out and make sure you have a current backup first :-)
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/ms130214(en-US,SQL.90).aspx
--|||David,
sry, forgot that :) I'm using SQL 2000
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1147360308.771189.249980@.i40g2000cwc.googlegroups.com...
> Lasse Edsvik wrote:
a
that
the
unique.
and E
of
has
And
> Always tell us what version of SQL Server you are using.
> In SQL Server 2005:
> WITH T (row_num) AS
> (SELECT ROW_NUMBER() OVER
> (PARTITION BY a,b,c ORDER BY a,b,c,d,e)
> FROM #Test)
> DELETE FROM T
> WHERE row_num > 1;
> Google for "delete duplicates" and you'll find lots of other solutions
> in the archives of this group.
> Test it out and make sure you have a current backup first :-)
> --
> 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/ms130214(en-US,SQL.90).aspx
> --
>|||Hi Aaron,
consider raw data like this:
INSERT INTO #Test(A,B,C,D,E)VALUES(1,1,1,9,6)
INSERT INTO #Test(A,B,C,D,E)VALUES(1,1,1,-1,8)
then the query
SELECT A,B,C,MAX(D),MAX(E)
FROM table
GROUP BY A,B,C;
will produce a row
1,1,1,9,8
which is not present in the original data. Does it make sence?|||alter table #test add f timestamp
go
select * from #test
go
select A,B,C,D,E from #test where not exists(select 1 from #test t1
where t1.a=#test.a
and t1.b=#test.b
and t1.c=#test.c
and t1.f>#test.f)
A B C D E
-- -- -- -- --
1 1 1 -1 6
1 2 1 1 4
3 2 1 3 4
(3 row(s) affected)|||> which is not present in the original data. Does it make sence?
I don't know, I don't think the requirements were specific enough to make
you right or to make me wrong. I was just offering one possible solution.

Deleting Duplicate Data

Hi to All!
I have a table with 60 columns and more than one million rows. i have to
implement composite primary key but it gives me error of duplicate data.
i have used following query to detect the duplicate rows
select NID,output_No from tbl_Data
group by NID,output_No
having count(*) > 1
it gives me 2526 duplicate dows. Now i wants to delete the duplicate
rows what will be the query for deleting the duplicate records.
Thanx
*** Sent via Developersdex http://www.examnotes.net ***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
"Ghulam Farid" <gfaryd@.yahoo.com> wrote in message
news:umVO5durFHA.3444@.TK2MSFTNGP12.phx.gbl...
>
> Hi to All!
> I have a table with 60 columns and more than one million rows. i have to
> implement composite primary key but it gives me error of duplicate data.
> i have used following query to detect the duplicate rows
> select NID,output_No from tbl_Data
> group by NID,output_No
> having count(*) > 1
> it gives me 2526 duplicate dows. Now i wants to delete the duplicate
> rows what will be the query for deleting the duplicate records.
>
> Thanx
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Try this:
(1) SELECT columnList INTO workTable FROM tableName GROUP BY keyColumns
HAVING COUNT(*) > 1
(2) DELETE tableName FROM workTable WHERE tableName.keyColumns =
workTable.keyColumns
(3) INSERT tableName (columnList) SELECT columnList FROM workTable
(4) DROP workTable
You might want to wrap this in a transaction, but if you don't then a temp
table for the work table is contraindicated because if power goes out
between steps 2 and 3, you will lose the duplicate rows altogether.
If the table were tiny, you could use something like:
SET ROWCOUNT 1
AGAIN:
DELETE tableName FROM (SELECT keyColumns FROM tableName GROUP BY keyColumns
HAVING COUNT(*) > 1) a WHERE tableName.keyColumns = a.keyColumns
IF @.@.ROWCOUNT > 0 GOTO AGAIN
SET ROWCOUNT 0
"Ghulam Farid" <gfaryd@.yahoo.com> wrote in message
news:umVO5durFHA.3444@.TK2MSFTNGP12.phx.gbl...
>
> Hi to All!
> I have a table with 60 columns and more than one million rows. i have to
> implement composite primary key but it gives me error of duplicate data.
> i have used following query to detect the duplicate rows
> select NID,output_No from tbl_Data
> group by NID,output_No
> having count(*) > 1
> it gives me 2526 duplicate dows. Now i wants to delete the duplicate
> rows what will be the query for deleting the duplicate records.
>
> Thanx
>
> *** Sent via Developersdex http://www.examnotes.net ***

Deleting data from a column

What is the command to delete data from certain named colums but
otherwise leave the column intact and also leave the rows and unnamed
columns intact?The following syntax will replace the existing data in the specified columns
with NULL. Without a WHERE clause it will update every row in the table.
If you only want to modify some subset of rows, you'll need to specify the
appropriate WHERE clause.
UPDATE table
SET column_name1 = NULL, column_name2 = NULL, etc.
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
Download the latest version of Books Online from
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
"RocketMan" <ImaChessNut@.gmail.com> wrote in message
news:1181246690.348694.107840@.i38g2000prf.googlegroups.com...
> What is the command to delete data from certain named colums but
> otherwise leave the column intact and also leave the rows and unnamed
> columns intact?
>|||Hi
Its the UPDATE command
e.g
update table set column=null where x=1
see BOL for more info
Regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"RocketMan" <ImaChessNut@.gmail.com> wrote in message
news:1181246690.348694.107840@.i38g2000prf.googlegroups.com...
> What is the command to delete data from certain named colums but
> otherwise leave the column intact and also leave the rows and unnamed
> columns intact?
>

Deleting columns from table

in sql server 2000, I have a table that I have to write a script for to delete several columns in this table. I am finding that I have to use alter or drop keywords or a combination of the two but not sure because I have not done this before. I am googling this but finding all kinds of other information that I dont' need to know.

I dont have rights on this table so I cannot do this manually. I have to create the script and send it on to someone else.

If anyone can provide a good script example that I can use to delete unwanted columns it would be a great thing. Thanks.

You might want to check the scripts section @. SQLServerCentral.com.Here's onethat might work for you.

deleteing columns from a saved fixed width file connection object

How do I delete columns in a fixed width column file connection object, after I've saved it I can't remove columns anymore?

You'll need to reset your columns. See this thread for details ... http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=434333&SiteID=1

Donald

Deleted 99 of 154 columns, yet size of table is still the same?

I had a table with 154 columns. I dropped 99 of them. Each had a lot of
data in them.
But afterwards, when I sp_spaceused TABLENAME, the results in terms of
size were still the same.
Is there something I'm missing?
[ Sugapablo ]
[ http://www.sugapablo.net <--personal | http://www.sugapablo.com <--music ]
[ http://www.2ra.org <--political | http://www.subuse.net <--discuss ]
See DBCC CLEANTABLE in the BOL
"Sugapablo" <russ@.REMOVEsugapablo.com> wrote in message
news:pan.2005.05.24.10.51.15.294163@.REMOVEsugapabl o.com...
> I had a table with 154 columns. I dropped 99 of them. Each had a lot of
> data in them.
> But afterwards, when I sp_spaceused TABLENAME, the results in terms of
> size were still the same.
> Is there something I'm missing?
> --
> [
]
> [ http://www.sugapablo.net <--personal | http://www.sugapablo.com
<--music ]
> [ http://www.2ra.org <--political | http://www.subuse.net
<--discuss ]
>
|||Try running DBCC UPDATEUSAGE and see if that changes anything.
Andrew J. Kelly SQL MVP
"Sugapablo" <russ@.REMOVEsugapablo.com> wrote in message
news:pan.2005.05.24.10.51.15.294163@.REMOVEsugapabl o.com...
>I had a table with 154 columns. I dropped 99 of them. Each had a lot of
> data in them.
> But afterwards, when I sp_spaceused TABLENAME, the results in terms of
> size were still the same.
> Is there something I'm missing?
> --
> [
> ]
> [ http://www.sugapablo.net <--personal | http://www.sugapablo.com
> <--music ]
> [ http://www.2ra.org <--political | http://www.subuse.net
> <--discuss ]
>
|||Hi
Just check for DBCC CLEANTABLE in BOL
http://msdn.microsoft.com/library/en...asp?frame=true
this might help you
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
"Sugapablo" wrote:

> I had a table with 154 columns. I dropped 99 of them. Each had a lot of
> data in them.
> But afterwards, when I sp_spaceused TABLENAME, the results in terms of
> size were still the same.
> Is there something I'm missing?
> --
> [ Sugapablo ]
> [ http://www.sugapablo.net <--personal | http://www.sugapablo.com <--music ]
> [ http://www.2ra.org <--political | http://www.subuse.net <--discuss ]
>

Deleted 99 of 154 columns, yet size of table is still the same?

I had a table with 154 columns. I dropped 99 of them. Each had a lot of
data in them.
But afterwards, when I sp_spaceused TABLENAME, the results in terms of
size were still the same.
Is there something I'm missing?
[ Sugapablo
]
[ http://www.sugapablo.net <--personal | http://www.sugapablo.com <--mu
sic ]
[ http://www.2ra.org <--political | http://www.subuse.net <--di
scuss ]See DBCC CLEANTABLE in the BOL
"Sugapablo" <russ@.REMOVEsugapablo.com> wrote in message
news:pan.2005.05.24.10.51.15.294163@.REMOVEsugapablo.com...
> I had a table with 154 columns. I dropped 99 of them. Each had a lot of
> data in them.
> But afterwards, when I sp_spaceused TABLENAME, the results in terms of
> size were still the same.
> Is there something I'm missing?
> --
> [
]
> [ http://www.sugapablo.net <--personal | http://www.sugapablo.com
<--music ]
> [ http://www.2ra.org <--political | http://www.subuse.net
<--discuss ]
>|||Try running DBCC UPDATEUSAGE and see if that changes anything.
Andrew J. Kelly SQL MVP
"Sugapablo" <russ@.REMOVEsugapablo.com> wrote in message
news:pan.2005.05.24.10.51.15.294163@.REMOVEsugapablo.com...
>I had a table with 154 columns. I dropped 99 of them. Each had a lot of
> data in them.
> But afterwards, when I sp_spaceused TABLENAME, the results in terms of
> size were still the same.
> Is there something I'm missing?
> --
> [
> ]
> [ http://www.sugapablo.net <--personal | http://www.sugapablo.com
> <--music ]
> [ http://www.2ra.org <--political | http://www.subuse.net
> <--discuss ]
>|||Hi
Just check for DBCC CLEANTABLE in BOL
http://msdn.microsoft.com/library/e...asp?frame=true
this might help you
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Sugapablo" wrote:

> I had a table with 154 columns. I dropped 99 of them. Each had a lot of
> data in them.
> But afterwards, when I sp_spaceused TABLENAME, the results in terms of
> size were still the same.
> Is there something I'm missing?
> --
> [ Sugapablo
]
> [ http://www.sugapablo.net <--personal | http://www.sugapablo.com <--
music ]
> [ http://www.2ra.org <--political | http://www.subuse.net <--
discuss ]
>

DELETE where syntax ... need help :)

I have a table with the following columns,

NAME, TYPE, TAG

And there may be 'duplicates' on name and type.

How can I delete them??

I want to delete all with duplicate NAME and TYPEActually I want to delete all rows which is duplicate on NAME and
TYPE.

Name Type Tag
----------
TEST1 12 A
TEST1 12 B
TEST2 12 A
TEST4 14 B

If you take this example, I'd like to delete TEST1 and only have TEST2
and TEST4 left in my table.

This is a temporary table used to compare tables in different
databases.
I move all the tables from both databases into this temp table, and to
find the tables that are found only in on of the databases, I want to
perform the deletion as mentioned above.

The result should give me the tables (occurences) that is missing in
one of the databases. The TAG tells me which.|||Actually I want to delete all rows which is duplicate on NAME and

Quote:

Originally Posted by

TYPE.

You can remove the TAG criteria from the original statement I posted so that
all of the rows with duplicate NAME and TYPE values are removed:

CREATE TABLE dbo.PMTOOLS
(
[NAME] varchar(30) NOT NULL,
[TYPE] varchar(30) NOT NULL,
[TAG] varchar(30) NOT NULL
)
GO

INSERT INTO dbo.PMTOOLS
SELECT 'TEST1', '12', 'A'
UNION ALL SELECT 'TEST1', '12', 'B'
UNION ALL SELECT 'TEST2', '12', 'A'
UNION ALL SELECT 'TEST4', '14', 'B'
GO

DELETE dbo.PMTOOLS
FROM dbo.PMTOOLS
JOIN (
SELECT NAME, TYPE
FROM dbo.PMTOOLS
GROUP BY NAME, TYPE
HAVING COUNT(*) 1
) AS dups
ON
dups.NAME = PMTOOLS.NAME AND
dups.TYPE = PMTOOLS.TYPE

--
Hope this helps.

Dan Guzman
SQL Server MVP

"cobolman" <olafbrungot@.hotmail.comwrote in message
news:1183028881.884401.9280@.n60g2000hse.googlegrou ps.com...

Quote:

Originally Posted by

Actually I want to delete all rows which is duplicate on NAME and
TYPE.
>
Name Type Tag
----------
TEST1 12 A
TEST1 12 B
TEST2 12 A
TEST4 14 B
>
If you take this example, I'd like to delete TEST1 and only have TEST2
and TEST4 left in my table.
>
This is a temporary table used to compare tables in different
databases.
I move all the tables from both databases into this temp table, and to
find the tables that are found only in on of the databases, I want to
perform the deletion as mentioned above.
>
The result should give me the tables (occurences) that is missing in
one of the databases. The TAG tells me which.
>
>
>

|||On Jun 28, 4:50 am, "Dan Guzman" <guzma...@.nospam-
online.sbcglobal.netwrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

Actually I want to delete all rows which is duplicate on NAME and
TYPE.

>
You can remove the TAG criteria from the original statement I posted so that
all of the rows with duplicate NAME and TYPE values are removed:
>
CREATE TABLE dbo.PMTOOLS
(
[NAME] varchar(30) NOT NULL,
[TYPE] varchar(30) NOT NULL,
[TAG] varchar(30) NOT NULL
)
GO
>
INSERT INTO dbo.PMTOOLS
SELECT 'TEST1', '12', 'A'
UNION ALL SELECT 'TEST1', '12', 'B'
UNION ALL SELECT 'TEST2', '12', 'A'
UNION ALL SELECT 'TEST4', '14', 'B'
GO
>
DELETE dbo.PMTOOLS
FROM dbo.PMTOOLS
JOIN (
SELECT NAME, TYPE
FROM dbo.PMTOOLS
GROUP BY NAME, TYPE
HAVING COUNT(*) 1
) AS dups
ON
dups.NAME = PMTOOLS.NAME AND
dups.TYPE = PMTOOLS.TYPE
>
--
Hope this helps.
>
Dan Guzman
SQL Server MVP
>
"cobolman" <olafbrun...@.hotmail.comwrote in message
>
news:1183028881.884401.9280@.n60g2000hse.googlegrou ps.com...
>

Quote:

Originally Posted by

Actually I want to delete all rows which is duplicate on NAME and
TYPE.

>

Quote:

Originally Posted by

Name Type Tag
----------
TEST1 12 A
TEST1 12 B
TEST2 12 A
TEST4 14 B

>

Quote:

Originally Posted by

If you take this example, I'd like to delete TEST1 and only have TEST2
and TEST4 left in my table.

>

Quote:

Originally Posted by

This is a temporary table used to compare tables in different
databases.
I move all the tables from both databases into this temp table, and to
find the tables that are found only in on of the databases, I want to
perform the deletion as mentioned above.

>

Quote:

Originally Posted by

The result should give me the tables (occurence) that is missing in
one of the databases. The TAG tells me which.

cobolman,

I may be reading more into this than I should, but I am assuming you
want to keep one row for each set of dups. Dan's script will remove
all occurrences of the dup rows.

Do you have a sequential unique ID, or timestamp type of column on the
table? Let us know the details (schema) if you do and I'll post a
solution for you.

-- Bill|||Bill,

I do want to remove all occurences of the dup rows.
The result set should only hold the ones that did not have any dups.

Thanks to both (Dan and Bill) :)|||I guess I need help on another one as well, ...

I'd like to do a select to find all the foreign keys of a given table,
and the foreign_key columns.. (Sybase).

This SQL gives me what I want :

Select a.foreign_table_id, a.foreign_key_id, a.primary_table_id,
b.foreign_column_id, b.primary_column_id, c.column_id
from SYS.SYSFOREIGNKEY a
JOIN SYS.SYSFKCOL b ON
a.foreign_table_id = b.foreign_table_id AND
a.foreign_key_id = b.foreign_key_id
where a.foreign_table_id= XXX

But, .. what I'd really like is to instead of the column_id's and
table_id's have the actual name. I can get this from systable and
syscolumn, but I'm not sure how to write the sql|||Hmm...could this be it?

Select a.foreign_table_id, a.foreign_key_id, a.primary_table_id,
c.table_name, b.foreign_column_id,
(select column_name from sys.syscolumn where table_id =
a.foreign_table_id AND column_id = b.foreign_column_id),
b.primary_column_id,
(select column_name from sys.syscolumn where table_id =
a.primary_table_id AND column_id = b.primary_column_id)
from SYS.SYSFOREIGNKEY a
JOIN SYS.SYSFKCOL b ON
a.foreign_table_id = b.foreign_table_id AND
a.foreign_key_id = b.foreign_key_id
JOIN SYS.SYSTABLE c ON
a.primary_table_id = c.table_id
where a.foreign_table_id=XXX|||cobolman (olafbrungot@.hotmail.com) writes:

Quote:

Originally Posted by

I guess I need help on another one as well, ...
>
I'd like to do a select to find all the foreign keys of a given table,
and the foreign_key columns.. (Sybase).

You are probably better off asking in comp.databases.sybase. It does not
seem from your queries that neither Sybase use their old system
tables anymore.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

DELETE records.

Hello,

I have 3 tables with their columns as follows:

+ LabelsInDocs [LabelId] PK FK , [DocsId] PK FK

+ Labels [LabelId] PK , [LabelName]

+ Docs [DocId] PK , [DocUrl]

I set Cascade Delete On so when I delete a Doc all records in
LabelsInDocs will be deleted.

However, when a Doc is deleted I want also to delete all records in
Labels for the labels which do not have any Doc associated to it in
LabelsInDocs.

How can I do this?

Thanks,
Miguel

See: http://www.sqlteam.com/item.asp?ItemID=8595

Hope it helps!

Delete Records Which had some duplciate columns

I had the table with 5 columns and many rows.
Now i want to delete the records which meets column1,column2,column3 are same and keep one record on it.

Create Table T1 (Number int,name1 varchar(25), name2 varchar(25), name3 varchar(25), name4 varchar(25) )

Insert Into T1 values(1,'aaa','bbb','ccc','dd1')
Insert Into T1 values(1,'aaa','bbb','ccc','dd1')
Insert Into T1 values(1,'aaa1','bbb','qqq','ttt')
Insert Into T1 values(2,'www','xxx','yyy','zzz')
Insert Into T1 values(2,'www','xxx','nnn','mmm')
Insert Into T1 values(2,'www2','xxx','nnn','mmm')
Insert Into T1 values(3,'fff','ggg','hhh','iii')
Insert Into T1 values(3,'fff','ggg','rrr','lll')

Result shall be

1-aaa-bbb-ccc-dd1
1-aaa1-bbb-qqq-ttt
2-www-xxx-yyy-zzz
2-www2-xxx-nnn-mmm
3-fff-ggg-hhh-iiii didnt understood u completely, but what i see is that just 1st and 2nd row are same.
so instead of
select * from t1
u can
select distinct * from t1
so then u will have one row instead of two row for first two insert.|||

Quote:

Originally Posted by hisham123

I had the table with 5 columns and many rows.
Now i want to delete the records which meets column1,column2,column3 are same and keep one record on it.

Create Table T1 (Number int,name1 varchar(25), name2 varchar(25), name3 varchar(25), name4 varchar(25) )

Insert Into T1 values(1,'aaa','bbb','ccc','dd1')
Insert Into T1 values(1,'aaa','bbb','ccc','dd1')
Insert Into T1 values(1,'aaa1','bbb','qqq','ttt')
Insert Into T1 values(2,'www','xxx','yyy','zzz')
Insert Into T1 values(2,'www','xxx','nnn','mmm')
Insert Into T1 values(2,'www2','xxx','nnn','mmm')
Insert Into T1 values(3,'fff','ggg','hhh','iii')
Insert Into T1 values(3,'fff','ggg','rrr','lll')

Result shall be

1-aaa-bbb-ccc-dd1
1-aaa1-bbb-qqq-ttt
2-www-xxx-yyy-zzz
2-www2-xxx-nnn-mmm
3-fff-ggg-hhh-iii

i think you have to search before posting, there lot of Querys are here.

Delete Records when you have a primary key of two Columns

Hi everyone.

I have two tables: the catalog table and the detail table.
The two tables are joined by a two-columns key.

I want to make a Delete sentence for delete all the rows in the Catalog table that aren't in the detail table, in SQL Server.

The only problem is that the key is composed by two colums.

If the Key was maded of one column, that would be easy, like this:

DELETE FROM CATALOG
WHERE CATALOGKEY NOT IN (SELECT CATALOGKEY FROM DETAIL)

But it is possible to make a delete sentence if the key has two columns ?

ThanksBut it is possible to make a delete sentence if the key has two columns ?But of course :)

DELETE
FROM CATALOG
WHERE NOT EXISTS
(SELECT *
FROM DETAIL
WHERE DETAIL.CATALOGKEY = CATALOG.CATALOGKEY
AND DETAIL.FIELD2= CATALOG.FIELD2)

hth|||I get a sense that something else is afoot...

Read the sticky at the top of the forum and post what it asks for

Delete partial from column

Guys, i have a table that one of the columns (Email To) is
a concatenated list of email addresses separated by semi colons ";".

i.e.:

rrb7@.yahoo.com;richard.butcher@.sthou.com;administr ator@.sthou.com

etc like that.
each row varies with one exception. administrator@.sthou.com is in each one.

is there a simple way thru sql or T-SQL to delete that "administrator@.sthou.com" part? or should i call each row individually into say, a VB.net form using a split with the deliminator ";"
and then looping thru and updating each row?

thanks again for any easy answer
rikYou should be able to use the Replace function to delete that with an update query. It's available in both Access and T_SQL.|||Jelly Link update the post : It should be UPDATE, not DELETE
UPDATE <table_name>
set EmailTo = replace(EmailTo, 'administrator@.sthou.com;','')
where EmailTo like 'administrator@.sthou.com;%'

UPDATE <table_name>
set EmailTo = replace(EmailTo, ';administrator@.sthou.com;',';')
where EmailTo like '%;administrator@.sthou.com;%'

UPDATE <table_name>
set EmailTo = replace(EmailTo, ';administrator@.sthou.com','')
where EmailTo like '%;administrator@.sthou.com'|||delete <table_name>

delete?? And I'd think the where clause unnecessary, since the OP states that's in every record.|||ups sorry.....its UPDATE :p update...set......where.........

ehm...i use the where clause to remove the separator ";" and the email address when administrator@.sthou.com is at the beginning, in the middle, or even at the end of the list, but not emails which contain administrator@.sthou.com (eg : blablaadministrator@.sthou.com)|||delete?? And I'd think the where clause unnecessary, since the OP states that's in every record.

ups sorry.....its UPDATE :p update...set......where.........

ehm...i use the where clause to remove the separator ";" and the email address when administrator@.sthou.com is at the beginning, in the middle, or even at the end of the list, but not emails which contain administrator@.sthou.com (eg : blablaadministrator@.sthou.com)


Delete instead of update!!!!!!!!!.Stick this post and dont allow the poster to edit again in this post.hehehehe.lets every one see.I can see the panic in jelly link's face that time ,lol|||sorry.....hav so many things in my head :p|||it works great - i truly appreciate the help on this. Working with Oracle for so long, i feel like im running to catch up. it all looks familiar, but really not at all
thanks again
rik