Scripted Delete Rows

Jul 23, 2005

Hi,
I need to delete rows from my user tables dependant upon there non
existence from another table:

delete student
where student_id not in (select student_id from tblStudent)

The reasons is convoluted, simplest explanation is that our operational
system allows the change of business keys. This wreaks havoc in the
data warehouse.
So, I'm look for help on how I can delete rows from tables that have a
column STUDENT_ID. I'd like the script to search for the tables, then
perform the delete.
I don't know where information about user tables are stored, nor how to
loop through the results to do the delete.

Any Ideas are appreciated.

View 2 Replies


ADVERTISEMENT

How To Run Delete Query / Delete Several Rows Just By One Click ?

Feb 16, 2008

I'm using SqlDataSource and an Access database. Let's say I got two tables:user: userID, usernamemessage: userID, messagetextLet's say a user can register on my website, and leave several messages there. I have an admin page where I can select a user and delete all of his messages just by clicking one button.What would be the best (and easiest) way to make this?Here's my suggestion:I have made a "delete query" (with userID as parameter) in MS Access. It deletes all messages of a user when I type in the userID and click ok.Would it be possible to do this on my ASP.net page? If yes, what would the script look like?(yes, it is a newbie question) 

View 2 Replies View Related

Delete Doesn't Delete Rows, But @@ROWCOUNT Says It Did

Aug 20, 2007

I ran the following query in Query Analyzer on a machine running SQL Server 2000. I'm attempting to delete from a linked server running SQL Server 2005:

DELETE FROM sql2005.production.dbo.products
WHERE vendor='Foo'
AND productId NOT IN
(
SELECT productId FROM sql2000.staging.dbo.fooProductList
)

The status message (and @@ROWCOUNT) told me 8 rows were affected, but nothing was actually deleted; when I ran a SELECT with the same criteria as the DELETE, all 8 rows are still there. So, once more I tried the DELETE command. This time it told me 7 rows were affected; when I ran the SELECT again, 5 of the rows were still there. Finally, after running this exact same DELETE query 5 times, I was able to remove all 8 rows. Each time it would tell me that a different number of rows had been deleted, and in no case was that number accurate.

I've never seen anything like this before. Neither of the tables involved were undergoing any other changes. There's no replication going on, or anything else that should introduce any delays. And I run queries like this all day, involving every thinkable combination of 2000 and 2005 servers, that don't give me any trouble.

Does anyone have suggestions on what might cause this sort of behavior?

View 3 Replies View Related

How To Create A Trigger Such That It Can Delete The Rows Whenever Any Other Application Such As Biztalk Had Read The Rows ?

Mar 12, 2007

I had created a trigger which sees that whether a database is updated if it is its copy the values of the updated row into another control table now I want to read the content of control_table into BIzTalk and after reading I want to delete it.Can any one suggest the suitable ay to do this?

View 3 Replies View Related

Transact SQL :: Table Trigger To Delete All Rows Before Inserting Rows

Jul 24, 2015

I have a SQL script to insert data into a table as below:

INSERT into [SRV1INS2].BB.dbo.Agents2
select * from [SRV2INS14].DD.dbo.Agents

I just want to set a Trigger on Agents2 Table, which could delete all rows in the table , before carry out any Insert operation using above statement.I had below Table Trigger on [SRV1INS2].BB.dbo.Agents2 Table as below: But it did not perform what I intend to do.

