One DELETE Sql Statement To Delete From Two Tables
Aug 12, 2007I am trying to write one sql statement that deletes from two tables. Is it possible ? If yes, any thoughts ?
View 5 Replies
ADVERTISEMENT
Single Statement To Delete Record Into More Tables
Aug 6, 2004Hi ,
I little question for you ... is it possibile to write a SQL statement to delete records in several tables at the same time?
For example if I've two tables involved by join
DELETE <...> from Customers A
INNER JOIN CustomerProperties B ON A.CustomerID=B.CustomerID
I Must use two statement to remove records from both the tables?
Thx
I Use SQL 2000, Can You Use One Delete Query To Delete 2 Tables?
Nov 26, 2007this is my Delete Query NO 1
alter table ZT_Master disable trigger All
Delete ZT_Master WHERE TDateTime> = DATEADD(month,DATEDIFF(month,0,getdate())-(select Keepmonths from ZT_KeepMonths where id =1),0) AND TDateTime< DATEADD(month,DATEDIFF(month,0,getdate()),0)
alter table ZT_Master enable trigger All
I have troble in Delete Query No 2
here is a select statemnt , I need to delete them
select d.* from ZT_Master m, ZT_Detail d where (m.Prikey=d.MasterKey) And m.TDateTime> = DATEADD(month,DATEDIFF(month,0,getdate())-(select Keepmonths from ZT_KeepMonths where id =1),0) AND m.TDateTime< DATEADD(month,DATEDIFF(month,0,getdate()),0)
I tried modified it as below
delete d.* from ZT_Master m, ZT_Detail d where (m.Prikey=d.MasterKey) And m.TDateTime> = DATEADD(month,DATEDIFF(month,0,getdate())-(select Keepmonths from ZT_KeepMonths where id =1),0) AND m.TDateTime< DATEADD(month,DATEDIFF(month,0,getdate()),0)
but this doesn't works..
can you please help?
and can I combine these 2 SQL Query into one Sql Query? thank you
SQL Server 2008 :: Maintenance Plan Delete History Trying To Delete Wrong Files
Sep 11, 2015I have some simple files but they are failing because the delete history task is failing as it is looking for files in a non existent directory.
It is looking for files in C:Program FilesMicrosoft SQL ServerMSSQL10_50.INSTANCEMSSQLLog whereas it should be looking in C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLLog
how I can get this corrected so I can get the Maintenance Plans to run correctly.
I have tried deleting and recreating the Plan but to no avail
Master Data Services :: Hard Delete All Soft Delete Records (members) In Database
May 19, 2012I am using Master Data Service for couple of months now. I can load, update, merge and soft delete data in MDS. Occasionally we even have to hard delete data from MDS. If we keep on soft deleting records in a MDS table eventually there will be huge number of soft deleted records. Is there an easy way to hard delete all the soft deleted records from all MDS tables in a specific Model.
View 18 Replies View RelatedCopy And Delete Table With Foreign Key References(...,...) On Delete Cascade?
Oct 23, 2004Hello:
Need some serious help with this one...
Background:
Am working on completing an ORM that can not only handles CRUD actions -- but that can also updates the structure of a table transparently when the class defs change. Reason for this is that I can't get the SQL scripts that would work for updating a software on SqlServer to be portable to other DBMS systems. Doing it by code, rather than SQL batch has a chance of making cross-platform, updateable, software...
Anyway, because it needs to be cross-DBMS capable, the constraints are that the system used must work for the lowest common denominator....ie, a 'recipe' of steps that will work on all DBMS's.
The Problem:
There might be simpler ways to do this with SqlServer (all ears :-) - just in case I can't make it cross platform right now) but, with simplistic DBMS's (SqlLite, etc) there is no way to ALTER table once formed: one has to COPY the Table to a new TMP name, adding a Column in the process, then delete the original, then rename the TMP to the original name.
This appears possible in SqlServer too --...as long as there are no CASCADE operations.
Truncate table doesn't seem to be the solution, nor drop, as they all seem to trigger a Cascade delete in the Foreign Table.
So -- please correct me if I am wrong here -- it appears that the operations would be
along the lines of:
a) Remove the Foreign Key references
b) Copy the table structure, and make a new temp table, adding the column
c) Copy the data over
d) Add the FK relations, that used to be in the first table, to the new table
e) Delete the original
f) Done?
The questions are:
a) How does one alter a table to REMOVE the Foreign Key References part, if it has no 'name'.
b) Anyone know of a good clean way to get, and save these constraints to reapply them to the new table. Hopefully with some cross platform ADO.NET solution? GetSchema etc appears to me to be very dbms dependant?
c) ANY and all tips on things I might run into later that I have not mentioned, are also greatly appreciated.
Thanks!
Sky
SQL - Cascading Delete, Or Delete Trigger, Maintaining Referential Integrity - PLEASE HELP ME!!!
Nov 13, 2006I am having great difficulty with cascading deletes, delete triggers and referential integrity.
The database is in First Normal Form.
I have some tables that are child tables with two foreign keyes to two different parent tables, for example:
Table A
/
Table B Table C
/
Table D
So if I try to turn on cascading deletes for A/B, A/C, B/D and C/D relationships, I get an error that I cannot have cascading delete because it would create multiple cascade paths. I do understand why this is happening. If I delete a row in Table A, I want it to delete child rows in Table B and table C, and then child rows in table D as well. But if I delete a row in Table C, I want it to delete child rows in Table D, and if I delete a row in Table B, I want it to also delete child rows in Table D.
SQL sees this as cyclical, because if I delete a row in table A, both table B and table C would try to delete their child rows in table D.
Ok, so I thought, no biggie, I'll just use delete triggers. So I created delete triggers that will delete child rows in table B and table C when deleting a row in table A. Then I created triggers in both Table B and Table C that would delete child rows in Table D.
When I try to delete a row in table A, B or C, I get the error "Delete Statement Conflicted with COLUMN REFERENCE". This does not make sense to me, can anyone explain? I have a trigger in place that should be deleting the child rows before it attempts to delete the parent row...isn't that the whole point of delete triggers?????
This is an example of my delete trigger:
CREATE TRIGGER [DeleteA] ON A
FOR DELETE
AS
Delete from B where MeetingID = ID;
Delete from C where MeetingID = ID;
And then Table B and C both have delete triggers to delete child rows in table D. But it never gets to that point, none of the triggers execute because the above error happens first.
So if I then go into the relationships, and deselect the option for "Enforce relationship for INSERTs and UPDATEs" these triggers all work just fine. Only problem is that now I have no referential integrity and I can simply create unrestrained child rows that do not reference actual foreign keys in the parent table.
So the question is, how do I maintain referential integrity and also have the database delete child rows, keeping in mind that the cascading deletes will not work because of the multiple cascade paths (which are certainly required).
Hope this makes sense...
Thanks,
Josh
Delete Syntax To Delete A Record From One Table If A Matching Value Isn't Found In Another
Nov 17, 2006I'm trying to clean up a database design and I'm in a situation to where two tables need a FK but since it didn't exist before there are orphaned records.
Tables are:
Brokers and it's PK is BID
The 2nd table is Broker_Rates which also has a BID table.
I'm trying to figure out a t-sql statement that will parse through all the recrods in the Broker_Rates table and delete the record if there isn't a match for the BID record in the brokers table.
I know this isn't correct syntax but should hopefully clear up what I'm asking
DELETE FROM Broker_Rates
WHERE (Broker_Rates.BID <> Broker.BID)
Thanks
Help With Delete Statement/converting This Select Statement.
Aug 10, 2006I have 3 tables, with this relation:
tblChats.WebsiteID = tblWebsite.ID
tblWebsite.AccountID = tblAccount.ID
I need to delete rows within tblChats where tblChats.StartTime - GETDATE() < 180 and where they are apart of @AccountID. I have this select statement that works fine, but I am having trouble converting it to a delete statement:
SELECT * FROM tblChats c
LEFT JOIN tblWebsites sites ON sites.ID = c.WebsiteID
LEFT JOIN tblAccounts accounts on accounts.ID = sites.AccountID
WHERE accounts.ID = 16 AND GETDATE() - c.StartTime > 180
Solution!-Create Access/Jet DB, Tables, Delete Tables, Compact Database
Feb 5, 2007From Newbie to Newbie,
Add reference to:
'Microsoft ActiveX Data Objects 2.8 Library
'Microsoft ADO Ext.2.8 for DDL and Security
'Microsoft Jet and Replication Objects 2.6 Library
--------------------------------------------------------
Imports System.IO
Imports System.IO.File
Code Snippet
'BACKUP DATABASE
Public Shared Sub Restart()
End Sub
'You have to have a BackUps folder included into your release!
Private Sub BackUpDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BackUpDB.Click
Dim addtimestamp As String
Dim f As String
Dim z As String
Dim g As String
Dim Dialogbox1 As New Backupinfo
addtimestamp = Format(Now(), "_MMddyy_HHmm")
z = "C:Program FilesVSoftAppMissNewAppDB.mdb"
g = addtimestamp + ".mdb"
'Add timestamp and .mdb endging to NewAppDB
f = "C:Program FilesVSoftAppMissBackUpsNewAppDB" & g & ""
Try
File.Copy(z, f)
Catch ex As System.Exception
System.Windows.Forms.MessageBox.Show(ex.Message)
End Try
MsgBox("Backup completed succesfully.")
If Dialogbox1.ShowDialog = Windows.Forms.DialogResult.OK Then
End If
End Sub
Code Snippet
'RESTORE DATABASE
Private Sub RestoreDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles
RestoreDB.Click
Dim Filename As String
Dim Restart1 As New RestoreRestart
Dim overwrite As Boolean
overwrite = True
Dim xi As String
With OpenFileDialog1
.Filter = "Database files (*.mdb)|*.mdb|" & "All files|*.*"
If .ShowDialog() = Windows.Forms.DialogResult.OK Then
Filename = .FileName
'Strips restored database from the timestamp
xi = "C:Program FilesVSoftAppMissNewAppDB.mdb"
File.Copy(Filename, xi, overwrite)
End If
End With
'Notify user
MsgBox("Data restored successfully")
Restart()
If Restart1.ShowDialog = Windows.Forms.DialogResult.OK Then
Application.Restart()
End If
End Sub
Code Snippet
'CREATE NEW DATABASE
Private Sub CreateNewDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles
CreateNewDB.Click
Dim L As New DatabaseEraseWarning
Dim Cat As ADOX.Catalog
Cat = New ADOX.Catalog
Dim Restart2 As New NewDBRestart
If File.Exists("C:Program FilesVSoftAppMissNewAppDB.mdb") Then
If L.ShowDialog() = Windows.Forms.DialogResult.Cancel Then
Exit Sub
Else
File.Delete("C:Program FilesVSoftAppMissNewAppDB.mdb")
End If
End If
Cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Program FilesVSoftAppMissNewAppDB.mdb;
Jet OLEDB:Engine Type=5")
Dim Cn As ADODB.Connection
'Dim Cat As ADOX.Catalog
Dim Tablename As ADOX.Table
'Taylor these according to your need - add so many column as you need.
Dim col As ADOX.Column = New ADOX.Column
Dim col1 As ADOX.Column = New ADOX.Column
Dim col2 As ADOX.Column = New ADOX.Column
Dim col3 As ADOX.Column = New ADOX.Column
Dim col4 As ADOX.Column = New ADOX.Column
Dim col5 As ADOX.Column = New ADOX.Column
Dim col6 As ADOX.Column = New ADOX.Column
Dim col7 As ADOX.Column = New ADOX.Column
Dim col8 As ADOX.Column = New ADOX.Column
Cn = New ADODB.Connection
Cat = New ADOX.Catalog
Tablename = New ADOX.Table
'Open the connection
Cn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Program FilesVSoftAppMissNewAppDB.mdb;Jet
OLEDB:Engine Type=5")
'Open the Catalog
Cat.ActiveConnection = Cn
'Create the table (you can name it anyway you want)
Tablename.Name = "Table1"
'Taylor according to your need - add so many column as you need. Watch for the DataType!
col.Name = "ID"
col.Type = ADOX.DataTypeEnum.adInteger
col1.Name = "MA"
col1.Type = ADOX.DataTypeEnum.adInteger
col1.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col2.Name = "FName"
col2.Type = ADOX.DataTypeEnum.adVarWChar
col2.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col3.Name = "LName"
col3.Type = ADOX.DataTypeEnum.adVarWChar
col3.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col4.Name = "DOB"
col4.Type = ADOX.DataTypeEnum.adDate
col4.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col5.Name = "Gender"
col5.Type = ADOX.DataTypeEnum.adVarWChar
col5.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col6.Name = "Phone1"
col6.Type = ADOX.DataTypeEnum.adVarWChar
col6.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col7.Name = "Phone2"
col7.Type = ADOX.DataTypeEnum.adVarWChar
col7.Attributes = ADOX.ColumnAttributesEnum.adColNullable
col8.Name = "Notes"
col8.Type = ADOX.DataTypeEnum.adVarWChar
col8.Attributes = ADOX.ColumnAttributesEnum.adColNullable
Tablename.Keys.Append("PrimaryKey", ADOX.KeyTypeEnum.adKeyPrimary, "ID")
'You have to append all your columns you have created above
Tablename.Columns.Append(col)
Tablename.Columns.Append(col1)
Tablename.Columns.Append(col2)
Tablename.Columns.Append(col3)
Tablename.Columns.Append(col4)
Tablename.Columns.Append(col5)
Tablename.Columns.Append(col6)
Tablename.Columns.Append(col7)
Tablename.Columns.Append(col8)
'Append the newly created table to the Tables Collection
Cat.Tables.Append(Tablename)
'User notification )
MsgBox("A new empty database was created successfully")
'clean up objects
Tablename = Nothing
Cat = Nothing
Cn.Close()
Cn = Nothing
'Restart application
If Restart2.ShowDialog() = Windows.Forms.DialogResult.OK Then
Application.Restart()
End If
End Sub
Code Snippet
'COMPACT DATABASE
Private Sub CompactDB_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles
CompactDB.Click
Dim JRO As JRO.JetEngine
JRO = New JRO.JetEngine
'The first source is the original, the second is the compacted database under an other name.
JRO.CompactDatabase("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:Program
FilesVSoftAppMissNewAppDB.mdb; Jet OLEDB:Engine Type=5", "Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=C:Program FilesVSoftAppMissNewAppDBComp.mdb; JetOLEDB:Engine Type=5")
'Original (not compacted database is deleted)
File.Delete("C:Program FilesVSoftAppMissNewAppDB.mdb")
'Compacted database is renamed to the original databas's neme.
Rename("C:Program FilesVSoftAppMissNewAppDBComp.mdb", "C:Program FilesVSoftAppMissNewAppDB.mdb")
'User notification
MsgBox("The database was compacted successfully")
End Sub
End Class
Delete Statement
Apr 11, 2007Hi,I would like to delete a record from a table on the condition that a corresponding ID is located in another table, ie. deleting an email message if the user ID is listed as a recipient in a recipient table etc. Here is my SQL statement: DELETE FROM id_email_message WHERE (id_message IN (SELECT id_message FROM recipients
WHERE id_user = 324) AND message.id_message_status = 2) OR (id_message IN
(SELECT id_message FROM message WHERE id_owner = 324 and id_message_status = 2)) The problem is the multiple select statements paired with the delete statement is too much overhead for the server and I always get a timeout server error (at least that's what I'm guessing, the error page and tracing isn't much helpful). Is there a more efficient way to do this?Thanks.Eitan
Help With DELETE Statement
Nov 13, 2007Hello there... I'm creating a ASP.net Web Application and storing all my data in SQL database.I'm trying to create a Stored Procedure to Delete info from a table:- I have 3 tables: A, B and C: A's primary key is aID and has no foreign key B's primary key is bID and has 1 foreign key: aID (linking this table to table A); C's primary key is cID and has 1 foreign key: bID (linking this table to table B);- As you can see, all 3 tables are linked (A to B and B to C)I want to be able to DELETE all info from C only by giving aID, is this possible in SQL? I can retrieve the info easily through a SELECT statement and using an INNER JOIN (*). I also know how to do solve this by coding it in C# but I'd have to create some unnecessary variables and more than 1 Stored Procedure when it's probably possible to do all in one!* SELECT cID FROM C INNER JOIN B ON B.bID = C.bIDWHERE A.aID= whateverIf anyone knows the solution to my problem, please don't hesitate :p Thanks,SuperJB
View 2 Replies View RelatedSql Delete Statement
Sep 15, 2004hi, i want to delete some records from my table if there is more then 150 records (it should always be max 150records, it can be less and then it shouldent delete anything),
so when it goes over to 151 records i want to delete the oldest record, so i get the new record + 149 old records, is there a simple way to do this?
DELETE Statement
Dec 14, 2001When does the DELETE statement physically deletes the records? For example, if I execute the DELETE statement and in the middle of the execution I understand that it is wrong. What will happen if I stop it? Will it delete the records partially? I think the deletion happens when the full statement is done but need an expert answer. Thank you.
View 7 Replies View RelatedSQL Delete Statement
Oct 2, 2006Hi what do i need to add to this stmt to delete the result ?
---------------
select ct_cust1_text01,ct_address,ct_cust1_text09,count(*)from TABLE_NAME group by ct_cust1_text01,ct_address,ct_cust1_text09 having count(*) > 1
---------
i have tried delete * from TABLE_NAME where (select...)
not great at SQL appreciate any help...
How To Run Delete Query / Delete Several Rows Just By One Click ?
Feb 16, 2008I'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 RelatedAllow Single Row Delete From A Table But Not Bulk Delete
Sep 16, 2013The requirement is: I should allow single row delete from a table but not bulk delete. An audit table should get updated if there is any single delete or single update. So I wrote the triggers as follows: for single and bulk delete
ALTER TRIGGER [dbo].[TRG_Delete_Bulk_tbl_attendance]
ON [dbo].[tbl_attendance]
AFTER DELETE
AS
[code]...
When I try to run the website, the database error I am getting is:Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1.
Delete Doesn't Delete Rows, But @@ROWCOUNT Says It Did
Aug 20, 2007I 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?
Cannot Amend Or Delete Subscription And Cannot Delete Report.
Nov 20, 2007
Hi,
I have a problem with one report on my server. A user has requested that I exclude him from receiving a timed email subscription to several reports. I was able to amend all the subscriptions except one. When I try to remove his email address from the subscription I receive this error:
An internal error occurred on the report server. See the error log for more details. (rsInternalError) Get Online Help
For more information about this error navigate to the report server on the local server machine, or enable remote errors
Online no help couldn't offer any advice at all, so I thought I'd just delete the subscription and recreate it again, but I receive the same message. "Okay, no problem, I'll just delete the report and redeploy it and set up the subscription so all the other users aren't affected", says I. "Oh, no!", says the report server, and then it give me this message:
System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.Data.SqlClient.SqlException: Only members of sysadmin role are allowed to update or delete jobs owned by a different login. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at Microsoft.ReportingServices.Library.InstrumentedSqlCommand.ExecuteNonQuery() at Microsoft.ReportingServices.Library.DBInterface.DeleteObject(String objectName) at Microsoft.ReportingServices.Library.RSService._DeleteItem(String item) at Microsoft.ReportingServices.Library.RSService.ExecuteBatch(Guid batchId) at Microsoft.ReportingServices.WebServer.ReportingService2005.ExecuteBatch() --- End of inner exception stack trace ---
What's even weirder is that I'm the owner and creator of the report and I'm a system admin and content manager on the report server and I set up the subscription when the report was initially deployed. Surely I should have sufficient rights to fart around with this subscription/report as I see fit?
I have rebooted the server, redeployed the report, checked credentials on the data source and tried amending and deleting from both the report manager and management studio but still I am prevented from doing so.
Any help would be much appreciated.
Thanks in advance,
Paul
DELETE Statement Conflicted
Dec 6, 2006Hello
I am trying to delete a row from one table and I expected it to also be removed from the subsequent child tables, linked via foreign and primary keys.
However, when I tried to delete a row in the first table I saw this error:
DELETE FROM [dbo].[Names_DB]WHERE [LName_Name]=N'andrews'
Error: Query(1/1) DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_LName_Name'. The conflict occurred in database 'MainDB', table 'Category_A', column 'LName_Name'.
I went to the very last table in the sequence and I was able to delete the row without problems, but it did not effect any of the other tables.
Please advise.
I need to make many changes in these tables, should I use a trigger instead, if so what is the code to trigger each table? I am new to triggers.
Thanks
Regards
Lynn
Trigger On Delete Statement
Sep 26, 2007Hi! I am deleting some records from the database and at the same time inserting them into a new table. I am doing all this with sql querries. How can that be done with triggers. Basically on the delete, i'd like to insert the affected records. Thanks!
James
Only One Of My SQL Delete Statement Are Firing
Mar 12, 2006Hey guys...
I am trying to tidy up my code a bit and have one SQL command (Sub class) to call when needing to insert, update, delete and select.
I have got one class I am testing with that delete from a table support_ticket and then calls RunSQL() again to delete the corresponding tickets in Support_Reply.
however it only seems to want to delete from one table at a time...as i commented out the first sql and it worked and the second fires...but if the first one is active it doesnt fire.
Do anyone on the forum know why this has happened?
Sub DeleteUserTicket(sender as Object, e as EventArgs)
Dim strSQL1 = "DELETE FROM Support_Ticket WHERE (TicketID = " & txtticketID & ")"
RunSQL(strSQL1)
strSQL1 = "DELETE FROM Support_Reply WHERE (TicketID = " & txtticketID & ")"
RunSQL(strSQL1)
End Sub
'One class to run the sql statements for the entire page this will reduce in repetitve code as well as reduce code size
Sub RunSQL(strSQL)
Dim objCmd As SqlCommand
Dim sqlConn = New SqlConnection(strConn)
objCmd = New SQLCommand(strSQL, sqlConn)
objCmd.Connection.Open()
'Check that the rows can be deleted if not then return a error.
Try
objCmd.ExecuteNonQuery()
response.redirect("ticketsystemtest2.aspx")
Message.InnerHtml = "<b>Ticket " & txtticketID & " Closed</b> <br/>"
Catch ex As SqlException
If ex.Number = 2627 Then
Message.InnerHtml = "ERROR: A record already exists with " _
& "the same primary key"
Else
Message.InnerHtml = "ERROR: Could not update record, please " _
& "ensure the fields are correctly filled out <br>" & ex.Message & " " & ex.Number
Message.Style("color") = "red"
End If
End Try
objCmd.Connection.Close()
sqlConn = nothing
objcmd = nothing
End Sub
cheers
Recover After Delete Statement
Sep 6, 2001One of our developers just deleted a ton of records- is there any way we can recover this data? (we can't use a backup since a ton of changes were made since we last backed up)
Thanks-
Jack
Delete Statement Won't Work!!
Feb 1, 2001i am having problem running a simple delete statement against a table. it just hangs is there anything i should look at? the table has 4 primary keys and the index makes up of the 4 keys and ideas?
i viewed the delete statement with the execusion plan and this is what i saw.
delete -> index delete/delete -> sorting the input -> table delete/delete -> Top -> Index scan.
Delete Statement Very Slow
Dec 12, 2005Hi,
I've got a table with about 500 000 records and growing monthly by about 40 000 records
When I perform the following query:
DELETE from [myTable] WHERE Month = '07' AND Year='2005'
This query will take about 10 minutes to execute. Columns Month & Year are both indexed.
Surely MSSQL can't be this slow on only 500 000 records.
Must I do some other database optimization ???
Thanks
Please Help Me With A Complex DELETE Statement
Jan 27, 2005Hello, currently I have a query like this:
PHP Code:
SELECT *
FROM relations INNER JOIN
paths ON relations.path = paths.path_id
WHERE
(paths.links = '161') AND (relations.node1 = 162) OR
(paths.links = '161') AND (relations.node2 = 162) OR
(paths.links = '162') AND (relations.node1 = 161) OR
(paths.links = '162') AND (relations.node2 = 161) OR
(paths.links LIKE '162%') AND (relations.node1 = 161) OR
(paths.links LIKE '%162') AND (relations.node2 = 161) OR
(paths.links LIKE '161%') AND (relations.node1 = 162) OR
(paths.links LIKE '%161') AND (relations.node2 = 162) OR
(paths.links LIKE '%161;162%') OR
(paths.links LIKE '%162;161%')
ORDER BY relations.node1
Don't pay attention to the 161 and 162 things, is just test data, now my problem is that I want to transform that into a DELETE statement, but I can't find the right way to do it, so far I managed to do something like:
PHP Code:
DELETE relations
FROM relations INNER JOIN
paths ON relations.path = paths.path_id
WHERE
(paths.links = '161') AND (relations.node1 = 162) OR
(paths.links = '161') AND (relations.node2 = 162) OR
(paths.links = '162') AND (relations.node1 = 161) OR
(paths.links = '162') AND (relations.node2 = 161) OR
(paths.links LIKE '162%') AND (relations.node1 = 161) OR
(paths.links LIKE '%162') AND (relations.node2 = 161) OR
(paths.links LIKE '161%') AND (relations.node1 = 162) OR
(paths.links LIKE '%161') AND (relations.node2 = 162) OR
(paths.links LIKE '%161;162%') OR
(paths.links LIKE '%162;161%')
But that would delete only from the relations table and not from the paths table. I need to delete from both tables.
Can anyone help me please? Its kinda urgent.
Thansk!
Tuning A Delete Statement
Oct 26, 2004Hi,
I need to delete the following records (from enrollment_fact):
SELECT
a."STU_SID",
a."SCHOOL_YEAR",
a."DATE_SID",
a."LOC_SID"
FROM "dbo"."ENROLLMENT_FEX2" b,
"dbo"."LOCATION_DIM" c
LEFT OUTER JOIN
"dbo"."ENROLLMENT_FACT" a
on c."LOC_SID" = a."LOC_SID"
WHERE
b."LOC_KEY" = c."LOC_KEY"
and
a."DATE_SID" between b."MIN_DATE" and b."MAX_DATE"
This is the approach (excuse the misuse of the concat function, but you get the idea)
DELETE FROM "dbo"."ENROLLMENT_FACT"
WHERE CONCAT (a."STU_SID", a."SCHOOL_YEAR", a."DATE_SID", a."LOC_SID")
IN (
SELECT DISTINCT CONCAT (a."STU_SID",
a."SCHOOL_YEAR",
a."DATE_SID",
a."LOC_SID"
)
FROM "dbo"."ENROLLMENT_FEX2" b,
"dbo"."LOCATION_DIM" c
LEFT OUTER JOIN "dbo"."ENROLLMENT_FACT" a
ON c."LOC_SID" = a."LOC_SID"
AND a."DATE_SID" BETWEEN b."MIN_DATE"
AND b."MAX_DATE")
comments? better way? (without using an sp)
thanks
Delete Statement Using A Join Plz Help
Mar 2, 2005well i have 2 table one name detcom and another entcom stored in DB1 the key for both to join on is lets say A, B, C . I need to check if there are records based on the key A, B, C of both table where C EQUALS to '80_300_113' and if there are delete them and then grab data from another
database named DB2 on same server (same instance) wich contains the same tables entcom and detcom and insert all the data from those tables into the same tables in DB1 based on the key and where C = '80_300_113'
PLZ help
Question About Delete Statement
Nov 4, 2007Hi,My question is :
CREATE TABLE t1
(c1 INTEGER,
c2 INTEGER,
c3 DECIMAL(15,0 ))
INSERT INTO t1 VALUES (1, 2, 3.0)
How can i define a SQL commend will cause C1 to be decremented each time a row is deleted from the T2 table?
Thanks !
Very Tricky Delete Statement
Apr 29, 2008I have a rather tricky SQL delete query I’m trying to perform, preferably without cursors. I have the following data set, where I want each of the first changes to the status.
IDStatus
1Good
2So-So
3So-So
4Bad
5Bad
6Bad
7Bad
8Bad
9So-So
10Good
So given the above data set I would want to delete the following IDs: 3, 5, 6, 7, and 8. As they not the first in order. The problem is that while I can get isolate and save first instance of each status (in this case 1, 2, and 4), I can’t get the what I want. Is there a SQL way of accomplishing this.
Writing Delete Statement
Jul 30, 2013i have problem to discuss regarding delete statement. Due to the bad design of application i have added some duplicate values in table. i have fixed the issue for new insert but how to delete my old bad id. i have table having three column,
ID1 ID2 ID3
101 2 3
102 2 3
103 2 4
104 3 4
in my case first two record are duplicate i want to keep first one and delete the other.
How To Delete Row Number On Where Statement
Nov 7, 2013I have duplicate rows of data with the exact same data, for multiple sets of data.For example,
1 Fred Flintstone
1 Fred Flintstone
2 Barney Rubble
2 Barney Rubble
etc...
If I use the delete and where command, it will delete both rows and I want to keep one of them. I don't want to copy down each one and re insert as there are too many columns for each one and there are too many rows also.I was thinking of a delete row-number command in a where statement. but tried multiple forums and its not working.
How To Add Subquery To Delete Statement
Apr 28, 2015I have to write a delete statement that deletes all customers that have not put in an order I must use a subquery in the exist operator.
View 5 Replies View Related