In SQL Server 2000/2005 (not CE) I can use the following T-SQL statement to delete orphaned rows from a table:
DELETE GroupsMembers FROM GroupsMembers LEFT OUTER JOIN Groups ON GroupsMembers.GroupID = Groups.ID WHERE Groups.ID IS NULL
SQL Server CE does not seem to support combining the JOIN statement with the DELETE statement. Is this correct? If yes, is there any alternative statement that could be used to accomplish the same thing?
I'm trying to combine the following two strings to create a single Insert statement (and thus only generate one record instead of two). insertString = "Insert comments (uID) Select uID FROM users WHERE uName = @uName" insertString2 = "INSERT comments (eventID, text) VALUES ( @eventID, @comment)" I have tried: Insert comments (uID, eventID, text) SELECT uID FROM users WHERE uName = @uName VALUES (uID, @eventID, @comment) Individually they work fine, but I can't get the syntax correct to allow them to work together. As you can tell, I'm not very good with SQL, so any help would be greatly appreciated! Thanks in advance.
Hey all. Ive got a big problem with an sql statement Im working on.There are 2 tables with a master/detail relationship. The Header Tableis the master, the Line Table is the detail. So for each Header, thereare many Lines, but a Line can only reference one Header.There is a Line Total and Line Cost in each Line Record. Each LineRecord has a type.What I want to be able to do is, for each Header, I want to Sum eachcorresponding Line's Total and Cost where the type is either one valueor another. If the type is, for example, 10, only sum the Total, if itstype 2, only sum the Cost.Therefore, after the query is executed, you should have a result setsomething like thisJob : Job1 (header id)Desc : Job0001 (header desc)Cost : (sum of Line Costs where Line Type is 2 and header id is Job1)Total : (sum of Line Totals where Line Type is 10 and header id isJob1)-----------------------------------------------------------------------------------------------------------Job : Job2 (header id)Desc : Job0002 (header desc)Cost : (sum of Line Costs where Line Type is 2 and header id is Job2)Total : (sum of Line Totals where Line Type is 10 and header id isJob2)-----------------------------------------------------------------------------------------------------------etc.Hope this makes sense. Thanks
I have a SP returning the following result The select statement for this is
Code:
SELECT dbo.TEST1.[OFFICE NAME], COUNT(dbo.TEST1.[ACCOUNT ID]) AS AccountCount FROM dbo.Test2 INNER JOIN dbo.test3 INNER JOIN dbo.Test4 ON dbo.test3.[Accounting Code] = dbo.Test4.[Accounting Code] INNER JOIN dbo.TEST1 ON dbo.Test4.[Office ID] = dbo.TEST1.[ACCOUNT ID] ON dbo.Test2.[Model ID] = dbo.test3.ID INNER JOIN dbo.[Inquiry Details] ON dbo.Test2.InquiryID = dbo.[Inquiry Details].InquiryID WHERE (dbo.Test2.InquiryDate BETWEEN CONVERT(DATETIME, @startDate, 102) AND CONVERT(DATETIME, @endDate, 102)) AND dbo.Test1.[Account ID] IN(SELECT [account id] FROM test5 WHERE [Contact ID] = @contactId) GROUP BY dbo.TEST1.[OFFICE NAME] ORDER BY COUNT(dbo.TEST1.[ACCOUNT ID]) DESC name id count case1 226 320 case2 219 288 case3 203 163 case4 223 90 case5 224 73 i have another select stnat which returns like this The select statement is
Code:
Select test1.[office name], count(test1.[office name]) From test1 inner join test4 on test1.[account id]=test4.[office id] inner join test3 on test4.[accounting Code]=test3.[accounting Code] Group by test1.[Office Name] order by count(test1.[office name]) DESCname count case6 10 case2 56 case4 66 case1 74 case3 88 case7 100 case5 177 How can i combine this select stament with the SP, so that, i get a fourth column with case1 226 320 74 case2 219 288 56 .......................... ........................... Hope i am not confusing you all Please help me, if someone knows how to combine this? Thanks
I have two select statements on a single table as follows:
SELECT * FROM DOCUMENTS
WHERE FILEDATE LIKE '%1987'
SELECT DOCNUM, COUNT(*) AS TOTALS FROM DOCUMENTS
GROUP BY DOCNUM
HAVING (COUNT(*)>1)
I want to combine the them to provide a list of rows in the table that have duplicate "DocNum" but only within the subset of rows LIKE %1987. When I first looked into how this is done I was sure that you would use a subquery. I tried to combine them using the first one as the outer query and the COUNT as the subquery. However, this gave an error that the subquery was returning a value that did not match what the outer query was expecting. So my questions are: Do I really need a subquery in the first place since this is all within one table? And, if not, how can this be done?
I was wondering if it's possible to have a stored procedure that has two select statements which you can combine as a single result set. For instance:select name, age, titlefrom tableaselect name, age, titlefrom tablebCould you combine these queries into a single result set?
I'm looking for a way to combine the following 2 sets of code into one select statement. They're similar in that they reference the same tables, but they have different conditionals:
Code: SELECT TABLE_01.Date , TABLE_01.State , TABLE_01.City , ISNULL((SUM(A.Bandwidth)),0) AS SD_Bandwidth INTO TABLE_FINAL_01 FROM TABLE_01 LEFT OUTER JOIN TABLE_02 A
I have a database that I am splitting the data using odd account numbers and even account numbers. The odd acct numbers in one database and the even in the other database.
This database is very large. The problem is when I run the delete statements it is going to fill up the log files. Can I turn on the "Simple" mode on the database while I am deleteing the data. Will this cause a problem? Then can I turn back on the 'Full' mode when I have finished?
Has anyone ever done this and so how did it work. Or better yet is it possible?
I was curious whether it's possible to audit DELETE statements in the MS SQL database. I created a procedure (below), but I didn't find any event associated with DELETE statements.
Any help will be greatly appreciated!
Thanks, Alla
CREATE proc sp_Turn_Audit_On as /************************************************** **/ /* Created by: SQL Profiler */ /* Date: 11/15/2006 05:16:40 PM */ /************************************************** **/
-- Create a Queue declare @rc int declare @TraceID int declare @maxfilesize bigint declare @StatusMsg varchar declare @ServerTraceFile varchar set @ServerTraceFile = 'E:Program FilesMicrosoft SQL ServerMSSQLTraceAudit_Info' set @maxfilesize = 1024
I'm trying to find information and/or articles on what exactly "Do not replicate DELETE statements" does in transactional replication. How does this affect the Publisher, Distributor, and Subscriber? Does information deleted on the publisher not replicate to the subscriber? What scenarios would someone use this option on a article?
Hi, I am using sql2000 ent edition. I have a partitioned view based on 8 tables. My selects and inserts are fine. But, when I run a delete on the view based on a query on the paritioned column, I get a "Transaction (Process ID 149) was deadlocked and has been chosen as a victim". I looked at the query plan and it was showing a parallel query on all the underlying tables. So, I put the Option(maxdop 1), using only one processor and the delete worked fine.
Does anybody know why? is parallel query create deadlocks? is there any known problems with deletes on partitioned views? same question for updates. I think I have the same problem for updates.
how can i join these 2 queries to produce 1 result
Query 1: select R.Name, T.Branchid, t.TradingDate,t. TransactionDate, convert(varchar,T.Tillid)+'-'+convert(varchar,t.Saleid) as DocketNumber, t.SubTotal, t.SalesRepPercent, t.SalesRepComAmount as CommissionAmt from TransactionHeader T join SalesRep R on R.SalesRepid = T.SalesRepid where T.SalesRepid is not null
Query 2 : select C.TradingName,C.AccountNo From Sale S Join ClMast c on C.Clientid = s.CustomerAccountID
The result should be R.Name,T.Branchid, t.TradingDate,t. TransactionDate,DocketNumber,t.SubTotal, t.SalesRepPercent, t.SalesRepComAmount, TradingName,Accountno..Field Saleid is present in Transactionheader Table and Sale table
I have the following situation. We sell books on our website, and someof the books have more than one author. So I needed to create amany-to-many table, which is the intermidiate table between the authortable and the book table.I can't get the right join statement to work. I've used the code below,submitting an isbn (book id number) to identify the book, but the returnfrom the query simply sends me back all of the authors that are in themany_to_many table(called the book_to_author table here). I'd like it to return only theauthors attached to that isbn, instead of all the authors that are inthat table. What's wrong with the code below? Thanks for your help!SELECT a.firstname, a.lastname, a.title AS degree, a.bio, a.author_id,bf.isbn, bf.title AS booktitle, m.isbn AS Expr2, m.id, m.author_id ASExpr3 FROM author a INNER JOIN book_to_author m ONa.author_id=m.author_id CROSS JOIN book_detail_final bf WHEREbf.isbn='"&isbn&"' order by m.id descBill*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
Hi, I just want you to know that I am very young in ASP.NET world so please bear with me.I have been looking for an answer to my problem, but unfortunately I couldn’t find one. So I created a user here on www.asp.net just for making this post. Before I continue I just want to apologies if there is another post where this question is already answered.
Please watch this Print Screen I just took: � http://www.bewarmaronsi.com/Capture.JPG “ As you can see the “INSERT, UPDATE, and DELETE Statements� are disabled, and that’s exactly my problem. I tried with an MS access database and it works perfect, but when I use a MS SQL database this field gets disabled for some reason. The MDF file is located in the App_data folder and is called ASPNETDB. And when I try to add custom SQL statements, it gives me Syntax error near “=�. Something like that. I bought the Total Training Set1 package and it works perfect in their examples. I just want to thank you for reading my post and I hope that you got some useful information for me. By the way, I’, from Sweden so you have to excuse me if my English is rusty. Thanks! PS: Can it be that I’m running windows Vista?
I have problem in using the SQLDataSource. When in VS 2005 I drag and drop the SQLDataSource onto my page and then add a GridView control.I bind the GridView control to the SQLDataSource control. But the problem is it does not generate the INSERT, UPDATE, and DELETE statements. The dialog box is inactive. The screenshots may help. please help me in this regard. I also tried it for Accesscontrol but the same problem. Sorry for my poor English!. thanks in advance
the screenshot links: http://img139.imagevenue.com/img.php?image=28285_2_122_937lo.JPGhttp://img205.imagevenue.com/img.php?image=27550_1_122_203lo.JPG
I previously asked What does "Do not replicate DELETE statements" do? to make sure I was correct on my thinking of what "Do not replicate DELETE statements" does. And after finding out it does what I would like it to do, create an archive db, I've tried several testing scenarios to see if it would work. So far I have been unable to not replicate delete statements. I'm not sure if I'm not setting a property right or what and any guidance would be appreciated.
Here is what I've done.
Created a blank db to be used as a subscriber and created a test db with some random data in a table. Setup a New Publication on the db with the random data as "Transactional Publication" Selected the following articles and properties in the publication
Tables
DELETE Delivery Format = Do not replicate DELETE statements Views
Default Values Stored Procedures
Default Values User Functions
Default Values Selected the default options for the rest of the New Publication Wizard steps and clicked finish. Created a Pull Subscription on the new publication that I just created. Let it initialize. Then did a select count(*) query on the test table on the publication (18k+ rows) and subscriber (18k+ rows) Then did a delete t-sql from the test table on the publication. Then did a select count(*) query on the test table on the publication (0 rows) and subscriber (0 rows).
Now shouldn't it not delete the records on the subscription db?
I am working on an app that getting quite a few deadlocks due to delete statements. Â I have turned on the sql trace flags and pulled the offending delete statements out of the ERRORLOG and trying to mesh those up with the indexes defined on the table, etc. looking to see if there is anything that can be done strictly from the db side (no app code change) to reduce/eliminate these deadlocks. Â I have ran some tests/played around with RCSI and even disabling lock escalation but neither have improved my results.
What I have done is to search the errorlog file for DELETE FROM Tablename, output those matching lines, then sort of normalize the literal values to # or XYZ, open in Notepad++, removed trailing whitespace + dups and sort to come up with these results for the unique list of offending T-SQL statements (a LOT easier to read in text editor so sending screen cap. Â
I have two seperate tables that I need to get the data into one select statement. Here is the two separate select statements that I have created.
Select AGEGROUP, [YEAR], SUM(CASES) as Cases FROM tbHosp a LEFT JOIN tbAGEGROUP b on a.AgeGroupID = b.AgeGroupID LEFT JOIN tbYEARHOSP f on f.YEARID = a.YEARID LEFT JOIN tbSEX g on g.SEXID = a.SEXID where a.AgeGroupID in (1,2,3) and a.YEARID in (1,2,3) Group BY AgeGroup, [YEAR] ORDER BY AgeGroup, [YEAR]
Select AGEGROUP, [YEAR], SUM(POPULATION) as [Population] FROM tbPopulation a LEFT JOIN tbAGEGROUP b on a.AgeGroupID = b.AgeGroupID LEFT JOIN tbYEARHOSP f on f.YEARID = a.YEARID LEFT JOIN tbSEX g on g.SEXID = a.SEXID where a.AgeGroupID in (1,2,3) and a.YEARID in (1,2,3) Group BY AgeGroup, [YEAR] ORDER BY AgeGroup, [YEAR]
Hi,I'm new to ASP.NET and having a problem configuring the SqlDataSource control. I am using the standard ASP.NET 2.0 "aspnetdb" database to manage user accounts. The problem is this:When using the wizard to configure my SqlDataSource control, the option to auto-generate the Insert/Update/Delete SQL statements are grayed out. I've searched this forum and found that this can be a symptom of no primary keys in the tables. However, there are primary keys (UserId), which is the default schema as generated by asp.net (aspnet_regsql.exe). When I use the wizard, I make the following choices:How would you like to retrieve data from your database?-> Select "Specify columns from a table or view"-> Select the "vw_aspnet_MembershipUsers" view from the "Name:" drop-down list-> Select "UserId", "Email", "UserName" from "Columns:"After this, still no option to auto-generate I/U/D statements. Any thoughts on why this isn't working??? Thanks,Leah.
We are using MS SQL Server 2008. I am running a batch job which deletes 21 days older records(6-7 million records). But daily we have transaction is going on in the database. When the delete occurs, all the insert statements got blocked and waits till the delete statement to complete. May I know why the blocking occurs?Â
In t-sql 2008 r2, I have 2 select statements that I would like to join by calendarID . I would like to obtain the results in the same query but I keep getting syntax errors.
The first select is the following:
SELECT Section.number, Section.homeroomSection, Course.number, Course.name, Course.homeroom, Calendar.calendarID FROM Section INNER JOIN Course ON Course.number = Section.number INNER JOIN Calendar ON Course.calendarID = Calendar.calendarID
The second select is the following:
SELECT Person.studentNumber, Enrollment.grade, Calendar.calendarID, Calendar.name, Calendar.endYear FROM Enrollment INNER JOIN Person ON Enrollment.personID = Person.personID INNER Calendar ON Enrollment.calendarID = Calendar.calendarID
Is it possible to have an AND within an inner join statment? The below query works, except for the line marked with --*--.
The error I get is the "multipart identifier pregovb.cellname could no be bound", which usually means that SQL server can't find what I'm talking about, but it's puzzling, as I've created the temp table with such a column in it.
Is there a different way i should be structuring my select statement?
SELECT [Survey Return].SurveyReturnID, '1', #temp_pregovb.paidDate, #temp_pregovb.email FROM #temp_pregovb, [Survey Return] INNER JOIN SelectedInvited ON [Survey Return].SelectedID = SelectedInvited.SelectedID --*-- AND [SelectedInvited].cellref=#temp_pregovb.cellname
INNER JOIN [panelist Contact] ON SelectedInvited.PanelistID=[Panelist Contact].PanelistID WHERE [panelist contact].email=#temp_pregovb.email AND SelectedInvited.CellRef IN ( SELECT surveycell FROm [Survey Cells] WHERe SurveyRef='5')
I have an SQL data source on my page and I select "Table". On the next screen I pick the fields I want to show. Then I click the "Advanced" button because I want to allow Inserts, updates and deletes. But its all greyed out abd I can't check this option. The UID in the connection string I am connecting under has the correct permissions in SQL server to do inserts, update and deletes too. Anyone know why it would be greyed out? The connectionstring property in the aspx code is dynamic but this shouldn't be the reason because I have used this before with success
well 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'
