TSQL : How To Delete Duplicated Rows Except The Top 1 Order By Some Fields?
the table is like :
ID F1 F2 F3
--- --- --- ---
1 A 1 VR
2 B 2 VR
3 A 3 VF
4 A 2 VF
5 B 1 VF
......
the rules is:
if there are rows with same values of F3 field,then choose the top 1 row order by F1,F2 in the group,and delete other rows.
how can I delete rows with ID in (2 , 3 , 5)?
View Complete Forum Thread with Replies
Related Forum Messages:
TSQL - Avoid Duplicated Rows - Using Distinct / Group By
Hi guys, need some help here please... The code below shows 4 rows. The first two rows are almost identical, but the two of them exists in the same table as different rows. Row number 1 is also related to Row number 3 and Row number 2 is also related to Row number 4 The problem is that I have to use only one of then (Rows number 1 or 2) togheter with row 3 & 4. I thought using GROUP BY RECEIPTJURNALMATCH.JURNALTRANSID, but getting error. Thanks in advance, Aldo. Code Snippet SELECT RECEIPTJURNALMATCH.JURNALTRANSID AS 'R.JURNALTRANSID', RECEIPTJURNALMATCH.MATCHNUM AS 'R.MATCHNUM', JURNALTRANSMOVES.ACCOUNTKEY AS 'J.ACCOUNTKEY', JURNALTRANSMOVES.SUF AS 'J.TOTAL', STOCK.REMARKS AS 'S.REMARKS' FROM RECEIPTJURNALMATCH INNER JOIN JURNALTRANSMOVES ON RECEIPTJURNALMATCH.JURNALTRANSID = JURNALTRANSMOVES.ID LEFT OUTER JOIN STOCK ON RECEIPTJURNALMATCH.STOCKID = STOCK.ID WHERE JURNALTRANSMOVES.ACCOUNTKEY IN ('123456') Below the results: R.JURNALTRANSID R.MATCHNUM J.ACCOUNTKEY J.TOTAL S.REMARKS 89634 16702 123456 1155 ×¢×—: ;5752 89634 16703 123456 1155 ×¢×—: ;5752 89637 16702 123456 400 NULL 89639 16703 123456 155 NULL
View Replies !
To Retrieve And Delete The Dupilicate Rows From Which Does Not Have Any Unique Fields
is there any way to Retrieve duplicate rows from a table which does not have any unique columns. that is a select should be written in such a way that it returns all the duplicate rows. a delete statement should written to delete those duplicate rows. if there any seudo colums in sql server as in oracle. if not how to implement the concept. that is there should be a dummy column in the result set of the select which should display the row number like the seudo column rownum in oracle.
View Replies !
Querys And Duplicated Fields
Hello, I've got a trouble with my query and left outer joins. I've got 2 tables, table A and B, both have the same record called ID. I used this query: SELECT * FROM A LEFT OUTER JOIN B ON A.ID = B.ID This is OK and works fine, my trouble comes when i have a duplicated ID on both A and B. Instead of return 2 fields, it returns me 4. Is there some way to force SQl server to return only the first founded on B, but the 2 duplicated IDs on the A table? Thanks for all.
View Replies !
Delete One Of The Duplicated Row
I have many data in a table in which some rows are duplicated. How can I, for all duplicated rows, delete the extra rows and leave only one? You may assume checking one column is enough to tell if a row is duplicated. Thanks
View Replies !
Duplicated Rows
Hello, I have a table T1 with fields ID, F1, F2, F3, F4, F5, F6…. I need to find if there is duplicated rows based on F1, F2, F3 columns. If there is set F5=’minimum’ where ID is MIN(ID). So the smallest should be set as minimum. How can I do this in a stored procedure?
View Replies !
Duplicated Rows
Hello, I have a table T1 with fields ID, F1, F2, F3, F4, F5, F6€¦. I need to find if there is duplicated rows based on F1, F2, F3 columns. If there is set F5=€™minimum€™ where ID is MIN(ID). So the smallest should be set as minimum. How can I do this in a stored procedure?
View Replies !
Duplicated Rows
Hi, I have just started developing in SQL Express in the last 2 months so still learning. The problem I€™m having with my stored procedure is that I get duplicate rows in my results. The row is a duplicate in terms of column 'Job No' as when the query runs in access only one instance of each 'Job No' is returned but when I recreate the query in SQL server I get a number of rows back for the same 'Job No'? How would I go about getting just 1 instance of each 'Job No' back? With column 'Days to Date' showing the total 'Days to Date' for each Job No. Please see Ms Access results if unsure of what I€™m asking. A copy of the stored procedure is below and a sample of the out-put with Ms Access results at very bottom. ALTER PROCEDURE [dbo].[sl_DaysDonePerJob] AS SELECT CASE WHEN [Job No] IS NULL THEN '' ELSE [Job No] END AS [Job No], SUM([Actual Days]) AS [Days to Date], CONVERT(nvarchar(10),MIN(SessionDate),101) AS [Start Date], CONVERT(nvarchar(10),MAX(SessionDate),101) AS [End Date], MAX(CASE WHEN DATEPART(MM,SessionDate)=1 THEN 'Jan' WHEN DATEPART(MM,SessionDate)=2 THEN 'Feb' WHEN DATEPART(MM,SessionDate)=3 THEN 'Mar' WHEN DATEPART(MM,SessionDate)=4 THEN 'Apr' WHEN DATEPART(MM,SessionDate)=5 THEN 'May' WHEN DATEPART(MM,SessionDate)=6 THEN 'Jun' WHEN DATEPART(MM,SessionDate)=7 THEN 'Jul' WHEN DATEPART(MM,SessionDate)=8 THEN 'Aug' WHEN DATEPART(MM,SessionDate)=9 THEN 'Sep' WHEN DATEPART(MM,SessionDate)=10 THEN 'Oct' WHEN DATEPART(MM,SessionDate)=11 THEN 'Nov' WHEN DATEPART(MM,SessionDate)=12 THEN 'Dec' END) AS 'End Month' FROM Sessions GROUP BY [Job No], Sessions.SessionDate ORDER BY [Job No] Results in SQL Server Express 'Job No' 'DaystoDate' 'Start Date' 'End Date' 'End Month' 1113-001 0 08/16/2001 08/16/2001 Aug 1113-002 0.5 07/11/2000 07/11/2000 Jul 1113-002 0.5 02/09/2000 02/09/2000 Feb 1116-001 1 07/07/1999 07/07/1999 Jul 1116-001 1 07/06/1999 07/06/1999 Jul 1118-001 1 01/12/1999 01/12/1999 Jan 1118-001 0.5 03/17/1999 03/17/1999 Mar 1118-001 1 02/23/1999 02/23/1999 Feb 1118-001 1 01/26/1999 01/26/1999 Jan 1118-001 0.5 03/09/1999 03/09/1999 Mar 1118-001 1 12/15/1998 12/15/1998 Dec 1118-001 1 02/09/1999 02/09/1999 Feb Results in Ms Access Days Done per Job JobNo Days to Date Start Date End Date End Month 1113-001 0.00 16/08/2001 16/08/2001 Aug01 1113-002 1.00 09/02/2000 11/07/2000 Jul00 1116-001 2.00 06/07/1999 07/07/1999 Jul99 1118-001 6.00 15/12/1998 17/03/1999 Mar99
View Replies !
Deleting Duplicated Rows
Hi, I have a table named "std_attn", where, by some bad coding, lots of duplicated rows have been created. And the table don't have any PK. So Now tell me the way to remove the duplicaies.................. thnx
View Replies !
How To Remove Rows Where Only Part Of The Row Is Duplicated
Hi,I've got a db table containing 5 columns(excluding id) consisting of1.) First Half of a UK postcode2.) Town name to which postcode belongs3.) Latitude of Postcode4.) Longitude of Postcode5.) Second Part of the PostcodeI want to select columns 1,2,3 and 4, but once only. There are oftenseveral entries where 1 and 2 are the same but 3 and 4 are differenti.e.WA1Bewsey and Whitecross53.386492-2.596847WA1Bewsey and Whitecross53.388203-2.590961WA1Bewsey and Whitecross53.388875-2.598504WA1Fairfield and Howley53.388455-2.581701WA1Fairfield and Howley53.396117-2.571789My current query isSELECT DISTINCT Postcode, Town, latitude, longitudeFROM PostcodeWHERE Postcode.Postcode = 'wa1'ORDER BY Postcode, TownHowever as latitude and longitude differ on each line DISTINCT doesnot do what I'm looking for.Can anybody suggest a way changing the query to just give the firstinstance of each Postcode/Town combo?I.E.WA1Bewsey and Whitecross53.386492-2.596847WA1Fairfield and Howley53.388455-2.581701Many thanks!Drew
View Replies !
TSQL ? Order By Combination(varchar + Int)
Trying to sort varchar data with values that can be int as well i.e. 104, 27, AW345, 113, 1M001... Some brilliant "application specialist" set these up a way long time ago, now I have to deal with it. How would someone separate these values to have the "numeric-like" data sort in numerical order, while the character values sort and group together as text? The app is a result of running data from a legacy system in a sql7 driven e-commerce solution.
View Replies !
TSQL: I Want To Use A SELECT Statement With COUNT(*) AS 'name' And ORDER BY 'name'
I am very new to Transact-SQL programming and don't have a programmingbackground and was hoping that someone could point me in the rightdirection. I have a SELECT statement SELECT FIXID, COUNT(*) AS IOIsand want to ORDER BY 'IOI's'. I have been combing through the BOL, butI don't even know what topic/heading this would fall under.USE INDIISELECT FIXID, COUNT(*) AS IOIsFROM[dbo].[IOI_2005_03_03]GROUP BY FIXIDORDER BY FIXIDI know that it is a simple question, but perhaps someone could assistme.Thanks,
View Replies !
How To Run Delete Query / Delete Several Rows Just By One Click ?
I'm using SqlDataSource and an Access database. Let's say I got two tables:user: userID, usernamemessage: userID, messagetextLet's say a user can register on my website, and leave several messages there. I have an admin page where I can select a user and delete all of his messages just by clicking one button.What would be the best (and easiest) way to make this?Here's my suggestion:I have made a "delete query" (with userID as parameter) in MS Access. It deletes all messages of a user when I type in the userID and click ok.Would it be possible to do this on my ASP.net page? If yes, what would the script look like?(yes, it is a newbie question)
View Replies !
Delete Doesn't Delete Rows, But @@ROWCOUNT Says It Did
I 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?
View Replies !
Cascade Delete Contraints - Accessible Through TSQL?
Hi all, I was wondering if there is an easy way to loop through all contraints in a database and programmatically set the cascade delete to ON. I have a database with hundreds of contraints, so individually setting cascade delete on them is not optimal. Thanks for any info in advance! I think that the constraints are simply held in one of the system datatables, is there anyway to simply update that table?
View Replies !
Tsql - Avoid Repeated Rows
Hi Guys, I am using the query below to retrieve these results: You can see that the results are repeated, once for DATIF = 1 and then again for DATIF = 2. In this case does not matter if the results appear close to DATIF 1 or DATIF 2. Take in care that I can not know how may extradates or Extrasums are attached to each Account. Is there any way to avoid these repeated rows? Thanks in advance, Aldo. ACCOUNTKEY DATFID DATFNAME DATF SUFID SUFNAME SUF --------------- ----------- -------------------------------------------------- ----------------------- ----------- -------------------------------------------------- ---------------------- 123456 1 ExtraDates01 2005-01-01 00:00:00.000 1 ExtraSum01 4 123456 1 ExtraDates01 2005-01-01 00:00:00.000 2 ExtraSum02 3 123456 1 ExtraDates01 2005-01-01 00:00:00.000 3 ExtraSum03 1 123456 1 ExtraDates01 2005-01-01 00:00:00.000 4 ExtraSum04 2 123456 2 ExtraDates02 2004-01-01 00:00:00.000 1 ExtraSum01 4 123456 2 ExtraDates02 2004-01-01 00:00:00.000 2 ExtraSum02 3 123456 2 ExtraDates02 2004-01-01 00:00:00.000 3 ExtraSum03 1 123456 2 ExtraDates02 2004-01-01 00:00:00.000 4 ExtraSum04 2 Code Snippet SELECT DISTINCT Accounts.ACCOUNTKEY, ExtraDates.DATFID, ExtraDateNames.DATFNAME , ExtraDates.DATF , ExtraSums.SUFID , ExtraSumNames.SUFNAME , ExtraSums.SUF FROM EXTRADATES AS ExtraDates LEFT OUTER JOIN EXTRADATENAMES AS ExtraDateNames ON ExtraDates.DATFID = ExtraDateNames.DATFID RIGHT OUTER JOIN ACCOUNTS AS Accounts ON ExtraDates.KEF = Accounts.ACCOUNTKEY LEFT OUTER JOIN EXTRASUMS AS ExtraSums LEFT OUTER JOIN EXTRASUMNAMES AS ExtraSumNames ON ExtraSums.SUFID = ExtraSumNames.SUFID ON Accounts.ACCOUNTKEY = ExtraSums.KEF LEFT OUTER JOIN EXTRANOTENAMES RIGHT OUTER JOIN EXTRANOTES ON EXTRANOTENAMES.NOTEID = EXTRANOTES.NOTEID ON Accounts.ACCOUNTKEY = EXTRANOTES.KEF WHERE Accounts.SORTGROUP BETWEEN 0 AND 999999999 AND Accounts.ACCOUNTKEY BETWEEN '123456' AND '123456'
View Replies !
HOw To Order Fields Of Type Varchar?
I have a varchar field which holds IDs like (1, 3, 5, 19, 23) when I order it, i get it ordered in ASCII order like (1, 19, 23, 3, 5) rather than (1, 3, 5, 19, 23) Even if I convert it to int, I won't be able to order it. is there any way I can order a varchar field numerically? Angel
View Replies !
TSQL - Create A Variable Number Of Rows
I have a booking system which stores an event along with a start date and a finish date. I want to create a table which has a row for each day the event is on. I can find the number of days easily enough using a datediff function but I don't know how to translate this into that number of rows. My current thinking is to cross join the original table out to another temporary table with a large number of rows and use SQL2005 to only select the top 'x' rows, then use the rownumber of this temp table to add the required number of days to the StartDate to get each eventdate. Currently I use a loop to build the required table but there must be a way to get it all done in a single statement :) Thanks ORIGINAL DATA EventID StartDate EndDate 1 1/1/08 1/1/08 2 20/1/08 22/1/08 REQUIRED DATA EventID EventDate 1 1/1/08 2 20/1/08 2 21/1/08 2 22/1/08
View Replies !
Need A Quick Hand Using ORDER BY With Two Fields In The Same Table.
I have a problem with ordering and I am hoping that someone is able to help. In my table I have two fields, "requestdate" and "sentdate", and when I display the records I would like to sort by BOTH fields. I want to do this so that the full query is in order by date. I tried: ORDER BY requestdate, sentdate DESC But obviously all that does is order by requestdate (which is NULL or a date) and than it will order by sentdate. Can somebody tell me how to order by both as if they were the same field? Thanks!
View Replies !
TSQL - Updating Multiple Rows In A Table W/ Different Values
Using MS SQL 6.5, code below is in a Trigger or Stored Procedure. This query is from our developers still learning TSQL. We want to update a column in a Table (tblHourlyNoms)with a value from a View (vwCalc). Each record in vwCalc should correspond to exactly one record in tblHourlyNoms. The join is running off the primary key of tblHourlyNoms. The view is built out of 2 tables, as we thought this might be simpler. UPDATE tblHourlyNoms SET tblHourlyNoms.Allocation = vwCalcs.Expr1 FROM tblHourlyNoms INNER JOIN vwCalcs ON tblHourlyNoms.GasHour = vwCalcs.GasHour AND tblHourlyNoms.CON_ID = vwCalcs.CON_ID AND tblHourlyNoms.capID = vwCalcs.capID AND tblHourlyNoms.DeliveryPoint = vwCalcs.DeliveryPoint which ought to be the same as UPDATE tblHourlyNoms SET tblHourlyNoms.Allocation = vwCalcs.Expr1 FROM tblHourlyNoms, vwCalcs WHERE tblHourlyNoms.GasHour = vwCalcs.GasHour AND tblHourlyNoms.CON_ID = vwCalcs.CON_ID AND tblHourlyNoms.capID = vwCalcs.capID AND tblHourlyNoms.DeliveryPoint = vwCalcs.DeliveryPoint but it neither works! We get an error complaining of multiple rows returned via a subquery. yet this select does exactly what we require (except update the tblHourlyNoms table!). Help? Thanks, Matt.
View Replies !
Validation Of Returned Order Of Fields From A Stored Proc
Hi, Can we validate the returned order of fields from a stored procedure? Infact, i am taking a query as user input and extracts the results based on the query but for that order of fields specified in a query is important. Can i check the order after the query is run i.e if this is entered "select field1,field2,field3 from table" then i need to check the order of the resultset generated. I can't check the query before the resultset is generated because a user can enter bunch of queries. Any way will work, tsql or .net app. Thanks,
View Replies !
DataSet Rows Being Deleted, But After The Update , The Sql Database Is Not Updated. The Delete Rows Still In The Database.
Stepping thru the code with the debugger shows the dataset rows being deleted. After executing the code, and getting to the page presentation. Then I stop the debug and start the page creation process again ( Page_Load ). The database still has the original deleted dataset rows. Adding rows works, then updating works fine, but deleting rows, does not seem to work. The dataset is configured to send the DataSet updates to the database. Use the standard wizard to create the dataSet. cDependChildTA.Fill(cDependChildDs._ClientDependentChild, UserId); rowCountDb = cDependChildDs._ClientDependentChild.Count; for (row = 0; row < rowCountDb; row++) { dr_dependentChild = cDependChildDs._ClientDependentChild.Rows[0]; dr_dependentChild.Delete(); //cDependChildDs._ClientDependentChild.Rows.RemoveAt(0); //cDependChildDs._ClientDependentChild.Rows.Remove(0); /* update the Client Process Table Adapter*/ // cDependChildTA.Update(cDependChildDs._ClientDependentChild); // cDependChildTA.Update(cDependChildDs._ClientDependentChild); } /* zero rows in the DataSet at this point */ /* update the Child Table Adapter */ cDependChildTA.Update(cDependChildDs._ClientDependentChild);
View Replies !
VS2005 - Using Detailsview To Update, Insert And Delete Rows From SQL 2005 Database. Delete And Insert Work But Update Does Not - No Errors Returned
Using VS 2005 DetailsView to insert, delete, and update rows in SQL 2005 database. insert and delete work but update does not. I recieve no errors and the detailsView comes back unchanged (as well as table row is unchanged). I am trying to use as little code behind as possible. However I do have ItemUpdating routines that seem to work (i.e Checking table for new login duplicates and encrypting passwords). The following is the source code generated by VS2005:<%@ Page Language="VB" AutoEventWireup="false" CodeFile="frmDbRegionMgrNew.aspx.vb" Inherits="frmDbRegionMgrNew" Title="Region Manager DB Update" Theme="detailsVeiwTheme" %><%@ Import Namespace="System.Data" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml" ><head runat="server"><title>Untitled Page</title> </head><body><form id="form1" runat="server"><div><asp:Label ID="Label1" runat="server" Font-Bold="True" Font-Size="Large" ForeColor="#004000"Style="z-index: 101; left: 14px; position: absolute; top: 10px" Width="199px">Data Base Maintenance </asp:Label><asp:HyperLink ID="HyperLink1" runat="server" BackColor="ActiveBorder" BorderColor="ActiveBorder"BorderStyle="Outset" Font-Bold="True" Font-Size="X-Small" ForeColor="#004000"Height="31px" NavigateUrl="DataBaseMaint.aspx" Style="z-index: 133; left: 524px;position: absolute; top: 7px" Width="96px">DB Main Menu</asp:HyperLink><br /><br /><br /><table style="width: 654px"><tr><td style="width: 120px"><asp:Label ID="Label2" runat="server" Font-Bold="True" Font-Size="Large" ForeColor="#004000"Text="Regional Manager" Width="189px"></asp:Label></td><td style="width: 100px"></td><td style="width: 203px"></td></tr><tr><td style="width: 120px" valign="top"><asp:Label ID="Label3" runat="server" Font-Bold="True" Font-Size="Small" ForeColor="#004000"Style="z-index: 128; left: 2px; position: absolute; top: 115px" Width="128px">Select Greenhouse -></asp:Label></td><td style="width: 100px" valign="top"><asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True" DataSourceID="SqlDataSource1"DataTextField="Name" DataValueField="GrnHseID"></asp:DropDownList><asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:MetrolinadataConnectionString %>"SelectCommand="SELECT [GrnHseID], [Name] FROM [Greenhouse]"></asp:SqlDataSource></td><td style="width: 203px"> <asp:DetailsView ID="DetailsView1" runat="server" AllowPaging="True" AutoGenerateRows="False"DataKeyNames="RegionMgrID" DataSourceID="SqlDataSource2" Height="50px" Width="125px"><Fields><asp:TemplateField HeaderText="RegionMgrID" InsertVisible="False" SortExpression="RegionMgrID"><EditItemTemplate><asp:Label ID="Label1" runat="server" Text='<%# Eval("RegionMgrID") %>'></asp:Label></EditItemTemplate><ItemTemplate><asp:Label ID="Label1" runat="server" Text='<%# Bind("RegionMgrID") %>'></asp:Label></ItemTemplate></asp:TemplateField><asp:TemplateField HeaderText="GrnHseID" SortExpression="GrnHseID"><EditItemTemplate><asp:Label ID="Label2" runat="server" Text='<%# Eval("GrnHseID") %>'></asp:Label></EditItemTemplate><InsertItemTemplate><asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("GrnHseID") %>'></asp:TextBox></InsertItemTemplate><ItemTemplate><asp:Label ID="Label2" runat="server" Text='<%# Bind("GrnHseID") %>'></asp:Label></ItemTemplate></asp:TemplateField><asp:BoundField DataField="DispLvl" HeaderText="DispLvl" SortExpression="DispLvl" /><asp:BoundField DataField="FName" HeaderText="FName" SortExpression="FName" /><asp:BoundField DataField="Minit" HeaderText="Minit" SortExpression="Minit" /><asp:BoundField DataField="LName" HeaderText="LName" SortExpression="LName" /><asp:BoundField DataField="Phone" HeaderText="Phone" SortExpression="Phone" /><asp:BoundField DataField="Ext" HeaderText="Ext" SortExpression="Ext" /><asp:BoundField DataField="Cell" HeaderText="Cell" SortExpression="Cell" /><asp:BoundField DataField="Email" HeaderText="Email" SortExpression="Email" /><asp:BoundField DataField="Login" HeaderText="Login" SortExpression="Login" /><asp:BoundField DataField="Password" HeaderText="Password" SortExpression="Password" /><asp:BoundField DataField="AccessLvl" HeaderText="AccessLvl" SortExpression="AccessLvl" /><asp:CommandField ShowDeleteButton="True" ShowEditButton="True" ShowInsertButton="True" /></Fields></asp:DetailsView><asp:SqlDataSource ID="SqlDataSource2" runat="server" ConflictDetection="CompareAllValues"ConnectionString="<%$ ConnectionStrings:MetrolinadataConnectionString %>" DeleteCommand="DELETE FROM [RegionMgr] WHERE [RegionMgrID] = @original_RegionMgrID AND [GrnHseID] = @original_GrnHseID AND [DispLvl] = @original_DispLvl AND [FName] = @original_FName AND [Minit] = @original_Minit AND [LName] = @original_LName AND [Phone] = @original_Phone AND [Ext] = @original_Ext AND [Cell] = @original_Cell AND = @original_Email AND [Login] = @original_Login AND [Password] = @original_Password AND [AccessLvl] = @original_AccessLvl"InsertCommand="INSERT INTO [RegionMgr] ([GrnHseID], [DispLvl], [FName], [Minit], [LName], [Phone], [Ext], [Cell], , [Login], [Password], [AccessLvl]) VALUES (@GrnHseID, @DispLvl, @FName, @Minit, @LName, @Phone, @Ext, @Cell, @Email, @Login, @Password, @AccessLvl)"OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT * FROM [RegionMgr] WHERE ([GrnHseID] = @GrnHseID)"UpdateCommand="UPDATE [RegionMgr] SET [GrnHseID] = @GrnHseID, [DispLvl] = @DispLvl, [FName] = @FName, [Minit] = @Minit, [LName] = @LName, [Phone] = @Phone, [Ext] = @Ext, [Cell] = @Cell, = @Email, [Login] = @Login, [Password] = @Password, [AccessLvl] = @AccessLvl WHERE [RegionMgrID] = @original_RegionMgrID AND [GrnHseID] = @original_GrnHseID AND [DispLvl] = @original_DispLvl AND [FName] = @original_FName AND [Minit] = @original_Minit AND [LName] = @original_LName AND [Phone] = @original_Phone AND [Ext] = @original_Ext AND [Cell] = @original_Cell AND = @original_Email AND [Login] = @original_Login AND [Password] = @original_Password AND [AccessLvl] = @original_AccessLvl"><DeleteParameters><asp:Parameter Name="original_RegionMgrID" Type="Int32" /><asp:Parameter Name="original_GrnHseID" Type="Int32" /><asp:Parameter Name="original_DispLvl" Type="Int32" /><asp:Parameter Name="original_FName" Type="String" /><asp:Parameter Name="original_Minit" Type="String" /><asp:Parameter Name="original_LName" Type="String" /><asp:Parameter Name="original_Phone" Type="String" /><asp:Parameter Name="original_Ext" Type="String" /><asp:Parameter Name="original_Cell" Type="String" /><asp:Parameter Name="original_Email" Type="String" /><asp:Parameter Name="original_Login" Type="String" /><asp:Parameter Name="original_Password" Type="String" /><asp:Parameter Name="original_AccessLvl" Type="Int32" /></DeleteParameters><UpdateParameters><asp:Parameter Name="GrnHseID" Type="Int32" /><asp:Parameter Name="DispLvl" Type="Int32" /><asp:Parameter Name="FName" Type="String" /><asp:Parameter Name="Minit" Type="String" /><asp:Parameter Name="LName" Type="String" /><asp:Parameter Name="Phone" Type="String" /><asp:Parameter Name="Ext" Type="String" /><asp:Parameter Name="Cell" Type="String" /><asp:Parameter Name="Email" Type="String" /><asp:Parameter Name="Login" Type="String" /><asp:Parameter Name="Password" Type="String" /><asp:Parameter Name="AccessLvl" Type="Int32" /><asp:Parameter Name="original_RegionMgrID" Type="Int32" /><asp:Parameter Name="original_GrnHseID" Type="Int32" /><asp:Parameter Name="original_DispLvl" Type="Int32" /><asp:Parameter Name="original_FName" Type="String" /><asp:Parameter Name="original_Minit" Type="String" /><asp:Parameter Name="original_LName" Type="String" /><asp:Parameter Name="original_Phone" Type="String" /><asp:Parameter Name="original_Ext" Type="String" /><asp:Parameter Name="original_Cell" Type="String" /><asp:Parameter Name="original_Email" Type="String" /><asp:Parameter Name="original_Login" Type="String" /><asp:Parameter Name="original_Password" Type="String" /><asp:Parameter Name="original_AccessLvl" Type="Int32" /></UpdateParameters><SelectParameters><asp:ControlParameter ControlID="DropDownList1" Name="GrnHseID" PropertyName="SelectedValue"Type="Int32" /></SelectParameters><InsertParameters><asp:Parameter Name="GrnHseID" Type="Int32" /><asp:Parameter Name="DispLvl" Type="Int32" /><asp:Parameter Name="FName" Type="String" /><asp:Parameter Name="Minit" Type="String" /><asp:Parameter Name="LName" Type="String" /><asp:Parameter Name="Phone" Type="String" /><asp:Parameter Name="Ext" Type="String" /><asp:Parameter Name="Cell" Type="String" /><asp:Parameter Name="Email" Type="String" /><asp:Parameter Name="Login" Type="String" /><asp:Parameter Name="Password" Type="String" /><asp:Parameter Name="AccessLvl" Type="Int32" /></InsertParameters></asp:SqlDataSource> </td></tr><tr><td style="width: 120px"></td><td style="width: 100px"></td><td style="width: 203px"></td></tr></table></div></form></body></html>Appreciate any and all help!Dave
View Replies !
TSQL - Use ORDER BY Statement Without Insertin The Field Name Into The SELECT Statement
Hi guys, I have the query below (running okay): Code Block SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02' FROM myTables WHERE Conditions are true ORDER BY Field01 The results are just as I need: Field01 Field02 ------------- ---------------------- 192473 8461760 192474 22810 Because other reasons. I need to modify that query to: Code Block SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02' INTO AuxiliaryTable FROM myTables WHERE Conditions are true ORDER BY Field01 SELECT DISTINCT [Field02] FROM AuxTable The the results are: Field02 ---------------------- 22810 8461760 And what I need is (without showing any other field): Field02 ---------------------- 8461760 22810 Is there any good suggestion? Thanks in advance for any help, Aldo.
View Replies !
Rows Insert Out Of Order
I am currently trying to insert or import some rows into a table and sql server always seems to sort it by one of the columns in a different order that I insert the data. I would appreciate any feedback on this issue. Here is my table structure. columnA columnB columnC columnD columnE columnF columnG char char int int int smallint char it keeps sorting by column F and seperates them by odds and evens. Does any have a clue why this is happening? I am just using these two inserts. insert into tableNAME values('AA', 'A55', 0, 31, 1, 1, 3) insert into tableNAME values('AA', 'A55', 0, 31, 1, 2, 2) These two rows would be seperated by any other rows already contained in the table. If I add more rows. It lumps them by odds and evens. Thanks
View Replies !
TSQL - Trim String Containing Both Data And Value Into 2 Separate Data Fields
Hi! Need help with this one: I have a column with a string composed by several data. After using REPLACE several times, I get something like the data below, which has (in most of cases) a value and a date. 378 9/05 388 9/05 4/05 1/06 606 1/06 646 76 5/05 100 1/05 118 8/05 129 8/05 9/05 342 05/3 123 1/07 4/06 164 The problem is that I need to get each value alone (to separate columns), in example: Value Date 378 09/2005 388 09/2005 0 04/2005 ... 606 01/2006 and so on... In addittion you can see that sometimes the Value come first or alone, and sometimes the Date come first or alone. I will appreciate any good ideas, Thanks in advance, Aldo.
View Replies !
Separate Fields Into Rows
I have a table with accountid and labtype However inside of the labtype is data separated by semicolons like below GIV;APS;LSL. What would be the best way to return this data as multiple rows such as acctid lab 100 GIV 100 APS 100 LSL Thee may be from 1-10 values in the labtype field
View Replies !
Convert Fields To Rows
I have a table (Not my own, cannot be changed) that has this structure: Table1 ID, field, data ----------------------- 1, fname, john 1, lname, doe 1, address, 123 any st 2, fname, jack 2, lname, sprat 2 address, 345 some st 2, phone, 321-555-1234 I want this: Table2 ID, fname, lname, address, phone -------------------------------------------------- 1, john, doe, 123 any st,, 2, jack, sprat, 345 some st, 321-555-1234 Notes: This query will need to run on a regular schedule. There are over 100 fields, I don't want to do this 100 times: --UPDATE table2 --SET fname = table1.data --FROM table2 INNER JOIN -- table1 ON tablw2.ID = table1.ID --WHERE table.field = fname Any Ideas.
View Replies !
Different Rows Order In Result Grid
Hello! I have two similar servers (hardware) with SQL Server 2000 (SP1) on Win2000 (SP2). (sp_helpsort - default) When I run the same SELECT statement from Query Analyzer on each machine I get different rows order in result Grid (the order of rows stays the same for each machine but different for machine1 and machine2) SELECT table1.a, table1.b, table1.c, table1.d FROM table2, table1, table3 WHERE table2.nTestDefnId = 1034 AND table2.nDefnId = table1.nDefnId AND table3.szClinDiscType = 'X' AND table2.nDiscId = table3.nDiscId What is a reason for such behavior and how I can fix it? Thank you in advance, Elena.
View Replies !
Concatenating Fields Over Multiple Rows
I have a query that produces seperate rows for people, but I want to combine them into one place. I tried doing this in SQL but apparently it's not very easy in SQL Server. You need to loop through a table using cursors. I'm not quite that advanced with SQL Server and was wondering if there might be an easier way just using SSRS. In other words I have a table as such: 1 John Smith 2 Jane Doe 3 Matthew Jones And I'd like to create one textbox that contains the following: "John Smith, Jane Doe, Matthew Jones" I've been drawing a blank. Anyone have any ideas? Levi
View Replies !
Flat File To Table - Rows Out Of Order
Hi, I noticed something strange today. I created a pkg that reads a flat file and writes the rows to a table. In checking the data in the file against what's in the table, I noticed that the rows were inserted in a different order than they are in the file. All the rows appear to be in the table correctly, but they're just not in the same order as in the file. I've never seen this before. But I checked very carefully, and this is indeed the case. Is this normal?? Thanks
View Replies !
How To Change The Order Of Rows In Datatable Randomly?
Hello! I would like randomly change the order of the rows in my table. Is there any way to do that? I also have a question about random generator. Is it possible to get a repeatable sequence of random numbers between 1 and 10 in T-SQL? (for example 2,7,6,5,8,9,3,2,....each state with the same probability). But i need the same sequence every time i run my procedure. I know this is just a pseudo generator. I tried to use function rand([seed]) and change the seed value, but I got some strange results...(floor(rand([seed])*100)) Thanx for any idea, Z.
View Replies !
Delete Many Rows
Hi All,I am designing a purge process for a db that has grown to almost 200GB.My purge process will remove about 1/3 of the 500 million rows spreadover seven tables. Currently there are about 35 indexes defined onthose seven tables. My question is will there be a performance gain bydropping those indexes, doing my purge, and re-creating the indexes. Iam afraid that leaving those indexes in place will create a lot ofextra overhead in my delete statements by having to maintain theindexes. I know that it could take many hours to rebuild the indexesafterward, but I am planning on doing that anyway. The reason that Iwant to know whether I should drop the indexes ahead of time, is I maynot be able to do the entire purge at once and the tables may need tobe accessed between purges. If this occurs, I will need to have thoseindexes in place.So do I drop the indexes before the purge and re-create them later ordo I leave them in place and re-index them afterward?Thanks In Advancep.h.
View Replies !
Delete Rows
Hi I am trying to delete rows in temp1 which are exist in temp2 delete from dbo.temp1 as a inner join temp2 as b on a.regionname=b.regionname and a.servicecode=b.servicecode and a.directioncode=b.directioncode and a.destorigflag=b.destorigflag I am getting error : Incorrect syntax near the keyword 'as'. thanks in advance subash
View Replies !
Delete Rows Where...
Hi there, I have two tables a,b, with the same columns. How do I delete all the data from a that has the same entry as b? Thanks for all the answers
View Replies !
Select Rows From Database Where Fields Are Null
Hi, how do i do a select statement with asp.net to return a record if a field is null. I have the following code: SqlCommand cmd = new SqlCommand("SELECT * FROM Categories WHERE ParentId = @ParentId", cn);.Parameters.Add("@ParentId", SqlDbType.Int).Value = parentId != null ? (object) parentId : DBNull.Value;cn.Open(); The variable parentId is a nullable int. However when i pass in a null value it doesn't return anything even though there are records in the database that have a null value for the ParentId field. Appreciate if someone could tell me what i am doing wrong. Thanks
View Replies !
Converting Rows Records Into Table Fields
Hi I have a table that contains duplicates. Custid, order #s custid,order# 1, 2525 1, 2323 1, 2222 2, 6969 2, 7474 3, 8888 Here what I am trying to do. I want to create a table that contains rows from the above table in this format. custid, order#,order#,order# 1 ,2525 ,2323, 2222 basically, I want to convert the duplicate rows into fields in a new table.
View Replies !
Delete From Related Tables (was &"TSQL Question&")
Hi.. I have 2 tables in MSSQL 2000, a)ISsues and b)IssuesHistory, there are inter-related, the value IssueID are in both table a)ISsues (IssueId is the primary key) and b)IssuesHistory When I wanted to delete some row in table Issue where column Application Type ='Infra: Email' use XX DELETE FROM Issues WHERE Application_Type='Infra: Email' It prompts a Error as the The conflict occurred in database 'XX', table 'IssueHistory', column 'IssueId'. The statement has been terminated. I think it was because IssueId exist in both table a)ISsues and b)IssuesHistory and IssueID is the primary key of Issue, if some IssuesID is deleted, then Issuehistory will become invalid So the method that I think of is the find out what is the IssueID which has Application_Type='Infra: Email' <---Test a---> use XX Select IssueId FROM Issues WHERE Application_Type='Infra: Email' Result= IssueId=2,3, 4 and etc and then <---Test b--> use XX DELETE FROM IssuesHistory WHERE IssueID=2, 3, 4 and etc then we can delete the row in Issues again, but the IssueId output in Test a is really a lot, is there a method to use the result IssueID output in <--Test a--> and auto insert to Where IssueID in <--test b-->
View Replies !
MS SQL Query, What's The Default Order The Rows Returned Are Sorted By?
i have a table and a column called req_id, i have it set as the primary key.. so if i just do SELECT * FROM table, shouldnt the rows returned be sorted by the order that the rows were inserted? this database was improted from an access database.. when i did that in access it would return the rows in sorted order by the order the row was inserted.. but now in MS SQL, its not sorted in that order.. i can't really tell what type of order it's in
View Replies !
Order Of Rows In The Inserted And Deleted Psuedo Tables
I have a table that sometimes has modifications to column(s) comprising the primary key [usually "end_date"]. I need to audit changes on this table, and naturally, turned to after triggers. The problem is that for updates, when the primary key composition changes, I'm not able to relate/join using the primary key - obviously, it no longer matches across INSERTED and DELETED. Now, for a single row update, it's easy to check for updates on PK columns and then deduce what changes were made... So the real question is: are rows in INSERTED and DELETED always in matching order (1st row in INSERTED corresponds to the 1st row in DELETED...)? I don't want to put a surrogate key (GUID nor IDENTITY) on the base table if at all possible. INSERT... SELECT from the inserted/deleted tables into a temp table with identity column is fine, and is what I'm currently doing; I would like MVP or product engineer level confirmation that my ordering assumption is correct. Testing using an identity surrogate key on base table, and selecting from the Ins/del tables, and the temp tables without an order by clause seems to always return in proper order (proper for my purposes). I've tested under SQL 2005 RTM, SP1, SP2, and SP2 "3152". FYI, I've lost the debate that such auditing is better handled by the application, not the database server... Aside: why doesn't the ROW_NUMBER() function allow an empty OVER( ORDER BY() ) clause? Will SQL ever expose an internal row_id, at least in the pseudo tables, so we can work around this situation? Thanks Mike
View Replies !
SQL Query - Delete All Rows Bar The Last 10? Help Please!
Hi, I'm really not very good at SQL sadly, so would really appreciate any help. I'm basically working on a website that has a chatroom. I want it so the chatroom table only holds, say 15 lines of chat, then once a new line of chat is entered the last line in the table is deleted, so the table always has a maximum of just 15 rows. The fields are: MessageID Poster Message DateTime I've tried my best, but just don't know how to do it. I suppose it's something like: SELECT TOP 15 * FROM Chatroom ORDER BY MessageID DESC - this gives me the last 15 rows. Then I need a delete statement to delete the rest?! Sorry, I am very bad at SQL, so any help would be great. This is written in a stored procedure. Can the stored procedure pick up whether there are 15 or more rows in the table, and if so then delete all bar the newest 15 rows of chat? To summarise: I want a stored procedure that checks if 15 or more rows exist, if they do then delete all bar the newest 15 rows. Thanks, Ricky
View Replies !
|
TRACKER
