How To Delete All Tables (the Devils Work)
Apr 9, 2004dont open the "How To Delete All Tables" post
it's view count is 666
I guess the devil really is in the details
ADVERTISEMENT
Delete Does Not Work.
Jul 2, 2007Hi all.I have used a SqlDataSource in my page with this delete command:DELETE FROM tblPersonnel WHERE (ID = @original_ID)and the "OldValueParameterFormatSring" property of the datasource is "original_{0}".and i also have a GridView and a button for delete in rows.(it's CommandName is "Delete"). But when i try to delete a record, the record does not get deleted and this button only makes a PostBack on the page! Why doesn't it work?
Thanks in advance.
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
Why Does The DELETE Trigger Not Work ?
Dec 1, 2004I have the following delete trigger but it doesn't work.
ALTER TRIGGER Users_DeleteUsers
ON dbo.Users
FOR DELETE
AS
DELETE FROM InstantForum_Members WHERE MemberID IN (SELECT ForumMemberId from Deleted)
DELETE FROM InstantKB_Users WHERE UserID IN (SELECT KBMemberId from Deleted)
But when I delete a user from Users table, I get an error in this trigger saying no commit or rollback given in trigger.
Can someone tell me why this trigger will fail ?
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.
Can Seem To Get Delete And Exist To Work Right
Sep 18, 2007Hi all,
I am writing a test result database in SQL 2K5 and one of the features I want to implement is a stored procedure that deletes the oldrecords while preserving a set number of records, the following is what my SP looks like
Procedure [dbo].[CleanResults]
@RecsToKeep bigInt,
@Output Int output
as
Declare @Date Datetime
Declare @Count bigint
set @Date = getdate()
Select @Count = Count(UniqueID) from [Main]
Print 'THE COUNT IS'
print @Count
if ( @Count > @RecsToKeep) begin
set @Count= @Count - @RecsToKeep
Print 'THE Number to delete is'
print @Count
select TOP(@Count) UniqueID from Main order By [Main].[TestDateTime] desc
Delete from [Main] where exists (select TOP(@Count) * from Main order By [Main].[TestDateTime] desc);
set @Output = @Count
end
else begin
set @Output = -1
end
whats odd is that the select staement will evaluate correctly and return the oldest record @Count record, however the delete stament removes all the records. An advice would be appriciated.
Thanks Christopher
PS Any advice for using TOP with variables in MSDE 2k (as opposed to 2k5) would be appreciated
Delete Query That Doesn't Work ???
Apr 19, 2007Hi!
Do you know why this query is ok on SQL 2005 and not on SQL ce ??
Code Snippet
DELETE ProfilesGroups
FROM ProfilesGroups pg
INNER JOIN Groups g
ON tpGroupID = g.gId
WHERE pg.tpProfileID = '7df60fae-a026-4a0b-878a-0dd7e5308b09'
AND g.gProfileID = '8a6859ce-9f99-4aaf-9ed6-1af66cd15894'
Thx for help ;-).
PlaTyPuS
SQL Server 2005 - Can't Get CASCADE DELETE To Work
Aug 13, 2007I'm using SQL Server 2005 (product version = 9.00.1406.00, product level = RTM, and edition = Developer Edition). I have a db with a number of tables; I created a Foreign Key in one table and added a Foreign Key w/ ON DELETE CASCADE to it -- all using Microsoft SQL Server Management Studio. When I delete the record in the table with the foreign key, the record in the other table does not get deleted. I tried doing this with a simple SQL script in Microsoft SQL Server Management Studio and in a simple .Net / ADO (C#-based) program. Only the record in the table that I'm specifically deleting is deleted.
Here's the table that is referenced by the foreign key (I told Server Management Studio to write out the script):
USE [CHNOPSDb]
GO
/****** Object: Table [dbo].[tblDeviceContainer] Script Date: 08/13/2007 16:47:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblDeviceContainer](
[ID] [int] IDENTITY(1,1) NOT NULL,
[DeviceContainerTypeID] [int] NOT NULL,
CONSTRAINT [PK_tblDeviceContainer] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
Here's the table that has the foreign key to the above table (again, I told Management Studio to write out the script):
USE [CHNOPSDb]
GO
/****** Object: Table [dbo].[tblNode] Script Date: 08/13/2007 16:46:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblNode](
[ID] [int] IDENTITY(1,1) NOT NULL,
[NodeName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[NodeTypeID] [int] NOT NULL,
[UnitID] [int] NOT NULL,
[pDeviceContainerID] [int] NOT NULL,
[NodeIndex] [int] NULL,
CONSTRAINT [PK_Node] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
USE [CHNOPSDb]
GO
ALTER TABLE [dbo].[tblNode] WITH CHECK ADD CONSTRAINT [FK_tblNode_tblDeviceContainer] FOREIGN KEY([pDeviceContainerID])
REFERENCES [dbo].[tblDeviceContainer] ([ID])
ON DELETE CASCADE
I then perform a delete using the following:
Use CHNOPSDb;
delete from tblNode where ID = 1;
It deletes the tblNode record but doesn't delete the tblDeviceContainer record that is referenced by tblNode.
Any help?
Thanks,
Bill
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
Manually Defined Delete Command Doesn't Work
Feb 16, 2008Hi,
i defined a sqldatasource in VWD manually (option specify sql statement) because several tables are involved. I also need a Delete statement, so i defined it also manually.
The select and delete statement are :
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:test %>" SelectCommand="SELECT aspnet_Users.UserName as lid, aspnet_Roles.RoleName as categorie, aspnet_Users.beheerder as beheerder, aspnet_Membership.Email as emailadres FROM aspnet_Users INNER JOIN aspnet_Membership ON aspnet_Users.UserId = aspnet_Membership.UserId INNER JOIN aspnet_UsersInRoles ON aspnet_Users.UserId = aspnet_UsersInRoles.UserId inner JOIN aspnet_Roles ON aspnet_UsersInRoles.RoleId = aspnet_Roles.RoleId order by username"
DeleteCommand="delete from aspnet_users where userid=@userid"> <DeleteParameters> <asp:Parameter Name="userid" /> </DeleteParameters> </asp:SqlDataSource>
My problem is that the Select works, but not the Delete.
Any idea why?
Thanks
tartuffe
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 View RelatedSql Server 2005 Maintenance Task Delete Files Does Not Work
Mar 24, 2008I used the toolbox to select maintenance cleanup task to create the job to do this. In reading similar notes regarding this problem, some people mentioned that there was a choice to include subfolders. I do not have this choice. When I execute select @@version I get Microsoft SQL Server 2005 - 9.00.3042.00 (X64) Feb 10 2007 00:59:02 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2) . This is running on a cluster. Any idea what is going on here? Thanks.
Work Tables/bookmark Lookups
Feb 4, 2006I notice that SQL Server 2005 creates worktables where SQL 2000 does not. Often these work tables appear in STATISTICS IO, but they show a 0 scan count and 0 logical reads. These worktables often appear to be substituted for bookmark lookups.
Has the optimizer decided to use worktables instead of bookmark lookups (often resulting in a higher cost plan)?
Sharon
The Multi Delete && Multi Update - Stored Procedure Not Work Ok
Feb 4, 2008the stored procedure don't delete all the records
need help
Code Snippet
DECLARE @empid varchar(500)
set @empid ='55329429,58830803,309128726,55696314'
DELETE FROM [Table_1]
WHERE charindex(','+CONVERT(varchar,[empid])+',',','+@empid+',') > 0
UPDATE [empList]
SET StartDate = CONVERT(DATETIME, '1900-01-01 00:00:00', 102), val_ok = 0
WHERE charindex(','+CONVERT(varchar,[empid])+',',','+@empid+',') > 0
UPDATE [empList]
SET StartDate = CONVERT(DATETIME, '1900-01-01 00:00:00', 102), val_ok = 0
WHERE charindex(','+CONVERT(varchar,[empid])+',',','+@empid+',') > 0
TNX
Delete From Two Tables
Jul 31, 2006These are my selcet and delete commands, however I am trying to delete from two tables both Assets and AssetAttribute. The both have related colums AssetTypeId, AssetAttributeId. I need to slect them first then delete them. I need the one action delete to delete int eh same information from both tables. Help. I only have the one written for Asst table, I need to include the Assetattribute table. Please help me. SelectCommand="SELECT AssetId, AssetTypeId, Description, AssetAttributeId, SKU, Barcode, GovBarcode, WarehouseId, IsVehicle, DeploymentStatus FROM Asset, AssetAttribute WHERE AssetId = @AssetId" DeleteCommand="DELETE FROM Asset, AssetAttribute WHERE AssetId = @AssetId"
View 3 Replies View RelatedDelete All Tables
Dec 4, 2006Hi folk, whats the SQL syntax of deleting all user tables of a specific database on a Microsoft SQL server?thanks in advance,mulata
View 3 Replies View RelatedHow To Delete From Two Tables At Once
Jun 18, 2007I have 2 tables "Orders" and "OrderProducts"
In my application, there are moments when I clean up these tables.
There is a query that looks for some flag in the "Orders" table, and deletes the records.
But there are related (PK-FK) records in the "OrderProducts" table.
How can I delete also these records in the same query?
Need To Delete From Two Tables.
Oct 23, 2007I've set up a grid control on my other page and trying to delete items via a check box. I got it to work with deleting from one table but I need to delete from two tables at the same time.
How do I code using a stored procedure called Delete Resource instead of the "Delete from Titles where ...(code is below)
Now if I can add my other table called resources to the sql query that's fine as they both will use titleID to delete the info.Any help would be appreciated. Thanks!
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim gvIDs As String = ""
Dim chkBox As Boolean = False
'Navigate through each row in the GridView for checkbox itemsFor Each gv As GridViewRow In GridView1.Rows
Dim deleteChkBxItem As CheckBox = CType(gv.FindControl("deleteRec"), CheckBox)
If deleteChkBxItem.Checked Then
chkBox = True
gvIDs += CType(gv.FindControl("TitleID"), Label).Text.ToString + ","
End If
NextDim cn As Data.SqlClient.SqlConnection = New Data.SqlClient.SqlConnection(SqlDataSource1.ConnectionString)
If chkBox Then
TryDim deleteSQL As String = _
"DELETE from Titles WHERE TitleID IN (" + _
gvIDs.Substring(0, gvIDs.LastIndexOf(",")) + ")"Dim cmd As Data.SqlClient.SqlCommand = New Data.SqlClient.SqlCommand(deleteSQL, cn)
cn.Open()
cmd.ExecuteNonQuery()
GridView1.DataBind()Catch Err As Data.SqlClient.SqlException
Response.Write(Err.Message.ToString)
Finally
cn.Close()
End Try
End IfProtected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim gvIDs As String = ""
Dim chkBox As Boolean = False
'Navigate through each row in the GridView for checkbox itemsFor Each gv As GridViewRow In GridView1.Rows
Dim deleteChkBxItem As CheckBox = CType(gv.FindControl("deleteRec"), CheckBox)
If deleteChkBxItem.Checked Then
chkBox = True
gvIDs += CType(gv.FindControl("TitleID"), Label).Text.ToString + ","
End If
NextDim cn As Data.SqlClient.SqlConnection = New Data.SqlClient.SqlConnection(SqlDataSource1.ConnectionString)
If chkBox Then
TryDim deleteSQL As String = _
"DELETE from Titles WHERE TitleID IN (" + _
gvIDs.Substring(0, gvIDs.LastIndexOf(",")) + ")"Dim cmd As Data.SqlClient.SqlCommand = New Data.SqlClient.SqlCommand(deleteSQL, cn)
cn.Open()
cmd.ExecuteNonQuery()
GridView1.DataBind()Catch Err As Data.SqlClient.SqlException
Response.Write(Err.Message.ToString)
Finally
cn.Close()
End Try
End IfEnd Sub
End Class
Delete From Tables
Oct 6, 2004I have 3 tables have the same column ID.I want to delete records in one query like "delete from orders as o,ordersdetail as od,membersdns as m where o.ID = 821" but I get the error "incorrect syntax near as" .is this possible to delete records like this?
View 6 Replies View RelatedDelete From Two Tables
Jul 31, 2006These are my selcet and delete commands, however I am trying to delete from two tables both Assets and AssetAttribute. The both have related colums AssetTypeId, AssetAttributeId. I need to slect them first then delete them. I need the one action delete to delete int eh same information from both tables. Help. I only have the one written for Asst table, I need to include the Assetattribute table. Please help me.
SelectCommand="SELECT AssetId, AssetTypeId, Description, AssetAttributeId, SKU, Barcode, GovBarcode, WarehouseId, IsVehicle, DeploymentStatus FROM Asset, AssetAttribute WHERE AssetId = @AssetId"
DeleteCommand="DELETE FROM Asset, AssetAttribute WHERE AssetId = @AssetId"
How To Delete All Tables
Apr 3, 2004Hi All,
How can i delete all the tables in a DATABASE with a single shot!!
Thanx in advance
Delete From ALL Tables
Nov 19, 2007I have 5 tables in the same database named the following: do, name, olm, image, person, scr
They are all link together by and have the following field in each: FCN
I would like to delete the corresponding record in each table where the value of the field 'image_path' in the 'image' table is '999999.jpg'
What would the syntax be?
delete from do, name, olm, image, person, scr
where image.image_path = '999999.jpg'
innerjoin something????
Thanks
Delete Tables
Feb 26, 2008Hello,
To delete all tables from my database I use:
SELECT 'DROP table ' + table_NAME
FROM INFORMATION_SCHEMA.tables
To get the list of the tables. Then I run that list:
DROP table Folders
DROP table Files
...
Because of relationships I need to run it various times until all the tables disappear. Is there a way to everything in one command?
Thank You,
Miguel
Delete All Tables
Oct 5, 2007
Hi,
How can i delete/drop all tables in a database using a script.
Thanks.
2 Tables = Redundant Data/ DISTINCT Doesnt Work
Apr 27, 2008Hello everybody,
have following problem:
I need info from 2 Tables. from the Table 2 I just need 1 column. When i ask for this column the output I get is data repeating themselve many times.
Distinct, should give me unique data, but is doesnt....
the code:
SELECT DISTINCT FSenddate, FSupplyIDName, FSupplyerNumber,FBillNo,FSourceBillNo,FItemName,FItemModel,
FAuxQty,FAuxTaxPrice,FHeadSelfP0237
FROM vwICBill_26
WHERE FSenddate BETWEEN DATEADD(dd,-14,GETDATE()) AND GETDATE()
This code just works in Table1 (vwICBill_26)
but with table 2 (vwICBill_1)
SELECT DISTINCT vwICBill_26.FSenddate,vwICBill_26.FSupplyIDName,
vwICBill_26.FSupplyerNumber,vwICBill_26.FBillNo,
vwICBill_26.FSourceBillNo,vwICBill_26.FItemName,
vwICBill_26.FItemModel,vwICBill_26.FAuxQty,
vwICBill_26.FAuxTaxPrice,vwICBill_26.FHeadSelfP0237,
vwICBill_1.FDate,vwICBill_1.FContractBillNo
FROM vwICBill_26,vwICBill_1
WHERE vwICBill_26.FSenddate BETWEEN DATEADD(dd,-14,GETDATE()) AND GETDATE()
AND vwICBill_1.FContractBillNo=vwICBill_26.FSourceBillNo
The last sentence is the problem
I want that it shows me the data that is not equal.
As soon as I implement the not equal it shows me the massive repeating data.
I mean even without the last sentence I get this data output.
All together, I want a clear database output without data repeating.
Any ideas how it may work without DISTINCT?
I think this problem is a typical amateure problem, but I would apreciate help!
Delete Tables Automatically
Nov 12, 2006How can I delete tables in a SQLServer Database automatically (at the moment I just do it using Micorosft SQl Management Studio Express manually) But its important that I can do that automatically, the best thing would be if I can do that from a .NET programm. thanks in advance,mulata
View 6 Replies View RelatedDELETE From Multiple Tables
Jan 15, 2007I am converting all the inline sql in my ASP.NET app to stored procs and I am experiencing some difficultly with my shortest and least complex stored proc. I am new to both ASP.NET and SQL especially using stored procedures so this may have some extremely obvious flaw in it. I am trying to delete a entry that has parts in two tables. I created a stored procedure along the lines of: CREATE PROCEDURE DeleteEntry(Â Â Â Â Â @EntryIDINT)ASDELETE FROM firstTable WHERE entry_id = @EntryIDDELETE FROM secondTable WHERE entry_id = @EntryIDI have a remove button on a asp page that executes the stored procedure but it only removes the entry from the second table the first time I click it and then when I click it a second time it removes the entry from the first table.
View 1 Replies View RelatedDelete From 3 Tables In Sql Server
Mar 25, 2004Hi,
I am working on IBUYSTORE's db. I have added a backoffice/admin to the site.
I have a page where I retrieve a customer's details based on the customerid such as the following:
Dim Ssql As String = "SELECT O.CUSTOMERID, C.CUSTOMERID, C.PASSWORD, C.FULLNAME, C.EMAILADDRESS, O.ORDERID, OD.ORDERID , OD.PRODUCTID FROM customers AS C , ORDERS AS O , ORDERDETAILS AS OD WHERE C.customerID=O.CUSTOMERID AND OD.ORDERID=OD.ORDERID AND C.customerID = @cust_ID"
CMD As New SqlCommand(Ssql, oConn)
CMD.Parameters.Add("@cust_ID", id)
I am also using a sqldatareader.
I have a delete button. If the site manager clicks on that button i want to delete all that customer's details including all his orders from both orders and orderdetails tables.
I am not sure how to accomplish it.
The tables are as follows:
Customers: Orders: OrderDetails
customerid customerid orderid
orderid
I tried the following delete:
strDelete = "DELETE FROM CUSTOMERS where CustomerID=@CUST_id; DELETE FROM ORDERS WHERE CUSTOMERID=@CUST_id; DELETE FROM ORDERDETAILS WHERE ORDERID=@orderID "
Dim objCMD As New SqlCommand(strDelete, oConn)
objCMD.Parameters.Add("@CUST_id", id)
objCMD.Parameters.Add("@orderID", LBLORDER.Text)
oConn.Open()
objCMD.ExecuteNonQuery()
oConn.Close()
I get the following error:
DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_Orders_Customers'. The conflict occurred in database 'Store', table 'Orders', column 'CustomerID'. DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_OrderDetails_Orders'. The conflict occurred in database 'Store', table 'OrderDetails', column 'OrderID'. The statement has been terminated. The statement has been terminated
Delete From 2 Tables With 1 SQL Query
Mar 23, 2004I have 2 tables that are joined together by a primary key (Order Number). Can I use one SQL query to delete from both of the tables. One table contains the order information from a client (Order Number, Customer Name etc). The other table has order information (Order Number, Item Number, Quantity Ordered etc.)
I need one statement that will allow me to remove the items from both tables. Can this be done.
Thanks in Advance
Wes
How To Delete Data From All The Tables?
Apr 7, 2008I want to reset my application and delete all the data in all tables.
But I have question for this.
1. I do not know how to loop over all the tables and delete data.
2. the database have database diagram so threre are dependency with tables so the delete order is hard to decide.
Please give me a idea.
Thanks
Mark
Delete From Multiple Tables
Jun 9, 2008OK, this is a big problem, with multiple tables, but here goes. Here's my schema:
--------------------------------------------------
Events
--------------------------------------------------
ID | E_Title
--------------------------------------------------
--------------------------------------------------
EventOptionGroups
--------------------------------------------------
ID | EOG_EventID | EOG_OptionGroupID
--------------------------------------------------
--------------------------------------------------
OptionGroups
--------------------------------------------------
ID | OG_Title
--------------------------------------------------
--------------------------------------------------
Options
--------------------------------------------------
ID | O_OptionGroupID
--------------------------------------------------
--------------------------------------------------
EventRegistration
--------------------------------------------------
ID | ER_EventID
--------------------------------------------------
--------------------------------------------------
RegistrantOptions
--------------------------------------------------
ID | RO_EventRegistrationID | RO_OptionGroupID
--------------------------------------------------
OK, what I'm trying to do is, when I delete an event, I need to delete all the data associated with that event. So here's the thought process.
Delete Event based on ID
Delete all EventRegistration where ER_EventID = Event.ID
Delete all RegistrantOptions where RO_EventRegistrationID = EventRegistration.ID
Delete all EventOptionGroups where EOG_EventID = Event.ID
Delete all OptionGroups where OptionGroups.ID = EOG_OptionGroupID
Delete all Options where O_OptionGroupID = OptionGroups.ID
Sorry that it's so complicated, by I need help. There are foreign key constraints on the tables as well, so you have to work from the bottom back up.
How To Delete Multiple Tables At Once
May 28, 2012In my DB there some tables that I would like to delete. Is there a way to do it NOT one by one?
View 4 Replies View RelatedDelete Query For Two Tables
Nov 26, 2013I have two tables PROFILES & ROLE
CREATE TABLE PROFILES(
ID varchar(20) UNIQUE NOT NULL,
Name varchar(40) NULL,
Address varchar(25) NULL
)
[Code] ...
This query joins both the tables and gets displayed in a grid.
select P.ID, Name, Address, Role, Applications
from PROFILES P
Inner Join ROLE R
ON P.ID= R.ID
I need to write a delete query which deletes data from both the table in a single query.