INSERT INTO #TEMP VALUES(1,'James',NULL)
INSERT INTO #TEMP VALUES(1,'James','George')
INSERT INTO #TEMP VALUES(1,'James','Vikas')
INSERT INTO #TEMP2(ID) VALUES(1)
INSERT INTO #TEMP2(ID) VALUES(1)
INSERT INTO #TEMP2(ID) VALUES(1)
Now i want to get the result as
;WITH CTE AS(
SELECT ROW_NUMBER() OVER(PARTITION BY Id ORDER BY ID ASC) AS RowNum ,*
FROM #TEMP
)
SELECT CASE WHEN RowNum=1 THEN CREATEDBY
WHEN RowNum > 1 THEN ModfiedBy
END
FROM CTE
But when i convert this select to update, i am missing something...
My update is
;WITH CTE AS(
SELECT ROW_NUMBER() OVER(PARTITION BY Id ORDER BY ID ASC) AS RowNum ,*
FROM #TEMP
)
UPDATE #TEMP2
SET SearchedBy =CASEWHEN RowNum=1 THEN CREATEDBY
WHEN RowNum > 1 THEN ModfiedBy
END
FROM CTE
WHERE #TEMP2.ID=CTE.ID
In answering this question you could just tell me what you think would work rather than trying to test it.
The following query works correctly and returns rows with pairs of values repid and repid2. I need loop through a large table and update *its* repid2 with rm_repid2 for any rows where its repid value equals rm_repid but only for the pairs that result in the below query
SELECT rm.repid AS rm_repid ,rm.repid2 AS rm_repid2 ,rm.id2contact ,r.repid AS r_repid ,r.prim AS r_prim FROM sfrep r JOIN sfrepmst rm ON r.repid=rm.repid2 WHERE rm.repid IN ( 'TEST01' ,'TEST02' )
Returns
rm_repid rm_repid2 id2contact r_repid -------- --------- ---------------------------------------- ------- TEST01 NEW01 John Smith NEW01 TEST02 NEW02 Ken Roberts NEW02
I am trying to write an update t-SQL statement from the following select statement:
SELECT EduNextContacts.ssn FROM EduNextContacts Left JOIN EduContactsAuditChanges ON EduNextContacts.ssn = EduContactsAuditChanges.ssn AND EduNextContacts.campaign_code = EduContactsAuditChanges.campaign_code GROUP BY EduNextContacts.ssn HAVING(SUM(CASE EduContactsAuditChanges.release_code WHEN '103' THEN 1 ELSE 0 END) + SUM(CASE EduContactsAuditChanges.release_code WHEN '102' THEN 1 ELSE 0 END) >= 2)
I tried many versions of writing my update statement with no luck. My most recent version is as follows:
UPDATE EduNextContacts
SET EduNextContacts.overLMLimit = 'Y'
FROM EduNextContacts Left JOIN
EduContactsAuditChanges ON EduNextContacts.ssn = EduContactsAuditChanges.ssn AND
HAVING(SUM(CASE EduContactsAuditChanges.release_code WHEN '103' THEN 1 ELSE 0 END) +
SUM(CASE EduContactsAuditChanges.release_code WHEN '102' THEN 1 ELSE 0 END) >= 2))
And gives the following error:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Can anyone help shed some light on how to make an update statement from my SELECT query above?
I am currently having this problem with gridview and detailview. When I drag either onto the page and set my select statement to pick from one table and then update that data through the gridview (lets say), the update works perfectly. My problem is that the table I am pulling data from is mainly foreign keys. So in order to hide the number values of the foreign keys, I select the string value columns from the tables that contain the primary keys. I then use INNER JOIN in my SELECT so that I only get the data that pertains to the user I am looking to list and edit. I run the "test query" and everything I need shows up as I want it. I then go back to the gridview and change the fields which are foreign keys to templates. When I edit the templates I bind the field that contains the string value of the given foreign key to the template. This works great, because now the user will see string representation instead of the ID numbers that coinside with the string value. So I run my webpage and everything show up as I want it to, all the data is correct and I get no errors. I then click edit (as I have checked the "enable editing" box) and the gridview changes to edit mode. I make my changes and then select "update." When the page refreshes, and the gridview returns, the data is not updated and the original data is shown. I am sorry for so much typing, but I want to be as clear as possible with what I am doing. The only thing I can see being the issue is that when I setup my SELECT and FROM to contain fields from multiple tables, the UPDATE then does not work. When I remove all of my JOIN's and go back to foreign keys and one table the update works again. Below is what I have for my SQL statements:------------------------------------------------------------------------------------------------------------------------------------- SELECT:SELECT People.FirstName, People.LastName, People.FullName, People.PropertyID, People.InviteTypeID, People.RSVP, People.Wheelchair, Property.[House/Day Hab], InviteType.InviteTypeName FROM (InviteType INNER JOIN (Property INNER JOIN People ON Property.PropertyID = People.PropertyID) ON InviteType.InviteTypeID = People.InviteTypeID) WHERE (People.PersonID = ?)UPDATE:UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ? WHERE [PersonID] = ? ---------------------------------------------------------------------------------------------------------------------------------------The only fields I want to update are in [People]. My WHERE is based on a control that I use to select a person from a drop down list. If I run the test query for the update while setting up my data source the query will update the record in the database. It is when I try to make the update from the gridview that the data is not changed. If anything is not clear please let me know and I will clarify as much as I can. This is my first project using ASP and working with databases so I am completely learning as I go. I took some database courses in college but I have never interacted with them with a web based front end. Any help will be greatly appreciated.Thank you in advance for any time, help, and/or advice you can give.Brian
I would like a simple statement to select some rows and then update them. I would like to use SERIALIZABLE locking, will this do the trick. Below is my TSQL
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE begin tran select * from patient_medication_dispersal_ where ddate = convert(char(10),getdate(),101)
update patient_medication_dispersal_ set rec_status = 1 where ddate = convert(char(10),getdate(),101) and rec_status =1
Hi all, have a pretty simple query. but am new to workin in multiple tables. This statement shows Items in table1 that exists in table2 that match a filter.
I would like to change this query to update the TOBEDELETED column to 'F' based on the same column and filter match.
SELECT ITEMS.ITEMNO, ITEMS.TOBEDELETED FROM ITEMS INNER JOIN QLOG ON ITEMS.ITEMNO=QLOG.SOURCEID WHERE QLOG.SOURCE= 'ITEMS'
Could anyone help med with a select statement with a join between totables. It is to be used in a OLAP cube.I Havde table LedgerBudget and Table Admin. In table admin I can setupa from and to date and also a budgetmodel.The admin have ONE record per OLAP cube.The statement below works fine if I have stated a budgetmodel in tableAdmin.But if no budetmodel stated in table Admin, I want the statement toselect every ledgerbudget with active = 1 and allocatemethod = 0Could anyone help me with this.SELECT LTRIM(dbo.LEDGERBUDGET.ACCOUNTNUM) AS ACCOUNT_ID,dbo.LEDGERBUDGET.STARTDATE AS TRANSDATE, - dbo.LEDGERBUDGET.AMOUNT ASBUDGETFROM dbo.LEDGERBUDGET INNER JOINdbo.ADMIN ON dbo.LEDGERBUDGET.STARTDATE >=dbo.ADMIN.FROMDATE ANDdbo.LEDGERBUDGET.STARTDATE <= dbo.ADMIN.TODATEANDdbo.LEDGERBUDGET.MODELNUM =dbo.ADMIN.BUDGETMODELIDWHERE (dbo.LEDGERBUDGET.ACTIVE = 1) AND(dbo.LEDGERBUDGET.ALLOCATEMETHOD = 0)BR/Thanks
This is probably a very simple question but i would appreciate some helpwith the correct syntax for and update stored procedureI have created user form that allows the user to update the name and address fields in a datatable called customers based on the input value customer ID = ( datatable/Customers)customerIDI have got this far and then got lost:Create SP_UpdateCustomer(@customerID, @name, @address)As Update customers ( name, address)Where customerID = @customerID GOCould anyone tell me what the correct sntax should be.many thanksMartin
This (demo) statement is fine in Access, and so far as I can see, shouldbe OK in SQL Server.But Enterprise Manager barfs at the final bracket. Can anyone helpplease?select sum(field1) as sum1, sum(field2) as sum2 from(SELECT * from test where id < 3unionSELECT * from test where id 2)In fact, I can reduce it to :-select * from(SELECT * from test)with the same effect - clearly I just need telling :-)cheers,Jim--Jima Yorkshire polymoth
Hi everyone..i m new to this field.. can anyone explain me with simple example onhow to insert,update,select data from the sqldatabase? i m using vwd 2005 express edition along with sql express edition. plz explain the simple example with code (C#) including how to pass connection strings etc.thank you.jack.
Hello all, I have two mult-value parameters in my report. Both of them working with selecting one or more values. But, when I test using "(Select All)" values for both parameters , only one parameter works. The "available values" for these two parameters are both from the data set.
select distinct ProductType from Product order by ProductType
I am importing data from a legacy system. The date column is a varchar and in the destination DB it is a proper DateTime.
That said I have invalid data and need to clean / accommodate for it as I go. What I need is if the date is valid then insert it, if not then ue a default of '01/01/1900' I am doing the following and it is not working.
CASE WHEN isDATE(RatifiedDate) = 1 THEN RatifiedDate WHEN isDate(RatifiedDate) = 0 THEN '01/01/1900' END as RatDate
SQL Ver: 2008 (not r2) Problem: The following code returns correct results when moving variable declarations and update statement outside a stored procedure, but fails to return a value other than zero for the "COMPANY TOTAL" records. The "DEPT TOTAL" result works fine both in and outside the sp.This may have to do with handling NULL values since I was getting warning message earlier involving a value being eliminated by an aggregate function involving a NULL. I only got this message when running inside the sp, not when running standalone. I wrapped the values inside the SUM functions with an ISNULL, and now return a zero rather than NULL for the "COMPANY TOTAL" records when running inside SP.All variable values are correct when running.
SQL CODE: DECLARE @WIPMonthCurrent date = (SELECT TOP 1 WIPMonth FROM budxcWIPMonths WHERE ActiveWIPPeriod = 'Y') select @WIPMonthCurrent as WIPMonthCurrent
I have a table that is returning rows from a table query. It seems I have done it before but I cannot seem to get the right procedure to obtain the values. I will paste in the code below in which you will see my bad attempts at accomplishing what I need.
Dim uid As String Dim pw As String Dim em As String, fn, ln, mi As String Dim par As String Dim Field, n, j As Integer Dim JJ As Integer
Hello,I created a formview in a web page. The data are in a sql server express database.With this form, I can to create a new data, I delete it but I can't to modify the data.The app_data folder is ready to write data; the datakeynames element in formview web control declared. I replace the automated query created by VS 2005 by a strored procedure to see if the problem solved.The problem is the same with an update query or a update stored procedure...Have you an idea, please.Than you for your help.Regards.
Im working with a detailsview and when I try to edit something and then update, the changes are not saved. I have 2 tables ("[etpi.admin].Ocorrencias" and "[etpi.admin].SMS") that store the data that Im trying to change. Since Im having problems with the name of tables, Im coding it manually, using SQL server management studio and VWD. I believe my code can be wrong (Im new to vwd and C# world), so here it is: UpdateCommand="UPDATE [etpi.admin].Ocorrencias SET [Status_Ocor] = @Status_Ocor, [Percentual] = @Percentual, [IDPriori] = @IDPriori, [Abertura] = @Abertura, [IDTecRes] = @IDTecRes, [Area] = @Area, [CodEquip] = @CodEquip, [Descricao] = @Descricao, [Destinatario] = @Destinatario, [Data_Implanta] = @Data_Implanta WHERE [etpi.admin].Ocorrencias.IDOcorre = @IDOcorre UPDATE [etpi.admin].SMS SET [idSMS] = @idSMSWHERE [etpi.admin].SMS.IDOcorre = @IDOcorre"> <UpdateParameters><asp:Parameter Name="idSMS" Type="Int32" /><asp:Parameter Name="Status_Ocor" Type="String" /><asp:Parameter Name="Percentual" Type="Int32" /><asp:Parameter Name="IDPriori" Type="Int32" /><asp:Parameter Name="Abertura" Type="DateTime" /><asp:Parameter Name="IDTecRes" Type="Int32" /><asp:Parameter Name="Area" Type="String" /><asp:Parameter Name="CodEquip" Type="Int32" /><asp:Parameter Name="Descricao" Type="String" /><asp:Parameter Name="Destinatario" Type="String" /><asp:Parameter Name="Data_Implanta" Type="DateTime" /><asp:Parameter Name="IDOcorre" Type="Int32" /></UpdateParameters> Thx.
hi all, i have created a gridview with the select,delete and insert commands working properly. but the update command does not work. when i edit a column and click the update button, it generates no errors but does not save the changes. it just brings back the original values. i dont know wats missing. can anyone help me? this is part of my code:UpdateCommand="UPDATE [test101] SET Surname=@Surname, Names=@Names,Registration=@Registration,[Course code]=@Course_code,Grade=@Grade WHERE ID=@ID1 "<UpdateParameters> <asp:Parameter Name="Surname" Type=String /> <asp:Parameter Name="Names" Type=String /> <asp:Parameter Name="Registration" Type=String /> <asp:Parameter Name="Course_code" Type=String /> <asp:Parameter Name="Grade" Type=Int32 /> <asp:Parameter Name="ID1" Type=Int32 /> </UpdateParameters>
I have a temp table with a column of all the same number. When I run my select statement I need it to bring back all the other data and just the single instance of the column with all the same numbers. How would I do this in my select statement?
For example, here is my table:
Here1One There1Two Up1Three Down1Four Back1Five
I need to bring back everything from column 1 and 3, but just the single instance of 1 from column 2 since it contains all the same number. All the data is collected in a single select statement.
I'm a newbie so I'll explain what I'm trying to achieve the best I can ...
I'd like to essentially loop through a SQL table to display the correct results. The workflow is the user query's the database and returns records (by property ID). In the return there are duplicate records being returned - in this case, two property owners returned with the same property ID.
How would I loop through the SQL statement in the application (code) to identify when the property id's are the same and display only one owner for that property?
Code Block SELECT tce.TimeCardID, tce.TimeCardExpenseID, tc.DateCreated, e.LoginID, e.FirstName + ' ' + e.LastName AS FullName, tce.ExpenseAmount, tce.ExpenseDescription, op.ProjectName, op.ProjectDescription, ec.ExpenseCode FROM OPS_TimeCards tc JOIN OPS_Employees e ON e.EmployeeID = tc.EmployeeID JOIN OPS_TimeCardExpenses tce ON tc.TimeCardID = tce.TimeCardID Join OPS_Projects op ON op.ProjectID = tce.ProjectID Join OPS_ExpenseCodes ec ON ec.ExpenseCodeID = tce.ExpenseCodeID WHERE e.LoginID = 'jross' ORDER BY tc.DateCreated DESC
this query returns me the correct data....but i need to tweak the query so it does not duplicate rows....My tce.TimeCardID is a PK in its table and so is TimeCardExpenseID...but the problem is U can have many TimeCardExpenseID's for one timecard so my results look like
TimeCardID TimeCardExpenseID 1 2 1 3 1 4
I want my query to return the "TimeCardID" but i just want that one ID to represent all the TimeCardExpenseID's...but i can not get it to work and have no clue....
Could some help me out an tell me why this wont run.
I think I got an error at the bottom
USE InfoPathBudget GO UPDATE TOSS SET ytdexpenses = SUM(monthlyExpenses) SET ytdBudgeted = SUM(monthlyBudgeted) SET ytdCapitalExpenses = SUM(monthlyCapitalExpenses) SET ytdCapitalBudgeted = SUM(monthlyCapitalBudgeted)
I got this when I ran it Server: Msg 157, Level 15, State 1, Line 2 An aggregate may not appear in the set list of an UPDATE statement.
My question is how can I query tblTargetRatio table to return correct record for the following cases:
1 EmployeeID = 1 and Date = 03/12/2012 (Expecting Ratio = 8) 2 EmployeeID = 1 and Date = 10/10/2012 (Expecting Ratio = 7) 3 EmployeeID = 1 and Date = 08/12/2012 (Expecting Ratio = 5)
Hi - I'm in a situation with a very large table, and trying to run an update that, any way I've approached it so far, seems to be taking unnacceptably long to run. Table has about 20 million rows, looks something like this:
ID - int, identity Type - varchar(50) PurchaseNumber - varchar(50) SalesAmount - Money
ID Type PurchaseNumber SalesAmount 1 A 3834AA38384 20.32$ 2 B 3834AA38384 11837.32$ 3 C 3834AA38384 666.32$ 4 C 887DF88U01H 23423.32$ 5 A 887DF88U01H 12.32$ 6 B OI83999FH28 4747.1$ 7 D 38438495985 9384.6 8 E 02939DDJJWI 22.22$ 9 F 07939SDFDF2 33.33$
The goal of the update is to make the [Type] uniform across [PurchaseNumbers], according to the max sales amount. For each PurchaseNumber a, set the type = the type of the row that has the MAX salesAmount. If there is only one entry for PurchaseNumber, leave the type alone. Expected update after completion would look like this:
ID Type PurchaseNumber SalesAmount 1 B 3834AA38384 20.32$ 2 B 3834AA38384 11837.32$ 3 B 3834AA38384 666.32$ 4 C 887DF88U01H 23423.32$ 5 C 887DF88U01H 12.32$ 6 B OI83999FH28 4747.1$ 7 D 38438495985 9384.6 8 E 02939DDJJWI 22.22$ 9 F 07939SDFDF2 33.33$
I got this out of a warehouse, and it definitely isn't normalized well. Was considering breaking down into a better model, but I'm not yet sure if that would make the update easier.
I've been approaching this with sub-queries (finding all the PurchaseNumbers with more then one entry, then the max sales purchase of that purchase Number, then the type of that purchase number and sales amount to update all of that purchase number) but this not only ends up a little messy, but also very slow.
The only other detail that may be important is that out of the 20 million total rows, about 19.5 million purchaseNumbers are unique. So, really, there are only about 500k rows I actually have to update.
I've thought of a few ways to make this work, but none of them seem fast and wanted to see if anyone had a pointer. Thanks!
Hi, I have one column of data which is 15.678 but in the excel, i format it to 15.68 ( two decimal place, so in excel i should see 15.68), when i am trying to import the data from excel to sql server by using odbc connection, it still getting 15.678, how can i get the data from 15.678 to 15.68 ( what i see is wat i get). Thanks for help.