Deleting Rows Based On Nvarchar Data

Apr 15, 2004

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?

View 7 Replies


ADVERTISEMENT

Deleting Mutiple Rows Based On Info

Apr 12, 2007

Is it possible to delete multiple rows from multiple tables based on information specified. Can you write a query that would pull the information if you knew what tables it would need to look in? If anyone know I would greatly appreciate any help I am not sure of this.

Thanks,
Traci

View 1 Replies View Related

T-SQL (SS2K8) :: Deleting Only 1 Row At A Time Instead Of Using Condition And Deleting Many Rows?

Jul 18, 2014

/****** Object: StoredProcedure [dbo].[dbo.ServiceLog] Script Date: 07/18/2014 14:30:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[ServiceLogPurge]

-- Purge records dbo.ServiceLog older than 3 months:
-- Purge records in small portions to avoid locking production tables
-- for a long time. The process takes longer, but can co-exist with
-- normal usage of the tables.

[Code] ...

*** Getting this error below when executing the code ***

Msg 102, Level 15, State 1, Procedure ServiceLogPurge, Line 45
Incorrect syntax near 'Failed:'.

View 9 Replies View Related

SQL Server 2008 :: Deleting Data (rows) From Table To Reclaim Space?

Feb 11, 2015

I have a table 300+GB. it holds 10 years of Data. I need to delete 5 years of data and put it to another server so I can have more space.

If I delete 5 years of data, Transaction log gets so huge and size of the database even gets bigger because of the .ldf file which even gets bigger! I think I can shrink the log file and the data file. Is this the best way to do it?

View 8 Replies View Related

SQL 2012 :: Data Size Of Table Is Not Reduced Even After Deleting Millions Of Rows

Sep 21, 2015

I have deleted nearly 30 million rows from a table. But however when I used the sp_spaceused command to calculate the data occupied by the table I don't see any difference in the data size of the table. In fact the data has increased to few MBs after the deletion, but not much.

View 8 Replies View Related

In A Data Model, How Do I Filter Rows Based On The Identify Of The User???

Jun 21, 2005

Good morning,

View 7 Replies View Related

Dynamiclly Remove Duplicate Rows From Results Table Based On Column Data?

Nov 30, 2007



I have a results table that was created from many different sources in SSIS. I have done calculations and created derived columns in it. I am trying to figure out if there is a way to remove duplicate rows from this table without first writing it to a temp sql table and then parsing through it to remove them.

each row has a like key in a column - I would like to remove like rows keeping specific columns in the resulting row based on the data in this key field.

Ideas?
Thanks,
Ad.

View 7 Replies View Related

How To Combine Multiple Rows Data Into Single Record Or String Based On A Common Field.

Nov 18, 2007

Hellow Folks.
Here is the Original Data in my single SQL 2005 Table:
Department:                                            Sells:
1                                                              Meat
1                                                              Rice
1                                                              Orange
2                                                              Orange
2                                                              Apple
3                                                             Pears
The Data I would like read separated by Semi-colon:
Department:                                            Sells:
1                                                             Meat;Rice;Orange
2                                                             Orange;Apple
3                                                             Pears
I would like to read my data via SP or VStudio 2005 Page . Any help will be appreciated. Thanks..
 
 

View 2 Replies View Related

Deleting Records Based On The Date Difference Using SP

Mar 27, 2008

I am having a table where i have the following columns where the date format is dd/mm/yyyy
Purchase Description        From_Date          To_Date-------------------------------       ---------------         ----------------Desktop                            2/2/2007            2/3/2007Mouse                              2/1/2007            28/1/2007Laptop                              5/1/2008            15/3/2008Speaker                           4/1/2008             21/1/2008
My requirement is i need to create a stored procedure which will look for the from_date and to_date values. If the difference is more than 30 days that record should get deleted automatically. How to write the stored procedure?
Please provide me with full stored procedure
Thanx in advance

View 4 Replies View Related

Deleting Records Based On Date Difference

Mar 27, 2008

I am having a table where i have the following columns where the date format is dd/mm/yyyy

Purchase DescriptionFrom_DateTo_Date
------------------------- --------
Desktop2/2/20072/3/2007
Mouse2/1/200728/1/2007
Laptop5/1/200815/3/2008
Speaker4/1/200821/1/2008

My requirement is i need to create a stored procedure which will look for the from_date and to_date values. If the difference is more than 30 days that record should get deleted automatically. How to write the stored procedure?

Please provide me with full stored procedure

Thanx in advance

C.R.P RAJAN

View 1 Replies View Related

Transact SQL :: Deleting Records Based On Certain Logic

May 7, 2015

There are two seperate jobs,Job A and Job B, which run and insert records in a table. Job A runs first and then Job B runs. The task is to overwrite Job B records if Job A and Job B have same

PID,EDate,CP,RelativePNum,XrefCode,CPD 
PID,EDate,CP,RelativePNum,XrefCode,CPD 

In the above screenshot, row 1 and row 2 have same

So i want to write a query which deletes row 2 records, and keep row 1,row 3 and row 4 records. This i want to do in the whole table.

create
table AB
(
PID
int,
tTimeStamp
datetime,
EDate 
varchar(40),

[Code] ....

View 12 Replies View Related

Compare Values In Consecutive Rows And Print Rows Based On Some Conditions

May 8, 2008

I have the following variables VehicleID, TransactDate, TransactTime, OdometerReading, TransactCity, TransactState.

VehicleID is the unique vehicle ID, OdometerReading is the Odometer Reading, and the others are information related to the transaction time and location of the fuel card (similar to a credit card).

The records will be first grouped and sorted by VehicleID, TransactDate, TransactTime and OdometerReading. Then all records where the Vehicle ID and TransactDate is same for consecutive rows, AND TransactCity or TransactState are different for consecutive rows should be printed.

I also would like to add two derived variables.

1. Miles will be a derived variable that is the difference between consecutive odometer readings for the same Vehicle ID.

2. TimeDiff will be the second derived variable that will categorize the time difference for a particular vehicle on the same day.

My report should look like:

VehID TrDt TrTime TimeDiff Odometer Miles TrCity TrState
1296 1/30/2008 08:22:42 0:00:00 18301 000 Omaha NE
1296 1/30/2008 15:22:46 7:00:04 18560 259 KEARNEY NE

Can someone please help me here?

Thanks,
Romakanta

View 1 Replies View Related

Deleting Rows

Nov 26, 2004

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?

Thanks

View 8 Replies View Related

Need Help With Deleting Rows

Apr 3, 2007

Hi i'm a learner of sql,

i need help in deleting rows from a table that have multiple occurences of a value.

the table has a column named product_id , i need to find out what rows have the product_id value repeated ( i.e not distinct)

how do i do that help please.

View 1 Replies View Related

Deleting Rows

Apr 21, 2008



I have an issue that I don't know how to figure out.



I have a table that has a list of files in it. This table also has GUIDs associated with each file.



Some of these files were found in the emporary internet files folder on the systems scanned.



I want to remove all rows where the file path contains emporary internet files

The problem is that there are other tables where the file GUIDs are referrenced.

So when I try this:
DELETE FROM osScanFile
WHERE (FilePath LIKE '% emporary internet files\%')

I get an error saying that "The DELETE statement conflicted with the REFERENCE constraint "blah blah blah". ...

Basically there are rows in other tables that reference the rows I'm trying to delete in this table.

So how do I get it to delete not only the rows with the search criteria in it but also all rows from all other tables where the rows are linked?

Thanks,
Terry

View 3 Replies View Related

Deleting Duplicate Rows

May 6, 2008

I have a csv file that I need to import daily into a SQL Server 2005 table. Much of the table contents could just be overwritten with the new csv file, however there are a set of Rows within the table that need to be appended to , rather than overwritten. 
There is no Primary Key in the csv file that can be used.  
I'm not sure this is the best approach, but what I have been trying to do, is append the entire csv file to the existing table, and then go back and delete the duplicates.
When I run the Delete, it does delete the majority of the records, but leaves a couple hundred behind. The number left behind varies with each run, can't seem to identify a pattern here. Running the Delete a second time does clean up the rows left behind in the first execution of the Delete, and gives the result I want.
Any thoughts as to why this needs to be run twice? Or is a better approach available?
Here is my code -
SELECT [Pkg ID], [Elm (s)], [Type Name (s)], [End Exec Date], [End Exec Time], dupcount=count(*)
INTO temppkgactions
FROM pkgactions
GROUP BY [Pkg ID], [Elm (s)], [Type Name (s)], [End Exec Date], [End Exec Time]HAVING count(*) > 1
 
DELETE TOP (SELECT COUNT(*) -1 FROM dbo.temppkgactions WHERE dupcount > 1 )
FROM dbo.pkgactions
DROP TABLE temppkgactions
 
Thanks
 
 

View 2 Replies View Related

Deleting Rows From Many Tables

May 19, 2006

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:SqlDataSource ID="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:Parameter Name="UserID" Type="Int32" />
</DeleteParameters>
<SelectParameters>
<asp:ControlParameter ControlID="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!

View 8 Replies View Related

Deleting Duplicate Rows

Feb 11, 1999

Hai

I have problem in deleting duplicate rows. I have a identity column in my table, if I try to use correlatted sub query with Delete command it gives error.

The other problem I have is I have a date column in my table and update that column with current date and time. If use a query to fetch a records on a particular day , it does not return any rows

select * from rates where ch_date >='02/11/99' and ch_date<='02/11/99'

If I use convert also there is some other problems. Is there any way to force date checkings to be done excluding time.



Thanks

View 6 Replies View Related

Deleting Some Of Duplicate Rows

Nov 17, 1998

This is an imaginary problem while discussing ROWID in ORACLE.

Consider a table without primary key, unique key, uniuqe index.
A row has inserted into the table many times.
I want to delete all but one dulicated rows. With any 'where' clause all rows(duplicated)
will be deleted. In ORACLE i can achieve this using ROWID as follows:

Delete from Table_name
where < all column values >
and ROWID <> ( Select max(rowid) from Table_name where < all column values > )

How can this be achieved in MS SQL Server 6.5 ?

According to Dr. Codd's Golden rules for RDBMS one is that
One should be able to reach each data value in the database by using
table name, row idenfication value and column name.

Does MS SQL Server 6.5 satisfy this requirement ?

Also How many of Dr. Codd's 13 Golden Rules for RDBMS does MS SQL Server 6.5
Satisfy? Which doesn't ?

Any discussion about Codd's Rules is welcome.


- Gunvant Patil
gunvantp@yahoo.com

View 1 Replies View Related

Deleting Rows Using A Join

Aug 27, 2001

I have two tables. Table 1 contains a distinct ID(3,4,5). Table 2 contains multiple ID's(3,3,3,3,3,4,4,4,5,5,5). I want to be able to use a join. If an ID is found in table 2, remove all entries of it. Any ideas? Thanks...

delete b.id
from table1 a join table2 b on a.id = b.id

View 1 Replies View Related

Deleting All Rows In A Table

Aug 6, 2004

hello there,
what is the code to Delete all rows in a Table????

thanks in advance

View 4 Replies View Related

Deleting Duplicate Rows

Feb 27, 2014

In my database, I have a table "tbl_c_extract" that consists of 4 columns that look the following. I'm looking at a daily batch of around 4000 records, of which 150 are likely to be duplicates.

Emp_No varchar(255), Proprietary_ID varchar(255), LeaveDateActual datetime
123456, E123456, 2014-09-27 00:00:00.000
213832, E123456, 2099-12-31 00:00:00.000
213836, E123456, 2014-01-31 00:00:00.000

In the example above, I need to remove 2 of the entries, leaving only the one that with the maximum leave date. In this case, those without a leave date have the 2099 entry.

Using CTE works exactly as I want it to, however SQL Server Agent doesn't seem to like the use of CTE..

Code:
WITH CTE (Proprietary_ID, LeaveDate, RN)
AS
(
SELECT Proprietary_ID, LeaveDate,
ROW_NUMBER() OVER(PARTITION BY Proprietary_ID ORDER BY Proprietary_ID, LeaveDate) AS RN
FROM tbl_c_extract
)
DELETE
FROM CTE
WHERE RN > 1

View 2 Replies View Related

Deleting Duplicated Rows

May 23, 2004

Hi,
I have a table named "std_attn", where, by some bad coding, lots of duplicated rows have been created. And the table don't have any PK. So Now tell me the way to remove the duplicaies..................


thnx

View 14 Replies View Related

Need Help Deleting Rows In A Table

Feb 1, 2007

I need to delete some specific rows in Table1

int1 must have the value 1
int2 must have the value 1
int3 must have the value 0
and now we get to the difficult part - first character in the field [Cost No] have to be different from the letter 'B'. [Cost No] have the datatype varchar(20)

I expected the code to look something like this:
DELETE Table1
FROM Table 1
WHERE ([Int1] = 1) AND ([Int2] = 1) AND ([Int3] = 0) AND ((LEFT([Cost No]), 1) <> 'B')

But I get this error message:
The left function requires 2 arguments

What am I doing wrong and what should the right code look like?

View 3 Replies View Related

Urgent - Deleting Duplicate Rows

Apr 5, 2001

Hi folks,

I need to delete the duplicate rows from a table. How to do that in SQL server 7.0 ? If possible write an example, so that it will be much useful for me..

Thanks for ur help..

rgds,
vJ

View 1 Replies View Related

Deleting Table Rows And Optimization

Dec 10, 2001

i have 6 read only tables that every night all the data gets dumped and a new updated copy of the data is copied over. the tables range from 500,000 rows to almost 4 million. i have indexes set up on the fields i use to query against. my questions are
1.since i dump all the data every night and replace it, do i need to rebuild the indexes every night or is that done after the data is reentered?
2.i want to use a fill factor on the table since it is read only, but will dumping the data every night and reinputting it have adverse affects with a fill factor?
3.should i be shrinking the database or defragging it everynight cause of my data dumps and reloads?

thanks in advance

spiral

View 1 Replies View Related

Deleting Thousands Of Rows In A Table

Apr 10, 2003

How can I quickly delete thousands of rows in a table (SQL2000) according a query and without blowing up the log file? For instance executing the query:
Delete from transactions
WHERE transactiondatestamp < DATEADD (m,-4,GETDATE())

increases my log file to almost 6GB before job was done an normal size was re-obtained. In addition it took a long to time to get the job done.
With the command truncate table I cannot use query unfortunately but this would be faster.

Anyone has an idea?

mipo

View 3 Replies View Related

Deleting Duplicate Rows In SQL Table

Nov 16, 2007

I have an SQL tables [Keys] that has various rows such as:
[ID] [Name] [Path] [Customer]
1 Key1 Key1 InHouse
2 Key2 Key2 External
3 Key1 Key1 InHouse
4 Key1 Key1 InHouse
5 Key1 Key1 InHouse

Obviously IDs 1,3,4,5 are all exactly the same and I would like to be left with only:
[ID] [Name] [Path] [Customer]
1 Key1 Key1 InHouse
2 Key2 Key2 External


I cannot create a new table/database or change the unique identifier (which is currently ID) either. I simply need an SQL script I can run to clean out the duplicates (I know how they got there and the issue has been fixed but the Database is still currently invalid due to all these duplicate entires).

Any help would be greatly appreciated.
Thanks,

View 3 Replies View Related

Deleting 8 Million Rows From Database

Jul 16, 2013

i am deleating 8 Million rows from my database,I am wondering how to control T-Log,also I heard something about row lock and table lock

View 4 Replies View Related

Deleting Duplicate Rows In SQL Table

Nov 16, 2007

I have an SQL tables [Keys] that has various rows such as:
[ID] [Name] [Path] [Customer]
1 Key1 Key1 InHouse
2 Key2 Key2 External
3 Key1 Key1 InHouse
4 Key1 Key1 InHouse
5 Key1 Key1 InHouse

Obviously IDs 1,3,4,5 are all exactly the same and I would like to be left with only:


I cannot create a new table/database or change the unique identifier (which is currently ID) either. I simply need an SQL script I can run to clean out the duplicates (I know how they got there and the issue has been fixed but the Database is still currently invalid due to all these duplicate entires).

Any help would be greatly appreciated.
Thanks,

View 10 Replies View Related

Deleting Rows, Halting Server

Dec 11, 2007

Hi,

I've deleted a bunch of rows from my table.. So far about 500k from a 9 million row table.. This table is hit frequently

I think maybe my indexes are a mess? Its so slow every page is timing out .. im now urgently trying to figure something out to resolve ..

ive done UPDATE STATISTICS on the table .. now its probably best I run some command on the indexes directly?

Any help very much appreciated.. !!!

mike123

View 10 Replies View Related

DELETING ROWS With REFERENTIAL INTEGRITY

Dec 7, 2006

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

View 7 Replies View Related

Problem With ID Update After Deleting Rows

Jul 18, 2007

Hi,



I hope some of you can help me with that



I made an application that insert some data to MS SQL 2005 DB Express Edition. One of columns in my database store text. The content of that field must, beyond the existing text, append the current id to its text string. Practically, it means that if on row nr 15 I store text value "15text", on the next row i will store "16text". I figured out that I can get value of max ID by creating following stored procedure.

USE [tracking_db]

GO

/****** Object: StoredProcedure [dbo].[spMaxId] Script Date: 07/18/2007 09:31:44 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[spMaxId]

@maxId integer output

as

SELECT @maxId= MAX(id) FROM FILES_TABLE





Once i get the maxID value i simply concat max id and string like:



string.Concat(max_id.ToString(), file_name);



where "max_id is integer return value from stored procedure and "file_name" is a string to rename like "max_id+file_name"





Two problems occur!!!

Problem nr 1.



Since I use to insert 10 new rows each time, the values from 0-9 are appended to text like "(0-9)text"



Problem nr 2.



If I delete some rows, ID does not get update. It means that after deleting all rows from table, next inserted item gets last existed ID before delting +1. New inserted item should get value 1 since table is empty after deleting all rows from it!!!



Hope some of you has any idea what to do



Muris

View 6 Replies View Related







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