USE [BB]
GO
/****** Object:  Trigger    Script Date: 24/07/2015 3:41:38 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON

[code]....

View 3 Replies View Related

DataSet Rows Being Deleted, But After The Update , The Sql Database Is Not Updated. The Delete Rows Still In The Database.

Jun 4, 2007

 Stepping thru the code with the debugger shows the dataset rows being deleted.
 
After executing the code, and getting to the page presentation. Then I stop the debug and start the
page creation process again ( Page_Load ).    The database still has the original deleted dataset rows.
Adding rows works, then updating works fine, but deleting rows, does not seem to work.
 
The dataset is configured to send the DataSet updates to the database. Use the standard wizard to create the dataSet.
 
 
cDependChildTA.Fill(cDependChildDs._ClientDependentChild, UserId);        rowCountDb = cDependChildDs._ClientDependentChild.Count;               for (row = 0; row < rowCountDb; row++)        {           dr_dependentChild = cDependChildDs._ClientDependentChild.Rows[0];           dr_dependentChild.Delete();                      //cDependChildDs._ClientDependentChild.Rows.RemoveAt(0);           //cDependChildDs._ClientDependentChild.Rows.Remove(0);            /* update the Client Process Table Adapter*/          // cDependChildTA.Update(cDependChildDs._ClientDependentChild);      //     cDependChildTA.Update(cDependChildDs._ClientDependentChild);        }
        /* zero rows in the DataSet at this point */        /* update the Child  Table Adapter */       cDependChildTA.Update(cDependChildDs._ClientDependentChild);

View 1 Replies View Related

Scripted CREATE INDEX

Mar 13, 2007

I have SQL Server 2005 x64. I found in the studio I can generate allsorts of scripts, including the creation of indexes.I works, however the script contains the CREATE TABLE statements aswell. I want the scripts to recreate the scripts on another filegroup(testing a theory from previous post).Am I missing a switch in the wizard to exclude CREATE TABLE?I did create the script, but then I manually deleted all the CREATETABLE statements, which was a pain.ThanksRob

View 1 Replies View Related

[help] Scripted Database Creation

Jul 13, 2007

Hi all :)My apologies if I posted in the wrong groups, but I just jumpedin MS SQL waters, so any guidance will be appreciated.What I'm trying to do is the following process:[1] present operator with a web page (script)[2] once filled with db name and username, script will create..sql file[3] osql.exe will be called with, i presume, -i file.sql andcreate a databaseI have limited SQL knowledge, but I got the information fromEnterprise Manager when I ran 'All Tasks -Generate SQLScript' on how the .sql file should look like.I realized what are the commands that would create a fresh DB(I ran this for newly created DB), and figured my .php scriptshould create such a file.It's fairly basic, and I'm almost sure all of you know howoutputed .sql file looks like, but anyway here it is:Script is called with parameters 'six' as database name and'magarac' as user name:---------------------------------------------------------------IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name= N'six')DROP DATABASE [six]GOCREATE DATABASE [six] ON (NAME = N'six_Data', FILENAME = N'E:Databasepathsix_Data.MDF' , SIZE = 1, MAXSIZE = 20,FILEGROWTH = 10%) LOG ON (NAME = N'six_Log', FILENAME = N'E:Databasepathsix_Log.LDF' , SIZE = 1, MAXSIZE = 20, FILEGROWTH= 10%)COLLATE SQL_Latin1_General_CP1_CI_ASGOexec sp_dboption N'six', N'autoclose', N'false'GO....use [six]GOif not exists (select * from dbo.sysusers where name =N'guest' and hasdbaccess = 1)EXEC sp_grantdbaccess N'guest'GOif not exists (select * from dbo.sysusers where name =N'sinisam')EXEC sp_grantdbaccess N'magarac', N'magarac'GOexec sp_addrolemember N'db_owner', N'magarac'GO---------------------------------------------------------------I managed to get an exact replica of .sql file that EnterpriseManager created. It leads me to believe that this way ofautomated database creation is indeed possible.Really sorry for making you to go through all this text, butafter I get a green light on this from you guys, I'll have abit more problematic question.Is there any reason why this should not be used, or would fail?Thanks in advance :)P.S.Just as a heads-up, next part of my problem is automatedcreation of new MS SQL server login to use with new DB.

View 3 Replies View Related

Views Scripted In The Wrong Order

Dec 19, 2005

Is there a way to make EM script all views of a database in the order in which they depend on each other?

View 11 Replies View Related

Can A DTS Package Be Scripted Or Transferred To Another Server?

Jul 20, 2005

