Nervous about Update Statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Nervous about Update Statement

Author

Topic

Jonny1409
Posting Yak Master

133 Posts

Posted - 2009-01-15 : 09:24:39

Hello,

I'd like some help please if possible.

I have a table with about 2500 rows in, each row is a different persons record.
In these rows there is a column called "Allowance" that I am required to update.
The figure I need to update this column with is different for each person, and is to come from a view called "vw_NewAllowances"

Would anyone be able to help me with the syntax please, as I don't want to get it wrong.?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-15 : 09:28:32

does view invloves aggregation or complex operations? if not just use update view set...
if it involves coimplex operations, use an instead of trigger for updation.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-01-15 : 09:31:24

Hehe, is you name in this list??

What you can do is create a duplicate of the table your updating, just for testing purposes:

SELECT * INTO myTestTable FROM myRealTable

...and then test your updates on myTestTable. If you want us to help you we will need some more information, how the view looks and how the table to be updated looks.

- Lumbago

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-15 : 09:34:47

Or temporarily replace UPDATE with SELECT * to see which records are affected

--1 UPDATE t1
--1 SET t1.Col1 = x.ColGreen
--2 select t1.pkcol, t1.col1, x.ColGreen
FROM Table1 AS t1
INNER JOIN MyView as x ON x.pkCol = t1.pkCol

E 12°55'05.63"
N 56°04'39.26"

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-01-15 : 09:40:26

If you are not sure of your update -copy table and test.
Copy:
SELECT *
INTO dbo.CopyOFTableName
FROM dbo.TableName

Test:
Update CopyOFTableName
SET CopyOFTableName.Allowance= vw_NewAllowances.Allowance
FROM CopyOFTableName
INNER JOIN vw_NewAllowances
ON CopyOFTableName.Fld1 = vw_NewAllowances.Fld1

Jonny1409
Posting Yak Master

133 Posts

Posted - 2009-01-15 : 10:22:55

quote:
Originally posted by visakh16

does view invloves aggregation or complex operations? if not just use update view set...
if it involves coimplex operations, use an instead of trigger for updation.

No it doesn't - it's simply a view of a table which has been created from an Excel Spreadsheet.

In this view, it has each person's name and number, then their allowance.

Jonny1409
Posting Yak Master

133 Posts

Posted - 2009-01-15 : 10:26:47

Thanks to you all - I have now got what I need

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources