Updating The Same Column Multiple Times In One Update Statement
Jul 23, 2005
I have a single update statement that updates the same column multiple
times in the same update statement. Basically i have a column that
looks like .1.2.3.4. which are id references that need to be updated
when a group of items is copied. I can successfully do this with
cursors, but am experimenting with a way to do it with a single update
statement.
I have verified that each row being returned to the Update statement
(in an Update..From) is correct, but that after the first update to a
column, the next row that does an update to that same row/column combo
is not using the updated data from the first update to that column.
Does anybody know of a switch or setting that can make this work, or do
I need to stick with the cursors?
Schema detail:
if exists( select * from sysobjects where id = object_id(
'dbo.ScheduleTask') and type = 'U')
drop table dbo.ScheduleTask
go
create table dbo.ScheduleTask (
Id int not null identity(1,1),
IdHierarchy varchar(200) not null,
CopyTaskId int null,
constraint PK_ScheduleTask primary key nonclustered (Id)
)
go
Update query:
Update ScheduleTask Set
ScheduleTask.IdHierarchy = Replace(ScheduleTask.IdHierarchy, '.' +
CAST(TaskCopyData.CopyTaskId as varchar) + '.', '.' +
CAST(TaskCopyData.Id as varchar) + '.')
From
ScheduleTask
INNER JOIN ScheduleTask as TaskCopyData ON
ScheduleTask.CopyTaskId IS NOT NULL AND
TaskCopyData.CopyTaskId IS NOT NULL AND
charindex('.' + CAST(TaskCopyData.CopyTaskId as varchar) + '.',
ScheduleTask.IdHierarchy) > 0
Query used to verify that data going into update is correct:
select
ScheduleTask.Id, TaskCopyData.Id, ScheduleTask.IdHierarchy, '.' +
CAST(TaskCopyData.CopyTaskId as varchar) + '.', '.' +
CAST(TaskCopyData.Id as varchar) + '.'
From
ScheduleTask
INNER JOIN ScheduleTask as TaskCopyData ON
ScheduleTask.CopyTaskId IS NOT NULL AND
TaskCopyData.CopyTaskId IS NOT NULL AND
charindex('.' + CAST(TaskCopyData.CopyTaskId as varchar) + '.',
ScheduleTask.IdHierarchy) > 0
I'm new to SQL Server but not new to SQL because I used it with Oracle. I wonder if it is possible to do this kind of statement on SQL Server:
UPDATE TableX M SET (M.column1, M.column2, M.column3)= (SELECT T.column1, T.column2, G.column3) FROM Table_Y T, Table_Z G WHERE join condition)) WHERE EXISTS (join condition for TableX)
Basically, I'd like to update multiple columns in one statement but for some reason I can not get it to work.
and i have also another table which is namelist that is linked to the masterlist table.. after i search for the record andrew in the table namelist..i updated andrew as 25 and sex is female..now i want reset andrew's record, same to the records that andrew has in the table masterlist..
I am trying to update the same row of the table multiple times. in other words, i am trying to replace the two stings on same row with two different values.
Example: if the column has a string "b" then replace with "B" and if the column has a string "d" then replace with "D" . I can write multiple updates to update it but i was just wondering if it can be done with single UPDATE statement
column before the update : bcdxyz after the update: BcDxyz
I have a column with XML data stored in it. I need to update that column several times with new values for different nodes. I've written a CLR function to update the XML quickly but the update is always based on the initial value of the xmlData column. I was hoping that the subsequent updates would be based on the new data from the prior update (each xmlTable has several newData rows). Do I have to make this a table valued function and use cross apply?
UPDATE xmlTable SET xmlTable.xmlData = Underwriting.UpdateByDynamicValue(xmlTable.xmlData,newData.NodeID,newData.NewValue) FROM xmlTable JOIN newData ON xmlTable.ID = newData.fkXmlTableID
Problem Summary: Merge Statement takes several times longer to execute than equivalent Update, Insert and Delete as separate statements. Why?
I have a relatively large table (about 35,000,000 records, approximately 13 GB uncompressed and 4 GB with page compression - including indexes). A MERGE statement pretty consistently takes two or three minutes to perform an update, insert and delete. At one extreme, updating 82 (yes 82) records took 1 minute, 45 seconds. At the other extreme, updating 100,000 records took about five minutes.When I changed the MERGE to the equivalent separate UPDATE, INSERT & DELETE statements (embedded in an explicit transaction) the entire update took only 17 seconds. The query plans for the separate UPDATE, INSERT & DELETE statements look very similar to the query plan for the combined MERGE. However, all the row count estimates for the MERGE statement are way off.
Obviously, I am going to use the separate UPDATE, INSERT & DELETE statements. The actual query plans for the four statements ( combined MERGE and the separate UPDATE, INSERT & DELETE ) are attached. SQL Code to create the source and target tables and the actual queries themselves are below. I've also included the statistics created by my test run. Nothing else was running on the server when I ran the test.
Server Configuration:
SQL Server 2008 R2 SP1, Enterprise Edition 3 x Quad-Core Xeon Processor Max Degree of Parallelism = 8 148 GB RAM
SQL Code:
Target Table: USE TPS; IF OBJECT_ID('dbo.ParticipantResponse') IS NOT NULL DROP TABLE dbo.ParticipantResponse;
I can't get my form to update my db. Is there something wrong with the update statement? I'm not getting any errors either. I have two check Box Lists: Dim strStep1 As String = ""Dim s As ListItemFor Each s In cblStep1.ItemsIf s.Selected ThenstrStep1 += s.Text + ", "End IfNext Dim strStep2 As String = ""Dim s2 As ListItemFor Each s2 In cblStep2.ItemsIf s2.Selected ThenstrStep2 += s2.Text + ", "End IfNext Dim ConnString As String = ConfigurationManager.AppSettings("ConnectionString")Dim Conn As New SqlConnection(ConnString)Dim CommandString As String = "UPDATE [tblAccess] SET [complete1] = ?, [complete2] = ?" & _"WHERE empName = @empName" Dim Command As New SqlCommand(ConnString, Conn)Command.Parameters.AddWithValue("complete1", strStep1)Command.Parameters.AddWithValue("complete2", strStep2) Any help is appreciated!
TRUNCATE TABLE [Temp_Export]; INSERT INTO [Temp_Export] (
[Code]....
The issue I'm having is that I am getting more records in the VIEW than records updated. What can explain such a discrepancy? I am updating the records based on the PK/FK Temp_Import_ID column, which exists in both tables. where the view would yield more records than those matched by the update statement?
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 need to update multiple columns in a table with multiple condition.
For example, this is my Query
update Table1 set weight= d.weight, stateweight=d.stateweight, overallweight=d.overallweight from (select * from table2)d where table1.state=d.state and table1.month=d.month and table1.year=d.year
If table matches all the three column (State,month,year), it should update only weight column and if it matches(state ,year) it should update only the stateweight column and if it matches(year) it should update only the overallweight column
I can't write an update query for each condition separately because its a huge select
I am trying write a query to update a column of data in my xLegHdr table however the update is based on multiple criteria. I was trying to use "IF..ELSE" statements but that is not working.
I would like to update the "SMiles" column based on the data in the "Dist" column. If the number in the "Dist" column is less than 250 then subtract 25 and multiply it by 1.15 the result should go in the "SMiles" column. If the number is grater than 250 then subtract 40 and multiply by 1.15 and place the result in the "SMiles" column; like so:
UPDATE xLegHdr SET SMiles = IF Dist<250 THEN Round(Dist-25)*1.15) ELSE Round(Dist-40)*1.15) END IF
I am writting a bit of SQL that takes data from one table then inserts it into another one. There is a field that can be any value (and is usually null), but when I insert the value in the new table then I want to execute:
IF table.field>0 then tabl2.field='400'. In other words for every row in the selection that has a field that is greater than 0 then '400' will be put into the new table.
I am not sure if the IF stamement can loop through a number of rows and execute depending on the value of a field in that row??
I want to do an update query like the following:UPDATE tblUserDetails SET DeploymentNameID = 102 WHERE (EmployeeNumber = @selectedusersparam)Is there some simple way to add the @selectedusersparam as value1,value2,value3 etc. or do I have to input it with this type of syntax:UPDATE dbo_tblUserDetails SET dbo_tblUserDetails.DeploymentNameID = 102WHERE (((dbo_tblUserDetails.EmployeeNumber)=value1 Or (dbo_tblUserDetails.EmployeeNumber)=value2));Help appreciated.Many thanks.
I am renovating an existing application and am converting the existing passwords into hashed values using SHA1. I know how to compute the hashed values as a byte array for each record. What I don't know how to do easily is update all of the records i a single call to the database. Normally, I would just do the following:UPDATE HashedPassword = someValue WHERE UserID = 101; UPDATE HashedPassword = someOtherValue WHERE UserID = 102; ...
What I don't know is what someValue and someOtherValue should be. How do I convert my byte array into string representation that SQL will accept? I usually execute multiple statements using Dim oCmd as New SqlCommand(sSQL, MyConn) and then call oCmd.ExecuteNonQuery(). Alternatively, I found the following code that uses the byte array directly but only shows a single statement. How could I use it to execute multiple statements as shown above?'FROM http://aspnet.4guysfromrolla.com/articles/103002-1.2.aspx
'2. Create a command object for the query Dim strSQL as String = _ "INSERT INTO UserAccount(Username,Password) " & _ "VALUES(@Username, @Password)" Dim objCmd as New SqlCommand(strSQL, objConn)
'3. Create parameters Dim paramUsername as SqlParameter paramUsername = New SqlParameter("@Username", SqlDbType.VarChar, 25) paramUsername.Value = txtUsername.Text objCmd.Parameters.Add(paramUsername)
Dim paramPwd as SqlParameter paramPwd = New SqlParameter("@Password", SqlDbType.Binary, 16) paramPwd.Value = hashedBytes objCmd.Parameters.Add(paramPwd)
'Insert the records into the database objConn.Open() objCmd.ExecuteNonQuery() objConn.Close()
It is possible to alter multiple columns within a single alter table statement? I have got the following URL that tells it is not possible to alter multiple columns within in signle alert table statement. http://www.blogcoward.com/archive/2005/05/09/234.aspx[^] Does anyone know about that?
I have multiple product tables with a "ListPrice" column. I want to use a temporary table that contains "ProductID" and "ListPrice" columns and execute a stored procedure that will update the "ListPrice" column in all product tables with the "ListPrice" from temporary table where "ProductID" from temporary table matches "ProductID" in product tables.
Is there a way to set a field value to the column default in an update statement? Eg. UPDATE Table2 SET field1 = DefaultValue where DefaultValue is the field1 column default in the table definition. The reason I need to do this is when I delete a record from Table1, I need to set the foreign key in Table2 to the default (I don't want to delete the record in Table2, just want to set the key to a default key). I could hard-code the default value in the stored procedure but I think that's just not clean. If I create a new instance of the DB and the default value changes, I'd need to change the stored procedure(s). Just not clean... To avoid a drawn-out discussion, there are reasons why I can't setup a relationship between the two tables and use "ON DELETE SET DEFAULT". Any info greatly appreciated.
I'm trying to write a script that would only update a column if it exists.
This is what I tried first:
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Enrollment' AND COLUMN_NAME = 'nosuchfield') BEGIN UPDATE dbo.Enrollment SET nosuchfield='666' END
And got the following error:
Server: Msg 207, Level 16, State 1, Line 1 Invalid column name 'nosuchfield'.
I'm curious why MS-SQL would do syntax checking in this case. I've used this type of check with ALTER TABLE ADD COLUMN commands before and it worked perfectly fine.
The only way I can think of to get around this is with:
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'Enrollment' AND COLUMN_NAME = 'nosuchfield') BEGIN declare @sql nvarchar(100) SET @sql = N'UPDATE dbo.Enrollment SET nosuchfield=''666''' execute sp_executesql @sql END
which looks a bit awkward. Is there a better way to accomplish this?
I am trying to query the Topics in my discussion forum...The Topic contains a "last_poster_id" and a "author_id" I need the username and userid for both "last_poster_id" and "author_id" in the table "aspnet_Users"How do I do this?I would guess I need to use sub select statements. Can someone help me?
Set publisher database table column to 1. Replicate table to subscriber (column is also 1 on subscriber once it is replicated). Set subscriber database column to 2. Update publisher database column to 1 (even though it is already 1). Change is NOT replicated to subscriber (it stays at 2).
This means that replication is comparing the insert statement to its current value in the database. Is there any way to change this behavior? We want the the column to replicate to the subscriber.
My current proc updates(updates using joins of two or three tables) millions of records as per the condition provided for each department.
However, when the proc fails it writes to a ErrorTable, ERROR_MESSAGE(), ERROR_SEVERITY() and which department has failed.
Since the records are updated keeping the selected departments in loop, I get the department in a temp variable.Now, I was asked to log the specific record where the failure was occured.Something like log the identity column value or primary key value which record has failed.
I am writing a query and have the bulk of it already written.
I am looking at a table that contains customer orders. There is a column named Customer_Order.Status Available values for this column is R, F, H, and C.
I'd like for my query to return all lines that have the value R, F, H.
My where clause is written like this
WHERE CUSTOMER_ORDER.SITE_ID = 'XXX' AND CUSTOMER_ORDER.STATUS = ('R','H','F')
Hi,I have table with three columns as belowtable name:expNo(int) name(char) refno(int)I have data as belowNo name refno1 a2 b3 cI need to update the refno with no values I write a query as belowupdate exp set refno=(select no from exp)when i run the query i got error asSubquery returned more than 1 value. This is not permitted when thesubquery follows =, !=, <, <= , >, >= or when the subquery is used asan expression.I need to update one colum with other column value.What is the correct query for this ?Thanks,Mani
I have a table #vert where I have value column. This data needs to be updated into two channel columns in #hori table based on channel number in #vert table.
CREATE TABLE #Vert (FILTER VARCHAR(3), CHANNEL TINYINT, VALUE TINYINT) INSERT #Vert Values('ABC', 1, 22),('ABC', 2, 32),('BBC', 1, 12),('BBC', 2, 23),('CAB', 1, 33),('CAB', 2, 44) -- COMBINATION OF FILTER AND CHANNEL IS UNIQUE CREATE TABLE #Hori (FILTER VARCHAR(3), CHANNEL1 TINYINT, CHANNEL2 TINYINT) INSERT #Hori Values ('ABC', NULL, NULL),('BBC', NULL, NULL),('CAB', NULL, NULL) -- FILTER IS UNIQUE IN #HORI TABLE
One way to achieve this is to write two update statements. After update, the output you see is my desired output
UPDATE H SET CHANNEL1= VALUE FROM #Hori H JOIN #Vert V ON V.FILTER=H.FILTER WHERE V.CHANNEL=1 -- updates only channel1 UPDATE H SET CHANNEL2= VALUE FROM #Hori H JOIN #Vert V ON V.FILTER=H.FILTER WHERE V.CHANNEL=2 -- updates only channel2 SELECT * FROM #Hori -- this is desired output
my channels number grows in #vert table like 1,2,3,4...and so Channel3, Channel4....so on in #hori table. So I cannot keep writing too many update statements. One other way is to pivot #vert table and do single update into #hori table.
I have a situation where I run the same taks multiple times during the execution. I would like to have one task which runs every time, instead of duplicating the task over and over 14 times in my script.
Basically, it is an audit log, which I set variables and then insert into a SQL table the variables.
I would like to do this:
Task1 ------Success-----> Set Vars -----Success--> Log | Task2 ------Success-----> Set Vars -----Success-| (do the Log task again) | Task3 -------Success-----> Set Vars -----Success-| (do the Log task again) | etc
This works, however, I have to duplicate Log over and over and over. No OR does not work, because it still only executes the Log task once.
Another option I thought of, but cannot find a way to implement is: Make the Log task "disabled" with no dependencies, then in the Set Vars script, enable and execute Log and disable again.
After doing some research it seems like you can only push the same table once using rda.push -- is this correct? If yes, are there any other alternatives for saving changes to the table back to SQL Server aside from merge replication?
One idea I am toying with is to pull the tracked table with 0 records, save changes to the tracked table, push, drop table and pull, repeating this process everytime I push the data. Wondering is somebody has any advice?
I have a four tables called plandescription, plandetail and analysisdetail. The table plandescription has the columns DetailQuestionID which is the primary and identity column and a QuestionDescription column.
The table plandetail consists of the column PlanDetailID which the primary and identity column, DetailQuestionID which is the foreign key attribute of plandescription table and a planID column.
The third table analysisdetail consists of a analysisID which the primary and identity column, PlanDetailID which is the foreign key attribute of plandetail table and a scenario.
Below is the schema of the three tables
I have a two web form that will insert, update and delete data into these three tables in a two transaction. One web form will perform CRUD operations in plandescription and plandetail table. When the user inserts QuestionDescription and planid in this web form, I will insert the QuestionDescription Value in the plandescription table and will generate a DetailQuestionID value and this value is fed to the plandetail table with the planid. Here I will generate a PlanDetailID.
Once this transaction is done, I will show the second web form in which the user enters the scenario and this will be mapped with the plandescription using the PlanDetailID.
This schema cannot be changes as this is the client requirement. When I insert values I don’t have any problem. However when I update existing data, I need to delete existing PlanDetailID in the plandetail table and recreate PlanDetailID data for that DetailQuestionID and planID. This is because, the user will be adding or deleting a planID associated with the QuestionDescription.
Once I recreate PlanDetailID for that DetailQuestionID and planID, I need to update the old PlanDetailID with the new PlanDetailID in the third table analysisdetail for the associated analysisID.
I created a #Temp table called #DetailTable to insert the values analysisID, planid and old PlanDetailID and new PlanDetailID so that I can have them in update statement once I delete the data from plandetail table for that PlanDetailID.
Then I deleted the plandetailid from the plandetail table and recreate PlanDetailID for that DetailQuestionID. During my recreation I fetched the new PlanDetailID’s created into another temp table called #InsertedRows
After this I am running a while loop to update the temp table #DetailTable with the newly created PlanDetailID for the appropriate planID’s. The problem is here. When I have the same number of planID’s for example 2 planID’s 1,2 I will have only two old PlanDetailID and new PlanDetailID for that planID and analysisID.But When I add a new PlanID or remove a existing planID I am getting null value for that newly added or deleted planID. This is affecting my update statement of analysisdetail table as PlanDetailID cannot be null.
I tried to remove the Null value from the #DetailTable by running the update statement of analysis detail in a while loop however its not working.
DECLARE @categoryid INT = 8 DECLARE @DetailQuestionID INT = 1380 /*------- I need the query to run for the below three data. Here i'm updating my planids that already exists in my database*/ DECLARE @planids VARCHAR(MAX) = '2,4,5'
Hey all, I am still pretty new to all of this and I am having problems accessing a the same DB twice in my page. I want to pull information once in one spot, but then pull different information in a different spot on the page. Anyway, in the first spot, I have the following code: Dim conString As String = WebConfigurationManager.ConnectionStrings("DataConn").ConnectionString Dim con As New SqlConnection(conString)Dim cmd As New SqlCommand("Select Team1.TeamID as Team1ID, Team1.TeamName as HomeTeam, Team2.TeamID as Team2ID, Team2.TeamName as AwayTeam, Football_Schedule.ScheduleID as ScheduleID from (Select * from Football_Teams) as Team1, (Select * from Football_Teams) as Team2, Football_Schedule where Football_schedule.team1 = Team1.teamid AND Football_Schedule.team2 = Team2.teamid order by Football_Schedule.WeekNum, Team1.TeamName, Team2.Teamname", con)Using con con.Open() Dim RS As SqlDataReader = cmd.ExecuteReader() While RS.Read() blah blah blah End While End Using
Then in my other spot on the page I have the following: Dim cmdresults As New SqlCommand("Select Users.Firstname, sum(PointsID) as TotalPoints from Football_Input, Football_Schedule, Users where Football_Input.TeamID = Football_Schedule.winID and users.userid = Football_Input.UserID Group by Users.firstname") Using con con.Open()Dim RD As SqlDataReader = cmdresults.ExecuteReader() While RD.Read()%> <tr> <td><%=RD%> </td> </tr> <%End While End Using When I try to execute I get this error, "ExecuteReader: Connection property has not been initialized." on the following line, " RD As SqlDataReader = cmdresults.ExecuteReader()" Any ideas? If possible a little explanation on how multiple connections to the same database work would be nice just for future reference. Thanks in advance!!, Chris