Hi
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?
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 RelatedI 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?
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 RelatedI 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]....
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..
Hi,
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.
Thanks!
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);
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.
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 RelatedHi 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.
Shriram
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 thisone....select * from openquery([LinkedServer], 'delete from NotesTable') wherelastName='Smith'(this doesnt work)Thanks!
View 9 Replies View RelatedI 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 .
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 RelatedHi
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
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
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 RelatedHi,
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
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?.
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.
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 RelatedCAN 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
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
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) ?
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
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
How to delete duplicate records from table ? Is there any query for that ?
I am using SQL 2005.
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
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]....
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] .....
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.
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 Relatedhi.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