I have production SQL Server database that must be moved to a newmachine. There is a fairly complex DTS package on the original serverthat is used to handle the weekly updates to the database.Is there a way to export this DTS package in order to set it up on thenew machine as well?Best Regards,Warren WrightScorex Development Team

View 3 Replies View Related

Saving Scripted Output From Enterprise Manager

Aug 14, 2002

Does anyone know where to declare the target directory for scripted output from Enterprise Manager?

Every time I get EM to script Tables I want the output not to go to c:mssql7inn . . but somewhere else. Trouble is I can't find where to set this variable. It won't work in a shortcut - I've tried that, and I can't find an ini file either.

Seems like a simple problem to fix if you just know where to look !

View 1 Replies View Related

Schedule A Scripted Restore On 2005 Express From .bak File.

Oct 28, 2007

Hi i have an web app demo that allows users to add info and change attributes within a SQL 2005 Express DB. I'd like to restore a clean copy of this database every couple of hours from a .bak file using a Windows scheduled task on the server. Has anyone got a .sql script for database restoration that i could use and call using a .cmd script file? Thanks.

View 2 Replies View Related

Scripted Backup/Restore To Remote Server Failing

May 16, 2008

FYI: I've posted this on a couple of forums and haven't gotten any response. I hope someone here can help since this is way past due.

First I'll give a little background on our situation.

Log Shipping and Replication are out, so I am scripting a backup locally, an xcopy to a remote box, and then a restore.

In the early stages of this, I'm trying to do 3 databases. 2 of them work fine alone. It's when I add the 3rd one that I have a problem. I noticed that in the 2nd stored procedure that I probably need to take out the WITH REPLACE if I'm dropping it beforehand as well. I don't have time to test it on this box until later tonight. I don't think that's the issue because it was doing the same thing before I added the drop. I'm overwriting the .txt file so I don't have the exact error that it's giving. I believe it's something similar to "Server: Msg 11, Level 16, State 1, Line 0 General network error. Check your network documentation." I believe it also said [SQLSTATE 42000].

Now for the code. Props to Tara and the code she's put online.

Any help would be appreciated and I'll be glad to help answer questions related to what I've got.

1st Step:
Agent Job scheduled to call stored procedure
EXEC sp_backup_user_dbs3

2nd Step (The code for that stored procedure is):

CREATE PROC sp_backup_user_dbs3
AS
SET nocount ON
DECLARE @Now CHAR(14) -- current date in the form of yyyymmddhhmmss
DECLARE @cmd SYSNAME -- stores the dynamically created DOS command
DECLARE @Result INT -- stores the result of the dir DOS command
DECLARE @RowCnt INT -- stores @@ROWCOUNT
DECLARE @DBName SYSNAME
DECLARE @filename VARCHAR(200) -- stores the path and file name of the BAK file
DECLARE @loglogical VARCHAR(1000)
DECLARE @datalogical VARCHAR(1000)
DECLARE @restoreData VARCHAR(255)
DECLARE @restoreLog VARCHAR(255)
DECLARE @backupFile VARCHAR(255)
DECLARE @physicalNameData VARCHAR(255)
DECLARE @physicalNameLog VARCHAR(255)
DECLARE @physicalNameDataStripped VARCHAR(255)
DECLARE @physicalNameLogStripped VARCHAR(255)
DECLARE @ExecStr NVARCHAR(4000)
DECLARE @strSQL VARCHAR(1000)
DECLARE @restoreToDataDir VARCHAR(255)
DECLARE @restoreToLogDir VARCHAR(255)
DECLARE @path VARCHAR(100)
SET @path = 'I:ackupMoveTo14'

--we need to delete all the old backup files from the I:ackupMoveTo14 folder
-- Build the del command
SELECT @cmd = 'del ' + @path + '*.BAK' + ' /Q /F'
--PRINT @cmd
EXEC master..xp_cmdshell @cmd,
NO_OUTPUT

CREATE TABLE #whichdatabase
(
dbname SYSNAME NOT NULL
)

