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

View 8 Replies


ADVERTISEMENT

Update Statement - Correlation Name Is Specified Multiple Times In FROM Clause

Jun 11, 2014

I have this update statement that I need to have joined by MSA and spec.

I keep getting an error.
Msg 1011, Level 16, State 1, Line 3
The correlation name 't1' is specified multiple times in a FROM clause.

Here is my statement below. How can I change this?

UPDATE MSA
SET [Count on Billed Charges] = (Select Count(distinct[PCS Number])
From MonthEnds.dbo.vw_All_Products t1
Inner Join MonthEnds.dbo.vw_All_Products t1 on t1.[MSA Group] = t2.[MSA Group] and t1.[Spec 1] = t2.[Spec 1])

View 3 Replies View Related

Multiple Column Update Statement

Aug 25, 2006

Hi,

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.

What would be the equivalent on SQL Server?

Thanks for the help,

Laszlo M

View 4 Replies View Related

Update Multiple Column Set Select Statement

Nov 14, 2013

i have a table named masterlist wherein the columns are :

name-----age------sex
andrew---19-------male
trisha---23------female

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..

View 3 Replies View Related

Transact SQL :: Update Same Row Multiple Times

Nov 9, 2015

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

Following is the sample data

declare @t1 table
(
    c1 varchar(5),
    c2 varchar(10),
    c3 varchar(5)
)
insert into @t1 values ('a','bcdxyz','efg')

[Code] ....

View 5 Replies View Related

Transact SQL :: Need To Update That Column Several Times With New Values For Different Nodes

May 28, 2015

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

View 2 Replies View Related

SQL Server 2008 :: Merge Statement Takes Several Times Longer To Execute Than Equivalent Update

Jun 20, 2013

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;

[code]....

View 9 Replies View Related

Update Statement Not Updating

Jun 26, 2006

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!

View 9 Replies View Related

Update Statement Not Updating All Records

Jul 27, 2012

I have the following code:

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?

View 6 Replies View Related

Multiple Tables Used In Select Statement Makes My Update Statement Not Work?

Aug 29, 2006

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 

View 5 Replies View Related

SQL Server 2008 :: How To Update Multiple Column With Multiple Condition

Feb 25, 2015

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

View 7 Replies View Related

Multiple Update Statement

Nov 14, 2000

If I want to update 2 columns at 1 time with a where clause
like

update table a
set column1 = 8 and
set column2 = 9
where id = 10

I know you can't use the and statement, what is the correct syntax?

View 1 Replies View Related

UPDATE Statement With Multiple Criteria

Apr 5, 2006

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

Any ideas?

View 1 Replies View Related

IF Statement To Update Multiple Rows

Oct 12, 2007



Hi,

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??

Thanks

View 7 Replies View Related

Update Statement To Include Multiple Parameterised Input

Oct 18, 2006

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.

View 5 Replies View Related

Update Multiple Varbinary Records With Single Sql Statement

Jan 16, 2007

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()
 

View 1 Replies View Related

Update Multiple Columns In One Table With Case Statement

Nov 15, 2013

I want to update multiple column in one table using with case statement. i need query pls..

stdidnamesubject result marks
1 arun chemistry pass 55
2 alias maths pass 70
3 babau history pass 55
4 basha hindi NULL NULL
5 hussain hindi NULL nULL
6 chandru chemistry NULLNULL
7 mani hindi NULLNULL
8 rajesh history NULLNULL
9 rama chemistry NULLNULL
10 laxman maths NULLNULL

View 2 Replies View Related

Transact SQL :: Update Statement To Include Multiple Records At Once

Apr 20, 2015

I have this update statement that works for one record. How do I write it to include multiple records at once. Please see sample below.

update
mklopt
set
 FRMDAT =
'12/31/2014'
where
 JOBCOD =
'PH14789' 

I also want to include the following instead of running it one at a time

PH17523    
PH17524    
PH17525    
PH17553    
PH17555    
PH17556    
PH17557    
PH17558    
PH17571    
PH17573    
PH17574    
PH17575    
PH17576    
PH17577    
PH1757

View 9 Replies View Related

To Alter Multiple Column With Single Statement

May 5, 2008

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?


Thanks,
Mushq

View 4 Replies View Related

Update Column In Multiple Tables

Jun 22, 2004

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.

I am new at writing stored procedures.

View 2 Replies View Related

SQL Update Statement Set Field Value To Column Default

May 17, 2008

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.

View 9 Replies View Related

Running An UPDATE Statement Only If A Column Exists

Nov 1, 2007

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?

View 3 Replies View Related

Querying Multiple Tables Multiple Times

May 31, 2007

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? 

View 12 Replies View Related

SQL 2012 :: Get Identity Column Value When Update Statement Fails

Sep 9, 2015

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.

View 4 Replies View Related

Replicate Column To Subscriber - Even It If Is Unchanged In UPDATE Statement

Jan 12, 2007

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.

View 9 Replies View Related

Transact SQL :: Get Identity Column Value When Update Statement Fails?

Sep 9, 2015

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.

View 2 Replies View Related

Transact SQL :: Returning Multiple Values From Column In Select Statement?

Nov 26, 2015

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')

I know I'm missing something....

View 3 Replies View Related

Update One Colum With Other Column Value In Same Table Using Update Table Statement

Jun 14, 2007

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

View 3 Replies View Related

SQL Server 2014 :: How To Update Values Based On Column Into Multiple Columns In Another Table

Jul 31, 2015

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.

View 5 Replies View Related

Run A Task Multiple Times

Oct 20, 2006

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.

Any ideas?

View 8 Replies View Related

Rda.push Multiple Times?

Oct 14, 2007



Hello,

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?

Thanks!

View 1 Replies View Related

SQL Server 2012 :: Delete / Recreate Identity Column / Fetch Newly Created Values In Update Statement?

Jul 25, 2015

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'

[code].....

View 2 Replies View Related

How To Connect To A DB Multiple Times Through Page

Dec 21, 2007

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

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved