Updating Tables From A Table Variable

Feb 14, 2005

I have a table variable into which I insert the results of a select statement. Based on the records held in the table variable I then want to update a field in one table and insert the records in the table variable into another table.

This works fine in a self contained test:

declare @table table(electionchangeid int)
declare @anothertable table(ID int)

insert into @table(electionchangeid) values(1036276)

update electionchange
set exportdate = GETDATE()
from electionchange ec
join @table t on t.electionchangeid = ec.electionchangeid

insert into @anothertable
select * from @table

But does not work within my sp .... (see next post).

It doesn't generate an error. It just doesn't update or insert any records. I would think that it was a scope issue, except that I can do a select on the table variable and see that it does contain records.

I would be very interested to hear people's thoughts on this.

Regards
Emma

View 1 Replies


ADVERTISEMENT

Declaring A Table Variable Within A Select Table Joined To Other Select Tables In Query

Oct 15, 2007

Hello,

I hope someone can answer this, I'm not even sure where to start looking for documentation on this. The SQL query I'm referencing is included at the bottom of this post.

I have a query with 3 select statements joined together like tables. It works great, except for the fact that I need to declare a variable and make it a table within two of those 3. The example is below. You'll see that I have three select statements made into tables A, B, and C, and that table A has a variable @years, which is a table.

This works when I just run table A by itself, but when I execute the entire query, I get an error about the "declare" keyword, and then some other errors near the word "as" and the ")" character. These are some of those errors that I find pretty meaningless that just mean I've really thrown something off.

So, am I not allowed to declare a variable within these SELECT tables that I'm creating and joining?

Thanks in advance,
Andy



Select * from

(

declare @years table (years int);

insert into @years

select

CASE

WHEN month(getdate()) in (1) THEN year(getdate())-1

WHEN month(getdate()) in (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12) THEN year(getdate())

END

select

u.fullname

, sum(tx.Dm_Time) LastMonthBillhours

, sum(tx.Dm_Time)/((select dm_billabledays from dm_billabledays where Dm_Month = Month(GetDate()))*8) lasmosbillingpercentage

from

Dm_TimeEntry tx

join

systemuserbase u

on

(tx.owninguser = u.systemuserid)

where

Month(tx.Dm_Date) = Month(getdate())-1

and

year(dm_date) = (select years from @years)

and tx.dm_billable = 1

group by u.fullname

) as A

left outer join

(select

u.FullName

, sum(tx.Dm_Time) Billhours

, ((sum(tx.Dm_Time))

/

((day(getdate()) * ((5.0)/(7.0))) * 8)) perc

from

Dm_TimeEntry tx

join

systemuserbase u

on

(tx.owninguser = u.systemuserid)

where

tx.Dm_Billable = '1'

and

month(tx.Dm_Date) = month(GetDate())

and

year(tx.Dm_Date) = year(GetDate())

group by u.fullname) as B

on

A.Fullname = B.Fullname

Left Outer Join

(

select

u.fullname

, sum(tx.Dm_Time) TwomosagoBillhours

, sum(tx.Dm_Time)/((select dm_billabledays from dm_billabledays where Dm_Month = Month(GetDate()))*8) twomosagobillingpercentage

from

Dm_TimeEntry tx

join

systemuserbase u

on

(tx.owninguser = u.systemuserid)

where

Month(tx.Dm_Date) = Month(getdate())-2

group by u.fullname

) as C

on

A.Fullname = C.Fullname

View 1 Replies View Related

Updating A Table By Both Inserting And Updating In The Data Flow

Sep 21, 2006

I am very new to SQL Server 2005. I have created a package to load data from a flat delimited file to a database table. The initial load has worked. However, in the future, I will have flat files used to update the table. Some of the records will need to be inserted and some will need to update existing rows. I am trying to do this from SSIS. However, I am very lost as to how to do this.

Any suggestions?

View 7 Replies View Related

Why Isn't This Variable Updating?

Jul 20, 2005

Hi;I have a sqlserver database with a field that is of TEXT datatype (not my decision) that is used to store comments from users on one ofour websites.For various reasons I need to make code that will clean the text inthis field( for example purposes mytable.comment ) so that there are no singlequotes in it.I am experiementing with making mytable.comment a mix of 'B' and 'Q'such that all 'Q's are replaced with 'B's.The code below works.......once.If I run it more then once no further 'Q's will get replaced.The problem is with the @index variable I am using that tellsUPDATETEXT where to update.It isn't changing.Any ideas would be greatly appreciatedSteve------------------------------------------------------------------------DECLARE @ptrBlurb varbinary(16), @index intselect @ptrBlurb=TEXTPTR(comment), @index=PATINDEX('%Q%',comment)frommytablewhere PATINDEX('%Q%', comment) <> 0 andprojid = '00013'UPDATETEXT mytable.comment @ptrBlurb @index 1 'B'select projid, comment from mytable-------------------------------------------------------------------------

View 5 Replies View Related

Updating The Variable's Value

Jul 29, 2007

Hello People,
I'm using SSIS and I want to send a report to the admin about how many rows are new, updated or unchanged in a mail. Everything is working fine except that the values that are sent are always zeros. I'm using a Row Count Transformation and configuered it to update the approperiate User Variable which I priviously created. However, the initial values in these variables are always Zeros. What can I do?
Thanks,
SHIKO

View 12 Replies View Related

Variable Scoping/updating Question

Mar 14, 2007

Hi,

I'm having some trouble with some variables in my package.

A brief overview:

My package grabs all the data from an Excel sheet and based on several factors, divides the data up into rows and inserts them into a database.

The Excel sheet has certain ranges of cells that determine how I split the data (and therefore which table the data will go into).

My package is structured as follows:

I. Control Flow:

1. Data Flow Task:

a. OLE DB Source - grabs all data from Excel sheet

b. Script Component - adds a rowcount column to each row, determines the ranges of the various sets of data and sets some variables to the row numbers of the ranges.

c. Conditional Split - splits the data by comparing each row number to the variables with the range row numbers in them

d. Various Flat File destinations (to be replaced by something saving to a table).

The problem I am having is that the Conditional Split doesn't seem to get the correct values of the variables once they are changed in the Script Component.

I know that the Conditional Split is setup correctly as if I put the values for the variables in as defaults the Conditional Split works correctly .

Inside of "

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

" in my script component, as I'm adding the rownumber, I search for specific values to determine if the row represents the beginning or end of a range. If it meets the criteria, I take the row number and and save it to a public integer (of the Script Component)

After I've added my row numbers, inside of "Public Overrides Sub PostExecute()" I have the following:

Public Overrides Sub PostExecute()

Me.ComponentMetaData.FireInformation(0, "Changed Variable!!!", "FirstSalesRow: " + CStr(MyBase.Variables.StartRange1), "", 0, False)

'set our variables

MyBase.Variables.StartRange1= iStartRange1

MyBase.Variables.EndRange1= iEndRange1



Me.ComponentMetaData.FireInformation(0, "Changed Variable!!!", "FirstSalesRow: " + CStr(MyBase.Variables.StartRange1), "", 0, False)

End Sub

Now, when the Script component finishes and I go to the Execution results, I can see that the first "Me.ComponentMetaData.FireInformation()"

returns the default value of the variable, 0.

But the line below it is the second "Me.ComponentMetaData.FireInformation()" and it clearly shows the correct variable value.

I have checked that all of my variables in the Script Component are in the "ReadWriteVariables" property, all variables are "ReadOnly = False" as well and are scoped at the package level.

This has lead me to believe that the Conditional Split task is grabbing the value of the parameter prior to the begin of the Data Flow itself.

Is that correct?

If so, should I be able to work around this by having one Data Flow with a script component to set the variables and output an in memory dataset, then have a second Data Flow with the Conditional Split in it?

Please let me know if you need any more info to help.

Thanks!

View 2 Replies View Related

Updating The Variable Inside The Dataflow

Nov 2, 2007



Is it possible to update the value of a user defined variable within the DataFLow in SSIS. I am aware you can update a variable using a script task in the Control Flow, but how about the DataFlow?

Thanks for any help in advance.

View 1 Replies View Related

Updating/getting Values From Datagrid In C# With Variable Parameters

Oct 24, 2006

HiI am new to the world of aspx, .net and C#.In aspx .net 2.0. I am trying to work out how to get a datagrid to perform an update. Using Visual Developer I have successfully added the control and specifed a select statement to return data via my SQLData Source. This works fine. However having specifed the control as editable I would like to perform an update through the datagrid and SQLDatasource. I see in the properties for the SQLDatasource object I can specify my update statement.However I do not understand how to get that update statement to have variable values and how newly entered values from the grid can be placed into these variables when the update takes place. Can someone please point me in the right direction? I have not found the MS doc very illuminating thus far and have not found any examples.Many ThanksT

View 1 Replies View Related

Script Task Not Updating Package Variable

May 3, 2006

I am trying to update a package variable. The package consists only of a script task and a package user variable. I have included the variable, myVar (scope: package; type: string), in the ReadWriteVariables property of the script task.

The only code I have used, in Public Sub Main, is:

Dts.Variables("myVar").Value = "2"

The package runs successfully but the variable does not change. I thought that maybe the underlying value really does change even though the value as seen in the package variables window does not (I tested this in another package/solution but it does not seem to - not even during runtime).

I also tried running the variabledispenser method but this resulted in the package running continuously until I stop debugging.

Any suggestions greatly appreciated.

Regards,

Puzzled Again

View 3 Replies View Related

SQL 2012 :: SSIS / Dynamic Updating Of A Recordset Variable?

Jul 8, 2015

updating a recordset contained in an System.Object variable during runtime.

I am trying to execute multiple file actions (plus parsing those files into a set of staging tables) at separate locations in parallel. I know I can do this in C# but I have a business requirement to use SSIS for all ETL operations.

Any one site can have 0 to many of 1 to 3 files. I would like to run multiple sites at the same time, so when all files of all types are completed at that site then go on to the next site in the list. I know I can do a single site at a time in a foreach loop but if I can run lets say 3-5 sites concurrently then I should be able to save execution time.

My thought is to have a recordset of the sites, when any 1 of the 3 (or more) "control flows" is open, update the recordset to let it know that site being actioned, when that site is complete, update the recordset that the site is completed, and so on.Or am I running in the wrong direction?

View 5 Replies View Related

Updating Data In Database VB Code Problem, Must Declare Scalar Variable

Feb 18, 2008

I get a Must Declare Scalar Variable for CompanyName error. Please help. Thank you. datasource.ConnectionString = ConfigurationManager.ConnectionStrings("ConnString").ToString()
datasource.UpdateCommand = ("UPDATE Company SET [CompanyName] = @CompanyName), = @Email, [PhoneNumber] = @PhoneNumber, [WebsiteName] = @WebsiteName WHERE ([CompanyID] = " & Request.QueryString("CID"))datasource.UpdateParameters.Add("@CompanyName", txtName.Text)
datasource.UpdateParameters.Add("@Email", txtEmail.Text)datasource.UpdateParameters.Add("@PhoneNumber", txtPhoneNumber.Text)
datasource.UpdateParameters.Add("@WebsiteName", txtWebsite.Text)
datasource.Update()

View 4 Replies View Related

Updating Tables

Jan 19, 2008

Consider i have 2 database namely database1 and database2 . both the databases are in the same server
database1 has a following tables
1.the table "class" has following fields and many more fields. consider 50 fields but for the use of example i have given only three fields name and sample values.     class        studentname           rollno  (table name - class)    8th std      aaaaa                     100
2. the table "Fees" has the following fields  and many more fields. consider 25 field but for the use of example i have gievn only 2 fields name and sample values    rollno              fees         (table name - fees)     100                50000     101                25000
Now i have created the following tables in database2  the table "class" has the following fields only,         class          studentname              rollnothe table "fees" has the following fields only.        rollno           fess  
Question ?please let me know if there is any tool or method . to transfer values from database1 to databse2
 
 
 

View 5 Replies View Related

Updating The Tables

Nov 26, 2005

now i want to update all the three tables with the help of asp.net in the table PhoneExtraFieldAliasalias should be replased by the inputed values say in filed1 = 'date' so now filed1 should behave like as date column and if no data is inputed then field should be 'null' and it picks up its value from phoneextra and phonemst and all the task are performed in one form of asp.net in phoneextra nad phoneextrafieldalias tables has phoneid is comman.first it takes data from phoneextrafiledalias then its value from phoneextra CREATE TABLE [dbo].[PhoneExtra] ([PhoneID] [varchar] (12) NOT NULL ,
[Field1] [varchar] (50) NULL ,[Field2] [varchar] (50) NULL ,[Field3] [varchar] (50) NULL ,[Field4] [varchar] (50) NULL ,[Field5] [varchar] (50) NULL ,[Field6] [varchar] (50) NULL ,[Field7] [varchar] (50) NULL ,[Field8] [varchar] (50) NULL ,[Field9] [varchar] (50) NULL ,[Field10] [varchar] (50) NULL ,[Field11] [varchar] (50) NULL ,[Field12] [varchar] (50) NULL ,[Field13] [varchar] (50) NULL ,[Field14] [varchar] (50) NULL ,[Field15] [varchar] (50) NULL ,[Field16] [varchar] (50) NULL ,[Field17] [varchar] (50) NULL ,[Field18] [varchar] (50) NULL ,[Field19] [varchar] (50) NULL ,[Field20] [varchar] (50) NULL ) ON [PRIMARY]
GO
CREATE TABLE [dbo].[PhoneExtraFieldAlias] ([CampaignID] [varchar] (20) NOT NULL ,[Field1] [varchar] (50) NULL ,[Field2] [varchar] (50) NULL ,[Field3] [varchar] (50) NULL ,[Field4] [varchar] (50) NULL ,[Field5] [varchar] (50) NULL ,[Field6] [varchar] (50) NULL ,[Field7] [varchar] (50) NULL ,[Field8] [varchar] (50) NULL ,[Field9] [varchar] (50) NULL ,[Field10] [varchar] (50) NULL ,[Field11] [varchar] (50) NULL ,[Field12] [varchar] (50) NULL ,[Field13] [varchar] (50) NULL ,[Field14] [varchar] (50) NULL ,[Field15] [varchar] (50) NULL ,[Field16] [varchar] (50) NULL ,[Field17] [varchar] (50) NULL ,[Field18] [varchar] (50) NULL ,[Field19] [varchar] (50) NULL ,[Field20] [varchar] (50) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[PhoneMst] ([PhoneId] [varchar] (12) NOT NULL ,[PhoneNo] [varchar] (50) NOT NULL ,[Name] [varchar] (50) NULL ,[Sex] [char] (1) NULL ,[Company] [varchar] (100) NULL ,[Address] [varchar] (150) NULL ,[City] [varchar] (50) NULL ,[State] [varchar] (50) NULL ,[Zip] [varchar] (50) NULL ,[Country] [varchar] (50) NULL ,[Email] [varchar] (60) NULL ,[Website] [varchar] (50) NULL ,[Fax] [varchar] (50) NULL ,[EntryOn] [datetime] NULL ,[Remarks] [varchar] (10) NULL ) ON [PRIMARY]GO

View 1 Replies View Related

Updating Changes To Two Tables Using T-SQL

Jan 24, 2006

I have a webform contains a list of documents and information about them.   What would be the best way to update any changes back to the database where I would need to log each change into an audit table as well.
For example:document   changedA               YesB               NoC               Yes
I would need to take this information and update the documents table with the changes from A and C, but ALSO would need TWO entries in my audit log table.  One for A and one for C.  Is there a quick way to do this or will I need to use loops and/or cursors?  I'm trying to find a way that won't kill my performance.  Thank you.

View 2 Replies View Related

Updating Very Fat Tables

Aug 19, 2005

Even if indexes are good, isn't there still a performance issue with updates to a very fat table (333 columns and almost 8030 bytes, with over 100,000 rows). We are looking at 300 users updating this table 400-500 times an hour. I had no input on the table design, but I can complain. All the updates are sql, not sp's

View 2 Replies View Related

Updating Two Tables

Feb 15, 2008

Hello I have two tables users and Private I want to be able to view, update, delete info from the two tables from one screen

scenario user logs in to system system stores userID and transfers userID to new table this user id will be used by sql to load only that users details (UserID is PK for users and FK for private) the user will be able to update some of their info. as an admin they will use the same procedure except they will be able to see all members details and add new members using update or delete. I have tried to use a data Sqladaptor to do this but the info will display it will not allow delete update. If I load both tables into separate Sqladaptors they are not in sync anyone have any suggestions

thanks

M

View 3 Replies View Related

Updating Two Tables

Dec 21, 2007

I have two tables:

table 1
-------

code
description
colour
size
active

table 2
-------

code
location
sales_region
active

How can I, using SQL Server 2005, update both tables so that

colour = red
location = Wisconsin
size = medium
active = yes

where code = AL1

please?

table 1 has code as the primary key. table 2 has no index.

View 4 Replies View Related

Updating Two Tables

Jan 23, 2007

Okay, here is my issue:I have an access program that tracks the location of certain items.When the items are moved their record will be added with transferinformation.So, there are two tables: tblContents and tblTransfertblContents holds all the relevant information about each item as wellas a flag for transferred items and tblTransfer holds all thetransferred item information.My problem is: How do I update tblTransfer when an item in tblContentsgets flagged true?btw, this is using a .asp front end to interact with the database.

View 1 Replies View Related

Updating Tables

Jan 11, 2008

I'm writing a sproc to update a table based on parameters being passed into it. Here is the sproc:




Code Block
CREATE PROCEDURE [dbo].[UpdateLicense]

@VendorId int,
@PoId int,
@LicenseTypeId int,
@LicenseUserId int,
@LocationId int,
@LicenseStartDate smalldatetime,
@DaysAllowed int,
@SerialNum varchar(50),
@ActivationKey varchar(50),
@MaxUsers int,
@Comments varchar(1000),
@LicenseId int
AS
BEGIN
UPDATE license
SET
vendor_id = @VendorId,
po_id = @PoId,
license_type_id = @LicenseTypeId,
lic_user_id = @LicenseUserId,
location_id = @LocationId,
lic_start_date = @LicenseStartDate,
days_allowed = @DaysAllowed,
serial_num = @SerialNum,
activation_key = @ActivationKey,
max_users = @MaxUsers,
comments = @Comments
WHERE license_id = @License_id;
END






When I try to compile this I get the following error:




Error Message
Msg 137, Level 15, State 2, Procedure UpdateLicense, Line 35
Must declare the scalar variable "@License_id".




My first question is what line is line 1? Does the line count include every line, even comments? Becuase, if so, then line 35 is "location_id = @LocationId," which makes no sense to me given the error message about the scalar variable. I am probably missing a comma or something but I cant see it. Anyone?

View 7 Replies View Related

Updating 2 Tables

Mar 2, 2008



Hi,

Is it possible to update two tables with the same stored procedure. I want to update the tables Member an Login using the same SP. If not is it possible to use two stored procedures but the same SQLconnection with two command statements?


e

USE [AdminDB]



ALTER PROCEDURE [dbo].[swim_insert_ipnumber]

@member int,

@mbrFirstName nvarchar(15),

@mbrLastName nvarchar(15),

@mbrEmail nvarchar(15),

@lgnIPnumber int



AS

BEGIN

update Member,, Login

set Member.mbrFirstName = @mbrFirstName,

Member.mbrLastName = @mbrLastName,

Member.mbrEmail = @mbrEmail,

Login.lgnIPnumber = @lgnIPnumber



where mbrMemberNumber = @member

END

View 6 Replies View Related

Updating Multiple Tables

Aug 28, 2006

Hello,I read Data Tutorials from this site. here in DAL Different TableAdapters are created for different purpose. I want to know when I want to update More then one table at a time then I have to fire Update Query from more than one Table Adapter. now how to maintain Consistancy? what if one adapter is successed and other fails to update tables in my database ? How to solve this problem???

View 3 Replies View Related

Updating Data In Two Tables

May 25, 2008

 I have a database which is used for the asp.net login control and i use the same database for my website work too. In this database there are asp.net created tables for login controls and the tables that i have created for the website. Now when i add a user to the website, data is added in the asp.net created tables (like aspnet_membership, aspnet_users). I want to add  some of the data that is added to these tables into the tables that i have created. Is there a way i can do this?

View 4 Replies View Related

Updating Of The Tables In Two Different Databases

Dec 16, 2001

I am using SQL server7.0. I am having two databases ,say database A and database B.Database A is the main database which is used in two /three projects.While database B is created by me for my work.From the database A, I am using the 4/5 tables, which i have copied in to the database B.
So, i want to update these tables in the database B as soon as any change (insert,update or delete ) occures on the tables in the database A.
That's why I am interested in doing these work.I have tried, but it doesn't work.So, I have placed these into these forum.
Is any body is having the trigger, procedure ready for doing these job. then plz,mail it to me

View 1 Replies View Related

Updating Multiple Tables

Mar 19, 2008

Hi,

I'm trying to update a table with a value that is dependent on another table.

Scenario
Table 1 has 2 fields: FieldID, FieldA
Table 2 links each Table 1 row to a specific row in Table 3 using FieldID
Table 3 has 2 fields: FieldID, FieldA

I need to update Table1.FieldA to equal the value in Table3.FieldA in the appropriate row.

I was attempting this along the lines of:
UPDATE Table1
SET Table1.FieldA=
(
SELECT DISTINCT Table3.FieldA FROM Table1, Table2, Table3
WHERE Table1.FieldID=Table2.Table1ID
AND Table2.Table3ID=Table3.FieldID
)

However, I realised that the select query would not know which Table3 row to look at.

I hope that makes sense and if someone can help me urgently I would be most appreciative!

Ian

View 1 Replies View Related

Updating Multiples Tables

Feb 8, 2007

hi,
a simple question. I have more that 100 table (132 just). one per month from 1997.

Example:
H0_CONTABILIDAD_199701
H0_CONTABILIDAD_199702
...
H0_CONTABILIDAD_200701
H0_CONTABILIDAD_200702
..
.
.
H0_CONTABILIDAD_200712


Now, how I update all tables with a stantement, sp, etc. Each table have 500k of rows aprox (500.000)

the idea is:

update <table>
set myField = 'newValue'


TIA

View 5 Replies View Related

Updating Tables Using Views

May 18, 2007

Is there any gud topic on "updating tables using Views".Plz let me know

View 3 Replies View Related

Updating System Tables

Jul 20, 2005

Hi,I want to replicate the system tables of the Northwind database inanother test database that I have created (say NorthwindTest). Istarted with the syscomments table:insert into NorthwindTest.dbo.syscommentsselect * fromNorthwind..syscomments whereNorthwind..syscomments.id not in(select id from NorthwindTest.dbo.syscomments )On doing so,I get the following error:Server: Msg 213, Level 16, State 5, Line 1Insert Error: Column name or number of supplied values does not matchtable definition.Does any one know why the two tables are different?Also, if I try to insert on a per row basis, the following worksinsert into syscomments values(53575229,0,1,0,0x280030002900)but the following DOES NOT workinsert into syscomments values(53575229,0,1,0,0x280030002900,NULL,NULL,NULL,NULL,NULL)FYI, the definition of syscomments table is:CREATE TABLE [syscomments] ([id] [int] NOT NULL ,[number] [smallint] NOT NULL ,[colid] [smallint] NOT NULL ,[status] [smallint] NOT NULL ,[ctext] [varbinary] (8000) NOT NULL ,[texttype] AS (convert(smallint,(2 + 4 * ([status] & 1)))) ,[language] AS (convert(smallint,0)) ,[encrypted] AS (convert(bit,([status] & 1))) ,[compressed] AS (convert(bit,([status] & 2))) ,[text] AS (convert(nvarchar(4000),case when ([status] & 2 = 2) then(uncompress([ctext])) else [ctext] end))) ON [PRIMARY]GOThanks in advance,TC

View 2 Replies View Related

Updating Tables Via Agent ?

Jul 20, 2005

I have a temporary table that I want to read, update another tablewith the information the delete the record from the Temp table everyhour or so.(The reason for doing this is we have an application that puts data intable1 this has to be moved to table2 before it can be used - thismovement is done using a trigger but can not delete the entry intable1 because the record is locked - so I creat the temporary recordand hopr to clean up later)What is the best way to do this, I was thinking possably an agent ?does anyone have any sample code to get me started.ThanksMartin

View 3 Replies View Related

Updating Multiple Tables

May 10, 2007



we have some tables in a many-to-many relationship. when data is changed in a row in one table, simultaneous changes are also made to a second table. this may not be the best way to do what we are doing, but it's the way it is today. if we were to use merge replication with column-level tracking and a conflict occurs in a column in either table, we want the row for both tables to be including in the conflict and resolved together. we don't want the row for a table that no conflict occured to be updated while the other row in the other table where the conflict did occur to not be updated. is there a way to wrap two updates to two tables in a transaction? or link them together in a single "conflict resolution transaction"? i don't see any online documentation referencing this scenario. if there is documentation on this, i would appreciate a pointer to it.



thanks,



bryan

View 1 Replies View Related

SQL Database Tables Are Not Updating

Jun 27, 2007

Although the codes get correctly compiled and executed the tables on the database are not getting updated. I also used the execute sql command manually but it also doesnt work.
:-(
What to do?

View 3 Replies View Related

Updating Data From 2 Tables

Apr 29, 2008



Hello,
I have a ta ble that has 2 columns(licenseplatenumber,vehicletype) and a second table that has 20 or more columns but has(licenseplatenumber,vehicletype) too. The first table got changed once because vehicletype changed from central office for each licenseplatenumber, and i was wondering if there is a way to update the second table with the data from the first table with one or two statements, and not one-by-one. there are alot of data to be changed.
thank you in advance..

View 1 Replies View Related

Updating 2 Tables In One Query

Nov 21, 2006

I'm using SQL Server 2005.
Is it possible to update two tables in a single update query by comparing rows in two tables?

Something like this:
UPDATE h
SET
CheckAmount =c.[amount1],
c.Updated = 'YES'
FROM tblCheckNumber as c
INNER JOIN tblHistory as h
ON c.Autonumber = h.AutoNumber
WHERE (h.CheckAmount Is Null Or h.CheckAmount=0)
AND h.CheckNumber Is Null
AND h.CheckDate Is Null
AND h.AccountNumber Is Null;

View 1 Replies View Related

.NET Framework :: Updating Two Tables

Nov 24, 2015

I have a two tables stock and sale, after going some sales, should update quantity in stock table

So, I did it as follows.

UPDATE stock
SET quantity -= item.totalQuantity
FROM stock
INNER JOIN (
SELECT sales.barcode
,SUM(sales.quantity) AS totalQuantity

FROM sales
WHERE sales.isQuantityDeduct = 0
GROUP BY sales.barcode
) AS item ON stock.barcode = item.barcode

UPDATE sales
SET isQuantityDeduct = 1
FROM sales
WHERE sales.isQuantityDeduct = 0

This approach has some problem, while updating stock table there may be newly inserted sales. So since I update all record of sale table, stock not updating correctly.

View 4 Replies View Related







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