INSERT
INTO #whichdatabase
(
dbname
)
SELECT [name]
FROM master.dbo.sysdatabases
WHERE [name] IN ( 'db1', 'db2')
ORDER BY [name]
-- Get the database to be backed up
SELECT TOP 1 @DBName = dbname
FROM #whichdatabase
SET @RowCnt = @@ROWCOUNT
-- Iterate throught the temp table until no more databases need to be backed up
WHILE @RowCnt <> 0 BEGIN SELECT @filename = @Path + '' + @DBName + '.BAK' BEGIN backup log @dbname
WITH truncate_only
END

-- Backup the database
BACKUP database @DBName TO disk = @filename

DELETE
FROM #whichdatabase
WHERE dbname = @DBName

-- Get the database to be backed up
SELECT TOP 1 @DBName = dbname
FROM #whichdatabase
SET @RowCnt = @@ROWCOUNT
-- Let the system rest for 5 seconds before starting on the next backup
WAITFOR delay '00:00:05'
END

DROP TABLE #whichdatabase
SET nocount OFF BEGIN
SET @cmd = ''
SET @cmd = 'xcopy I:ackupMoveTo14*.BAK \RemoteServer /C /Y' EXEC master.dbo.xp_cmdshell @cmd
END BEGIN

EXEC [RemoteServer].master..usp_restoreDbsFromDir2
END
RETURN 0 GO


3rd Step(the code for the usp_restoreDbsFromDir2 on the remote server):

CREATE PROCEDURE usp_restoreDbsFromDir2

AS

SET NOCOUNT ON


DECLARE @dbname varchar(255)
DECLARE @loglogical varchar(1000)
DECLARE @datalogical varchar(1000)
DECLARE @physicalName varchar(255)
DECLARE @physicalFileName varchar(255)
DECLARE @restoreData varchar(255)
DECLARE @restoreLog varchar(255)
DECLARE @backupDisk nvarchar (255)
DECLARE @physicalNameData varchar(255)
DECLARE @physicalNameLog varchar(255)
DECLARE @physicalNameDataStripped varchar(255)
DECLARE @physicalNameLogStripped nvarchar (255)
DECLARE @rowCnt int -- @@ROWCOUNT
DECLARE @ExecStr NVARCHAR(4000)
DECLARE @strSQL varchar(1000)
DECLARE @spidstr varchar(8000)
DECLARE @cmd sysname
DECLARE @bkpFile nvarchar(1000)
DECLARE @sql nvarchar(4000)
DECLARE @restoreDir varchar(255)
DECLARE @PhysicalDataPath varchar(255)
DECLARE @PhysicalLogPath varchar(255)


SET @restoreDir = 'F:MSSQLBACKUP'

-- Get files sorted by date
SET @cmd = 'dir ' + @restoreDir + '*.BAK /OD'

CREATE TABLE #Dir
(DirInfo VARCHAR(7000)
) -- Stores the dir results
CREATE TABLE #BackupFiles
(BackupDate varchar(10),
BackupFileName nvarchar(1000)
) -- Stores only the data we want from the dir
CREATE TABLE #RestoreFileListOnly
(
LogicalName nvarchar(128),
PhysicalName nvarchar(260),
Type char(1),
FileGroupName nvarchar(128),
[Size] numeric(20,0),
[MaxSize] numeric(20,0)
)

INSERT INTO #Dir
EXEC master.dbo.xp_cmdshell @cmd

INSERT INTO #BackupFiles
SELECT SUBSTRING(DirInfo, 1, 10), SUBSTRING(DirInfo, LEN(DirInfo) - PATINDEX('% %', REVERSE(DirInfo)) + 2, LEN(DirInfo))
FROM #Dir
WHERE ISDATE(SUBSTRING(DirInfo, 1, 10)) = 1 AND DirInfo NOT LIKE '%<DIR>%'


-- Get the newest file
SELECT TOP 1 @bkpFile = BackupFileName
FROM #BackupFiles
ORDER BY BackupDate DESC

SET @rowCnt = @@ROWCOUNT

