want to delete rows from two tables after a join.
in Access, here's something we can do:
delete table_A.*, table_B.*
from table_A left join table_B on ...
but in ms sql, it appears (to me) that you can only delete from one table at a time. how would i accomplish what i want to do?
also, is there a distinctrow equivalent in ms sql server? thanks
I have 3 tables . iwant to delete rows from all the three tables at same time using single statement.All the 3 tables have a unique column which will be supplied ny the user.
DELETE FROM T1,T2,T3 WHERE column1='1'
how do i do it.
Hi,
I'm using ASP with a JScript variant and MSSQL Server 2000. I would like to write a script that basically erases all data except for a few things.
Is there a way to update multiple tables at once without having to write lines and lines of code? I tried UPDATE tbl1,tbl2 SET uid='asc', but to no avail. It gave me a syntax error. My thinking behind it is something like... UPDATE dbo.* SET uid='mferguson' and after that I can delete stuff like DELETE dbo.*... Any ideas?
I know the above is ASP, I've tried this thread in the ASP forum with no avail... they referred me to this forum.
Hi,
I have different tables with the same schema as follows
ID Name
-----------------
<Table 1>
ID Name
-------------------
1 Name1
2 Name2
3 Name3
<Table 2>
ID Name
-------------------
1 Name1
4 Name4
5 Name5
I just want to delete the row where ID = 1 from these tables in one query ? Is it possible??
Thanks
~Mohan
I'm new to relational database concepts and designs, but what i've learned so far has been helpful. I now know how to select certain records from multiple tables using joins, etc. Now I need info on how to do complete deletes. I've tried reading articles on cascading deletes, but the people writing them are so verbose that they are confusing to understand for a beginner. I hope someone could help me with this problem.
I have sql server 2005. I use visual studio 2005. In the database I've created the following tables(with their column names):
Table 1: Classes --Columns: ClassID, ClassName
Table 2: Roster--Columns: ClassID, StudentID, Student Name
Table 3: Assignments--Columns: ClassID, AssignmentID, AssignmentName
Table 4: Scores--StudentID, AssignmentID, Score
What I can't seem to figure out is how can I delete a class (ClassID) from Classes and as a result of this one deletion, delete all students in the Roster table associated with that class, delete all assignments associated with that class, delete all scores associated with all assignments associated with that class in one DELETE sql statement.
What I tried to do in sql server management studio is set the ClassID in Classes as a primary key, then set foreign keys to the other three tables. However, also set AssignmentID in Table 4 as a foreign key to Table 3.
The stored procedure I created was
DELETE FROM Classes WHERE ClassID=@classid
I thought, since I established ClassID as a primary key in Classes, that by deleting it, it would also delete all other rows in the foreign tables that have the same value in their ClassID columns. But I get errors when I run the query. The error said:
The DELETE statement conflicted with the REFERENCE constraint "FK_Roster_Classes1". The conflict occurred in database "database", table "dbo.Roster", column 'ClassID'.
The statement has been terminated.
What are reference constraints? What are they talking about? Plus is the query correct? If not, how would I go about solving my problem. Would I have to do joins while deleting?
I thought I was doing a cascade delete. The articles I read kept insisting that cascade deletes are deletes where if you delete a record from a parent table, then the rows in the child table will also be deleted, but I get the error.
Did I approach this right? If not, please show me how, and please, please explain it like I'm a four year old.
Further, is there something else I need to do besides assigning primary keys and foreign keys?
is there a way to delete multiple tables using the Management Studio GUI?
View 1 Replies View RelatedHi,
I have a relational database with the primary table, table01. And 2 child/foreign tables, table02 and table03. All 3 tables shared the same key - [ID].
I am not sure if this is the correct approach but I am trying to create a stored procedure where if I were to delete a the row in table01 (primary), the procedure will automatically delete the common row in both table02 and table03.
I have come up with something like that but it does not seems to be correct.
CREATE PROCEDURE [sp_delete_test01_1] (@id [int])
AS
DELETE [test01] DELETE [test02] DELETE [test03]
WHERE ( [id] = @id)GO
Your advise please. Many Thanks.
Hi
i have to delete the master table data without deleting the child table records,is there any solution for this, parent table has relation with the child table.
regards
vinod.t.v
I think cursors might help me, but I'm not sure. I'm looking for ideason how to solve a problem I have.Consider two tables, one table contains student information (very wide100 fields) , the other historical changes of the student information,(narrow, just fields that record changes).As an example Table one has STUDENT_ID, STUDENT_MAJOR, STUDENT_NAME,RECORD_DT and has one student in it.Table two contains STUDENT_ID, STUDENT_MAJOR , CHANGE_DT and contains 2records, since the student changed their major 2 times.I want to end up with a table the contains 3 rows, the 2 changes to theMajor and the current student record. I want each row to be complete.Everything that I have tried (joins, outer joins, union) I end up withsome field being null (in my example, the STUDENT_NAME would on be inthe original row, and null for the two changes)I know this is pretty vague, but I am wondering if this is a place touse CURSORS?(Some of you may recognize this as a type 2 dimension or slowlychanging dimension as used in a data warehouse, which it is. I need tobuild up my historical changes to I can feed it to my warehouse. I havethe current student record, and all the descreet changes made to thestudent.)TIARob
View 18 Replies View RelatedIs there a way to delete from multiple tables/views a column with a specificname? For example, a database has 50 tables and 25 views all have a columnnamed ColumnA. Is it possible to write a simple script that will deleteevery column named ColumnA from the database?Seems to be it would be possible and I can somewhat vision it usingsysobjects but without wanting to spend too much time generating the script(when I could in shorter time manually delete) thought I'd pose the question.Thanks.
View 2 Replies View RelatedHi! I have a general SQL CE v3.5 design question related to table/file layout. I have an system that has multiple tables that fall into categories of data access. The 3 categories of data access are:
1 is for configuration-related data. There is one application that will read/write to the data, and a second application that will read the data on startup.
1 is for high-performance temporal storage of data. The data objects are all the same type, but they are our own custom object and not just simple types.
1 is for logging where the data will be permanent - unless the configured size/recycling settings cause a resize or cleanup. There will be one application writing alot [potentially] of data depending on log settings, and another application searching/reading sections of data.
When working with data and designing the layout, I like to approach things from a data-centric mindset, because this seems to result in a better performing system. That said, I am thinking about using 3 individual SDF files for the above data access scenarios - as opposed to a single SDF with multiple tables. I'm thinking this would provide better performance in SQL CE because the query engine will not have alot of different types of queries going against the same database file. For instance, the temporal storage is basically reading/writing/deleting various amounts of data. And, this is different from the logging, where the log can grow pretty large - definitely bigger than the default 128 MB. So, it seems logical to manage them separately.
I would greatly appreciate any suggestions from the SQL CE experts with regard to my approach. If there are any tips/tricks with respect to different data access scenarios - taking into account performance, type of data access, etc. - I would love to take a look at that.
Thanks in advance for any help/suggestions,
Bob
I need to delete multiple sql logins on one of my database servers that are no longer needed. Most of these logins are not attached to any database on the server because the database has been deleted from this server. Is there any way to delete logins that are no longer needed without having to review every login to verify it is not attached to a database before deleting?
View 2 Replies View RelatedI have a table with a record that looks like the attached TXT.
I need to keep the most recently entered value where flag_out = 1 and delete those duplicate
records, and this should only apply to records where there's also an flag_in value of 1.
I've tried a bunch of delete statements without avail....
TIA
Hi,
How do you delete multiple packages at once ?
We have a folder in SSIS called ETL and there are about 25 SSIS packages in it.
Now we need to update it.
I tried to delete the folder but you get the message that the folder is not empty.
So I renamed the folder to ETL_old and my deployement works fine.
But now I want to get rid of all the old folders. Delete a folder didn't work.
Selecting multiple packages doesn't work.
To delete a single one you select the package, right click and select delete, click yes.
But then I have to do it 25 times. I was unable to set a short cut with the keyboard for that action.
Any ideas ?
Constantijn Enders
Hey all,
I know very simple SQL queries but I need help with this one. I have multiple lines in a SQL database that I need to run. Basically, I need to run this (the bracketed text and the XXX are place holders):
DELETE FROM [tableName] WHERE [columnName] = 'XXXXX'
But I need to run it around 90 times where XXXXX is a unique variable each time. I could create 90 lines similar to this one but that would take way too much time to run. Any suggestions for a noob?
Thanks,
- MT
-=<>=-=<>=-=<>=-=<>=-=<>=-
Matt Torbin
President
Center City Philadelphia Macintosh Users Group
http://www.ccpmug.org/
Hi, I am pretty new to SQL and know that I am probably going around this the wrong way.
I want to make a stored proc that deletes rows from table 1 and delete rows from table 2 where the common link is the id.
Any help would be greatly appreciated!
Many thanks
moop
hi,
How can I delete two tables with a WHERE condition.
That is I can't do this -What can be an alternate of this statement -
Delete from Tab1,Tab2,Tab3
where
Tab1.name = Tab2.Name and
Tab2.name = Tab3.name
TIA
Does anyone know why it takes a long time (Approx 10 minutes) to delete a DB. I have 2 identical environments for test and prod. I can delete DBs from my test environment quickly but it takes forever to delete a DB from the production environment. I am running SQL2000 SP3a. There are about 25 DBs in the environment with their sized ranging from 100meg to 2 gig. The only differnece in the environments is all test DBs are simple recovery mode and modt prod DBs are full.
Thanks,
Ken
Hello everyone,
I have to delete some rows from three different tables. My tables are:
- VERSION_1
- ID
- VERSION_2
- ID_VERSION FOREIGN KEY
- VERSION_3
- ID_VERSION FOREIGN KEY
The 3 tables are related to each other by the VERSION_1 ID.
what i have to do is deleting first from VERSION_3, then from Version_2 and finally from VERSION_1. This is what i got so far:
DELETE FROM VERSION_3, VERSION_2, VERSION_1 WHERE VERSION_3.ID_VERSION = ? AND VERSION_2.ID_VERSION = ? AND VERSION_1.ID = ?
I cannot make it work, could someone please help me? i am stuck at this.
Thanks in advance
How do I delete from multi tables?
There are no issues with keys.
Will this work?
DELETE MC.Document
FROM dbo.dw_MasterClaim MC, dbo.dw_MasterClaim_Checks CHK, dbo.dw_MasterClaim_ChgDate,dbo.dw_MasterClaim_Diagnosis, dbo.dw_MasterClaim_InsNo, dbo.dw_MasterClaim_Pay, dbo.dw_MasterClaim_ProcLine, dbo.dw_MasterClaim_ProcLine2, dbo.dw_MasterClaim_ProcLine3, dbo.dw_MasterClaim_RiskPool, dbo.dw_MasterClaim_SuspHist
WHERE MC.Document IN
(Select Document
FROM fl1stDwImport.dbo.raw_MasterClaim
WHERE Document = MC.Document)
I am new to SQL server and have just installed SQL Server 2005 (came withOffice 2007- think it's Express edition).I am trying to get my head around this.Just been working on an Access data base and have upsized to SQL.Having made several changes I have done this a number of times - linkingboth direct to file and creating a new application Access SQL project.Now I would like to know how to delete any tables or databases I have placedon the SQL before my system gets too messy.Furthur information - This is on my personal computer and is not shared orused by other people.Any ideas?Scott
View 1 Replies View RelatedI'm triyng to delete from three tables.
Issue Table:
Column Name
Data Type
Allow Nulls
issueID
int
Unchecked
name
varchar(50)
Unchecked
title
varchar(100)
Checked
description
varchar(500)
Checked
crntIssue
bit
Checked
frntPage
int
Checked
archived
bit
Checked
navOrder
int
Checked
dateCreate
datetime
Unchecked
Outlook Table:
Column Name
Data Type
Allow Nulls
ID
int
Unchecked
menu
bit
Checked
mnuOrder
int
Checked
mnuLevel
int
Checked
parent
int
Checked
issueID
int
Unchecked
masterPage
varchar(100)
Unchecked
visible
bit
Checked
name
varchar(50)
Checked
title
varchar(250)
Unchecked
description
varchar(1000)
Checked
summary
text
Checked
contents
text
Checked
image
varchar(50)
Checked
imgCaption
varchar(1000)
Checked
approve
bit
Checked
createDate
datetime
Unchecked
Links Table:
Column Name
Data Type
Allow Nulls
lnkID
int
Unchecked
linkFromID
int
Unchecked
linkToID
int
Unchecked
Issue table contais all the magazine issues. Outlook table contains all the pages of the Issue table. And the Links table contains the links or connection between parent page and child page. So here's what I wanted to do. When I click the delete issue button, I want to delete any pages, links, and issue from three tables that matches the issue ID that I wanted to delete. So for example, if I wanted to delete issueID 2, all the pages in the Outlook table and any links of those pages(lnkFromID) that are in the Links table should be deleted too. The lnkFromID and lnkToID are foriegn key of Outlook.ID table. The lnkFromID is the parent and lnkToID is the child.
I was wondering that maybe I can three delete statements for three tables. Is this a possibility? So any help is much appreciated.
Hi All
I'm new to sql server. I have built simple database apps using MFC CRecordset over MS Access. I'm tying to learn about SQL server by building a simple app using MFC CRecordset in Visual Studio 2005.
The problem I have is within SQL Server Management studio experess. I have a table called OriginalDrawings that looks like this
CREATE TABLE [dbo].[OriginalDrawings](
[DrawingID] [int] IDENTITY(1,1) NOT NULL,
[OriginalFileName] [varchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
[PartNumber] [varchar](50) COLLATE Latin1_General_CI_AS NOT NULL,
[SheetNumber] [int] NULL,
[Revision] [varchar](50) COLLATE Latin1_General_CI_AS NULL,
[OriginalDirectory] [varchar](100) COLLATE Latin1_General_CI_AS NULL,
[DrawingCategory] [int] NOT NULL,
[ProductFamily] [int] NOT NULL,
[IsSalvage] [bit] NULL,
[FileSize] [int] NULL,
[DataQueryFlag] [bit] NOT NULL,
[DataQueryCode] [int] NULL,
CONSTRAINT [PK_OriginalDrawings] PRIMARY KEY CLUSTERED
(
[DrawingID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
I've loaded it with 40,000 records from my test app. If I open it in SSMSE hit Ctrl-A to select all and then press delete SSMSE appears to hang - it freezes for 10 mins+ (after which I restart my PC. I can delete 1000 records at a time OK, I can delete all quite quickly from my test app by walking through the recordset and deleting each record.
Does anyone know why it appears to hang when I try the delete all?
Thanks
Alec
SQL Server 2005 Express 9.00.3042.00
Microsoft SQL Server Management Studio Express 9.00.2047.00
Microsoft Data Access Components (MDAC) 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)
Microsoft MSXML 2.6 3.0 4.0 5.0 6.0
Microsoft Internet Explorer 6.0.2900.2180
Microsoft .NET Framework 2.0.50727.42
Operating System 5.1.2600
What I'm trying to do is delete a user and all their related information within the other tables. I'm not wanting to delete the table, just one column with that user and their related information. So my Primary_Key is UserID within the table [alumni] and my three Foreign_Keys are CommentID, PhotoID, and AlbumID within the tables [comments], [photos], and [albums]. Here is some of the code that I have:
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:SoderquistString %>"
DeleteCommand="DELETE FROM [alumni] WHERE [UserID] = @UserID"
SelectCommand="SELECT [UserID], [UserName], [FirstName], [LastName], [State] FROM [alumni] WHERE ([State] = @State)">
<DeleteParameters>
<asp:Parameter Name="UserID" Type="Int32" />
</DeleteParameters>
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList1" Name="state" PropertyName="SelectedValue"
Type="String" />
</SelectParameters>
</asp:SqlDataSource>
The users are set up in GridView form. Is there some type of DELETE command that I need to be writing that is different than the one above? I have tried adding onto the following DELETE statment:
DeleteCommand="DELETE FROM [alumni] WHERE [UserID] = @UserID
DELETE FROM [photo] WHERE [UserID] = @UserID;
DELETE FROM [album] WHERE [UserID] = @UserID;
DELETE FROM [comment] WHERE [UserID] = @UserID;
...but that doesn't work...and doesn't look right. I would really appreciate anyones suggestions or help that you may be able to provide. Thank you!
Is there a way to automatically delete tables in a database? I want to delete tables with a specific prefix in their names (that I assign to them) based on their age. Is there a way to do this automatically while keeping the tables that I don't want to touch?
joe
I need to remove duplicate data from around 25 tables. I want to use a while loop to go through all tables. If I list out all of the column names the query runs fine, but since there are 25 tables some with 50 plus columns I was hoping to use something like the following, which errors out because my sub queries return more than one result.
SELECT q.* from
(Select ROW_NUMBER() OVER ( Partition BY (SELECT [name] AS [Name] FROM syscolumns
WHERE id = (SELECT id FROM sysobjects
WHERE type = 'U'
AND [Name] = 'Orders')
)
Order by (select top 1 [name] AS [Name] FROM syscolumns
[Code] ....
Hi how can I delete a local temp table?
I know a golbal is deleted by
if exists(select * from tempdb..sysobjects where name='##MyTemGlobalTable' and type='U')drop table ##MyTemGlobalTable'
but how do I drop a #MyTemGlobalTable' ?
thank you
i have a database sturcture :
| tbl_Customer | -> one to many ->| tbl_Orders |
What i want is to delete one customer from the tbl_Customer table, and automatically delete all the child orders to that customer.
i could by using loops, look through all the childs and delete the records one by one and the customer last.
so i wounder if there are an SQL state that automatically look up childs and drop them all by calling an stored procedure.
Hello all,
I have a DTS package set up to import a text file on a daily basis. I need to dump the data in 2 table after 7 days of the last import .this is the code that I have
Delete From TblTemp
date(Day(-7), CurrentStamp).
But for some reason it deleting the data right after it imports it. And it doesn't delete anything out of the other table.
Thanks in advance
Hello I have 16000 tables in a sql database and I need a sql query command to delete empty tables from that sql database please help.
F16 LĂ?GHTĂ?NĂ?NNNG
Hi,
How can I prevent a colleage to delete tables in a specific database.
Yes he has access to Enterprise Manager. We would like to allow him read only to the live databases.
Is this possible?
Hi,How can i reset to zero, deleting all the users who are in the aspnet_* tables in my production 2003 server?Thanks
View 6 Replies View RelatedHi All,
So.. I'm a complete newb to SQL stuff.
I managed to find the 'Deleting Duplicate Records' from SQLTeam.com (thanks, by the way!!).. I managed to modify it for one of my tables (one of 14).
-- Add a new column
Alter table dbo.tblMyDocsSize add NewPK int NULL
go
-- populate the new Primary Key
declare @intCounter int
set @intCounter = 0
update dbo.tblMyDocsSize
SET @intCounter = NewPK = @intCounter + 1
-- ID the records to delete and get one primary key value also
-- We'll delete all but this primary key
select strComputer, strATUUser, RecCount=count(*), PktoKeep = max(NewPK)
into #dupes
from dbo.tblMyDocsSize
group by strComputer, strATUUser
having count(*) > 1
order by count(*) desc, strComputer, strATUUser
-- delete dupes except one Primary key for each dup record
deletedbo.tblMyDocsSize
fromdbo.tblMyDocsSize a join #dupes d
ond.strComputer = a.strComputer
andd.strATUUser = a.strATUUser
wherea.NewPK not in (select PKtoKeep from #dupes)
-- remove the NewPK column
ALTER TABLE dbo.tblMyDocsSize DROP COLUMN NewPK
go
drop table #dupes
Now that I've got that figured out, I need to write the same thing to fix the other 13 tables (with different column info)- and I'll need to run this daily.
Basically I've put together some vbscript that gathers inventory data and drops it into an MSDE db (sorry - goin for 'free' stuff right now). Problem is it has to run daily so that I'm sure to capture computers that turned on at different times etc which ever-increases my database 'till I bounce off the 2GB limit of MSDE.
So the question is, what would be the best way to do this? Can I put the code into a stored procedure that I can execute each day?
Thanks for your help....