Delete A Row And All Other Rows Thats Linked To It

Apr 6, 2006

i 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


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

/****** Object:  Trigger    Script Date: 24/07/2015 3:41:38 PM ******/


View 3 Replies View Related

T-SQL (SS2K8) :: Get Rows From C If Linked Rows In B Contain All Rows In A

Oct 28, 2014

I have 3 tables...

JobRequirements (A)
JobID int
QualificationTypeID int

EmployeeQualifications (B)
EmployeeID int
QualificationTypeID int

Employee (C)
EmployeeID int
EmployeeName int

I need to return a list of all employees fit for a specific job ... The criteria is that only employees who have all the JobRequirements are returned. So if a job had 3 requirements and the employee had just 2 of those qualifications, they would not be returned. Likewise, the employee might have more qualifications than the job requires, but unless the employee has all the specific qualifications the job requires they are not included. If an employee has all the job qualifications plus they have extra qualifications then they should be returned...

How to only return those records where all the child records are present in the other table..

View 5 Replies View Related

Delete Using A Linked Server

Dec 12, 2005


I am trying to delete records in a file on the AS400 using a linked server
and I am getting the following error message:

Server: Msg 7345, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' could not delete from table '"catalog"."schema"."table"'. There was a recoverable, provider-specific error, such as an RPC failure.
ODBC: Msg 0, Level 19, State 1
SqlDumpExceptionHandler: Process 58 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
[OLE/DB provider returned message: Multiple-step operation generated errors. Check each status value.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::DeleteRows returned 0x80040e21: DBROWSTATUS_E_FAIL].

The linked server is configured as follows:
"server type": other data source
"provider name": Microsoft OLE DB provider for ODBC drivers
"data source" references a system DSN name that uses the driver
"Client Access ODBC driver(32 bit)"
linked server options: "Data access" and "Use remote collation" boxes checked
provider options: "Dynamic parameters", "Nested queries", "Allow in process",
"Non transacted updates" boxes checked

The file on the AS400 is journaled and has a unique key.

The Select and Insert statements work like a charm but I can not for the life of me get the Delete or Update statements to work.

I have been struggling with this problem for over a month, so any help whatsoever would be tremendously appreciated.


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

Linked Server - Delete Hangs

Sep 8, 2014

I have a procedure on SQLSERVER DB1 where i insert into few tables on SQL SERVER DB1 and delete a table on remote server SQL SERVER DB2 via linked server. Unfortunately, the delete is taking for ever. Procedure never completes. Data in that table is close to 500 records.

I have delete statement like
Exec(delete linkserv.onedb.stg.tab1)

Later i modified the delete query as below but no luck

DELETE OPENQUERY(linkserv, 'Select * from onedb.stg.tab1')

What can i do here to get this fixed?
Please see linked server properties below
Enable promotion of distributed transactions for RPC:True
Use Remote Collation: True
All other properties : False

How can i debug my SQL Server procedure and find the issue. I came to know that i do not have permission to use debug feature on SSMS.

View 2 Replies View Related

Problem With Delete On Linked Server

May 16, 2006

I am getting time-out errors when I try to perform a simple delete on alinked server. The command is:Delete From MyTable Where PKID=12345I have tried executing this command directly from Query Analyzer, but itjust times out. However, when I run it from QA against the server itself(rather than from my local server against a linked server), it executesimmediately. Similarly, if I run the same SQL command through an ODBC linkedtable in an Access 2000 MDB file (tweaking the syntax slightly), it alsoexecutes immediately. Only if I run it from SQL 7 as a stored procedure or aQA command against a linked server does it time-out.I have no problems inserting rows using any of the three methods notedabove. It is only with deleting rows through the linked server that it timesout.Thanks for any assistance.Neil

View 5 Replies View Related

How To Write A Delete Statement In Linked Server

Oct 6, 2007

Hi all,
We are retriving tables from DB2 and loading into a SQL server. while retriving temporary tables are getting created in the linked server. we need to delete the data in those temporary tables in Linked server. I don't know how to write delete statement for the tables in linked server. any pointers would be appreciated.

Thanks in advance.

View 1 Replies View Related

Delete Sql Syntax For Linked Notes Table

Apr 17, 2006

I am hoping this is a quick easy question for someone! :)I am trying (struggling) with moving data from Sql Server to a LotusNotes table.I am using SQL Server 2000, I have a Lotus Notes linked server (usingNotesSQL), and I wasnt to clear the table (delete all records) and thenreload it from my data on SQL Server.What is the syntax to delete the records?My select statement would be like this:select * from openquery([LinkedServer], 'select * from NotesTable')where lastName='Smith'My delete statement ??? -- cant quite figure out the syntax of * from openquery([LinkedServer], 'delete from NotesTable') wherelastName='Smith'(this doesnt work)Thanks!

View 9 Replies View Related

SQL Server Admin 2014 :: How To Delete A Login Linked With Endpoints

Sep 1, 2015

I am planning to delete a login from SQL logins because he moved out from project .when i try to delete the login , it throws an error saying " The server principal owns an endpoint and cannot be dropped , error 15141 "

Same problem facing on different servers.

Note : Environment is SQL 2012,SQL 2008 including cluster servers .

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


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


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

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

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

View 5 Replies View Related


Sep 6, 2004


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


Mar 30, 1999



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

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 :


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

View 1 Replies View Related

Delete Duplicate Rows

Oct 17, 2005

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.

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!


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


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

Current Data:

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


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

[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 is inevitable Mr.Madhivanan.

View 8 Replies View Related

Scripted Delete Rows

Jul 23, 2005

Hi,I need to delete rows from my user tables dependant upon there nonexistence from another table:delete studentwhere student_id not in (select student_id from tblStudent)The reasons is convoluted, simplest explanation is that our operationalsystem allows the change of business keys. This wreaks havoc in thedata warehouse.So, I'm look for help on how I can delete rows from tables that have acolumn STUDENT_ID. I'd like the script to search for the tables, thenperform the delete.I don't know where information about user tables are stored, nor how toloop through the results to do the delete.Any Ideas are appreciated.

View 2 Replies View Related

Delete Of Mirrored Rows

Oct 6, 2005

hi.I've seen ways to delete duplicate rows.Can someone give me some sql to do this?I have a table with varchar table_name_start, varchar column_name,varchar table_name_end;it has rows like this:table1 col1 table2table1 col2 table 3table2 col1 table1I'd lke to delete the rows if they exist with the names swappedaround, i.e. like above since the first and third share a column nameand the table_name_start/end matches the others table_name_end/start,I'd like to delete one and leave the other.I'm scratching my head trying to figure this out.thanks

View 6 Replies View Related

Copyrights 2005-15, All rights reserved