-- Iterate throught the table until no more databases need to be backed up
WHILE @RowCnt <> 0
BEGIN

SET @cmd = @restoreDir + @bkpFile

INSERT INTO #RestoreFileListOnly
EXEC('RESTORE FILELISTONLY FROM DISK = ''' + @cmd + '''')

--get the dbname from the bkpFile name
--SET @strSQL = CHARINDEX('_db_', @bkpFile)
--SET @dbname = LEFT(@bkpFile, @strSQL - 1)

SET @strSQL = CHARINDEX('.bak', @bkpFile)
SET @dbname = LEFT(@bkpFile, @strSQL - 1)
--PRINT @dbname
--IF @@ROWCOUNT <> 2
-- RETURN 3

SET @backupDisk = @restoreDir + @bkpFile
SELECT @datalogical = LogicalName
FROM #RestoreFileListOnly
WHERE Type = 'D'
SELECT @loglogical = LogicalName
FROM #RestoreFileListOnly
WHERE Type = 'L'
SELECT @PhysicalDataPath = PhysicalName
FROM #RestoreFileListOnly
WHERE Type = 'D'
SELECT @PhysicalLogPath = PhysicalName
FROM #RestoreFileListOnly
WHERE Type = 'L'


SELECT @strSQL = 'alter database ' + @dbname + ' set offline with rollback immediate'
--alter database MyDatabase set offline with rollback immediate
--PRINT @strSQL
EXEC (@strSQL)

SELECT @strSQL = 'DROP database ' + @dbname
--alter database MyDatabase set offline with rollback immediate
--PRINT @strSQL
EXEC (@strSQL)

--restore the database
SELECT @strSQL = ''
SELECT @strSQL = @strSQL + 'RESTORE DATABASE ' + @dbname + CHAR(10)
SELECT @strSQL = @strSQL + 'FROM DISK = ''' + @backupDisk + '''' + CHAR(10)
SELECT @strSQL = @strSQL + 'WITH' + CHAR(10)
SELECT @strSQL = @strSQL + CHAR(9) + 'REPLACE'
SELECT @strSQL = @strSQL + ',' + CHAR(10)
SELECT @strSQL = @strSQL + CHAR(9) + 'MOVE '''+ @datalogical + ''''+ ' TO '''+ @PhysicalDataPath + ''''
SELECT @strSQL = @strSQL + ',' + CHAR(10)
SELECT @strSQL = @strSQL + CHAR(9) + 'MOVE ''' + @loglogical + '''' + ' TO '''+ @PhysicalLogPath + ''''
--PRINT @strSQL
EXEC (@strSQL)


SELECT @strSQL = 'alter database ' + @dbname + ' set online with rollback immediate'
--alter database MyDatabase set offline with rollback immediate
--PRINT @strSQL
EXEC (@strSQL)

BEGIN
-- Build the del command
SELECT @cmd = 'del ' + @restoreDir + '' + @bkpFile + ' /Q /F'
--PRINT @cmd
-- Delete the file
EXEC master..xp_cmdshell @cmd, NO_OUTPUT

END

--This is supposed to remove the row once done
DELETE FROM #BackupFiles
WHERE @bkpFile = BackupFileName

-- Get the database to be backed up
SELECT TOP 1 @bkpFile = BackupFileName
FROM #BackupFiles
ORDER BY BackupDate DESC

SET @rowCnt = @@ROWCOUNT

--Wait a couple of seconds before starting the next one
WAITFOR delay '00:00:30'

END

Drop TABLE #Dir
Drop TABLE #BackupFiles
Drop TABLE #RestoreFileListOnly

SET NOCOUNT OFF

RETURN 0

GO

View 2 Replies View Related

Delete Many Rows

Mar 17, 2006

Hi All,I am designing a purge process for a db that has grown to almost 200GB.My purge process will remove about 1/3 of the 500 million rows spreadover seven tables. Currently there are about 35 indexes defined onthose seven tables. My question is will there be a performance gain bydropping those indexes, doing my purge, and re-creating the indexes. Iam afraid that leaving those indexes in place will create a lot ofextra overhead in my delete statements by having to maintain theindexes. I know that it could take many hours to rebuild the indexesafterward, but I am planning on doing that anyway. The reason that Iwant to know whether I should drop the indexes ahead of time, is I maynot be able to do the entire purge at once and the tables may need tobe accessed between purges. If this occurs, I will need to have thoseindexes in place.So do I drop the indexes before the purge and re-create them later ordo I leave them in place and re-index them afterward?Thanks In Advancep.h.

View 12 Replies View Related

Delete Rows

Oct 19, 2006

Hi

I am trying to delete rows in temp1 which are exist in temp2

delete from dbo.temp1 as a

inner join temp2 as b

on a.regionname=b.regionname

and a.servicecode=b.servicecode

and a.directioncode=b.directioncode

and a.destorigflag=b.destorigflag

I am getting error :

Incorrect syntax near the keyword 'as'.

thanks in advance

subash

View 3 Replies View Related

Delete Rows Where...

Oct 17, 2007

Hi there,

I have two tables a,b, with the same columns.
How do I delete all the data from a that has the same entry as b?

Thanks for all the answers

View 3 Replies View Related

Transact SQL :: How To Find All Dynamic Scripted Stored Procedure In Database

Jul 2, 2015

Is there any way/Process that we can find out all stored procedures from database where dynamic script has used in script.

View 6 Replies View Related

How Can I Keep Only The Top Ten Rows And Delete The Rest

Dec 17, 2007

I have a table that I would like to only keep the top 10 rows for each username. How can I kep the top 10 and delete the rest?

View 17 Replies View Related

SQL Query - Delete All Rows Bar The Last 10? Help Please!

Jan 6, 2008

Hi,
I'm really not very good at SQL sadly, so would really appreciate any help.
I'm basically working on a website that has a chatroom. I want it so the chatroom table only holds, say 15 lines of chat, then once a new line of chat is entered the last line in the table is deleted, so the table always has a maximum of just 15 rows.
The fields are:
MessageID
Poster
Message
DateTime
I've tried my best, but just don't know how to do it.
I suppose it's something like:
SELECT TOP 15 * FROM Chatroom ORDER BY MessageID DESC - this gives me the last 15 rows.
Then I need a delete statement to delete the rest?! Sorry, I am very bad at SQL, so any help would be great. This is written in a stored procedure.
Can the stored procedure pick up whether there are 15 or more rows in the table, and if so then delete all bar the newest 15 rows of chat?
To summarise: I want a stored procedure that checks if 15 or more rows exist, if they do then delete all bar the newest 15 rows.
Thanks,
Ricky

View 5 Replies View Related

DELETE Rows In MSDE

Sep 6, 2004

Hi..

DELETE FROM table1 WHERE projektID=5


there are 500000 rows that has projektID=5.. and when i run the query the hardrive is working for a couple of minutes and then stops. and NOTHING has happened. not a single row has been deleted?.. cant the DELETE statement handle that many rows or?. or is there another way i can delete these rows?.

View 3 Replies View Related

Delete A Row And All Other Rows Thats Linked To It

Apr 6, 2006

Hii want to delete a row in my database but the problem is, i cant delete it as other table rows is linked to it.  I have to delete all the rows thats linked to the row i wanna delete first.Is there a easier way to delete the row and all the rows thats linked to it?  i wanna code it to do it.an suggestions?

View 1 Replies View Related

Delete Duplicate Rows

Jun 25, 2000

I have a table which looks as follow:

field1 field2 field3 field4 field5 ......
A B C A X ......
A B C B Y ......
A B C C Z ......
A B C A Y ......
. . . . . ......

I want to delete all the rows except one row. Anybody can help?

Thank you very much.

View 3 Replies View Related

Delete Duplicate Rows Using T-SQL

Jan 20, 2000

How do you delete duplicate rows in a table so only one row is left in the table, using T-SQL.

View 1 Replies View Related

DELETE DUPLICATE ROWS

Mar 30, 1999

CAN ANYBODY REPLY FOLLOWING QUESTIONS. I WANT TO DELETE DUPLICATE ROWS
IN MY TABLE WITHOUT USING TRANSACTION TABLE. AND ONE MORE QUESTION HOW
TO GET YESTERDAY DATE BY USING ISQL WINDOW.



THANKS
JK

View 2 Replies View Related

Delete Only 2 Out Of 4 Identical Rows

Jun 19, 2004

I am a beginner in Ms-Sql,so kindly help me with this query:-
Following is the table:-

Name Phone email
John 4564 john@abc.com
John 4564 john@abc.com
John 4564 john@abc.com
John 4564 john@abc.com

How can i manage to delete only 2 rows out of these 4 rows

View 11 Replies View Related

Delete Duplicate Rows

Jan 13, 2005

Suppose that we have the following rows in a MSSQL table :


1administrateur1NULLNULL
2administrateur2NULLNULL
1administrateur1NULLNULL
2administrateur2NULLNULL



How to remove duplicates (leave only the 2 first rows) ?

View 1 Replies View Related

Delete Duplicate Rows

Oct 17, 2005

Hi,
I have the following query to select duplicate rows from the table. How can i delete them with out using temp table.

select UserName, Title, Name, ColWidth, Sequence
from table1 (nolock))
Group by UserName, Title, Name, ColWidth, Sequence
Having count(*) >1


Any help would be greately appreciated.
Thanks

View 2 Replies View Related

Rows Affected By Delete

May 28, 2004

Hello all,

Is there someway to tell how many rows were affected by a delete statement? A variable perhaps?

Any help would be appreciated!

Brian

View 1 Replies View Related

Delete Duplicate ROWS

Jun 18, 2008

How to delete duplicate records from table ? Is there any query for that ?
I am using SQL 2005.

View 3 Replies View Related

Delete Rows From A Table

Jun 20, 2008

Hi,

This is an easy one.

How do delete rows from a table?

I want to delete all the rows from the last month of a table so i have to be able to pass the month parameter to the delete statement.

Help.. plz.. i think this is an easy one

View 17 Replies View Related

Delete Rows From Table

May 16, 2012

In my table I have following current data. I want to delete duplicate records from table.I am trying following SQL statement, So I am getting desired output which is orange color in current data. I want to keep the desired output records and other all should be deleted. But complection is that in current data values in C5 and C6 are different.

select C1,C2,C3,C4,COUNT(*) from table_Delete
group by C1,C2,C3,C4
having COUNT(*) >= 1
ORDER BY COUNT(*) DESC

Current Data:

C1 C2 C3 C4 C5 C6
201 203224 325E 00000999
201 203224 325E 00009999
201 203224 325E 10001999
201 203224 325E 30003999

[code]....

View 3 Replies View Related

Delete Duplicate Rows

Jan 27, 2014

declare @a1 table
(id int not null identity(1,1),
phone decimal(18,0),
adress nvarchar(100))
insert @a1
(phone,adress)

[Code] ....

id phone address
----------- --------------------------------------- ----------------------------------------------------------------------------------------------------
1 111 new york
2 111 new york
3 111 new york
4 222 maxico
5 222 mexico

select phone,count(phone) as say from @a1 group by phone having count(phone)>1

phone say
--------------------------------------- -----------
111 3
222 2

How can I remove duplicate phone. For example

after delete
select*from @a1
select*from @a1

id phone address
----------- --------------------------------------- ----------------------------------------------------------------------------------------------------
1 111 new york
4 222 maxico

I wrote this to show for example in my real table have 50000 rows and 1751 duplicate rows.

[URL] .....

View 2 Replies View Related

How Do You Delete Duplicate Rows......

Jan 18, 2006

How do you delete duplicate rows when there are constraints or like in professional terms as we say "Referential Integrity".

Mr.Madhivanan i hope you remember me. Because it is after all purpose that binds us all together...purpose...it is inevitable Mr.Madhivanan.

View 8 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved