Simple update? - SQL Server Forums

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)

Simple update?

Author

Topic

cdgregory
Starting Member

9 Posts

Posted - 2009-11-22 : 11:13:47

Hi,

I really hope you can help. I have what seems like a simple request, but I am struggling.

So, I have 4 tables, I need to update the month and year columns on tblDirect with the month and year values from tblNewData. Sounds straight forward enough but the problem is that there no relation between tblDirect and tblNewData; the relationship is created be joining three tables (tblDirect, tblPOLine, tblPurchaseOrder) as the unique ref is created by concatenating tblPurchaseOrder.tPONumber and tblPOLine.iLineNumber) - this is then joined directly to the tblNewData.unique_ref.

The tables and columns:
tblDirect (month, year, rowid)
tblPOLine (ipoid, iLineNumber)
tblPurchaseOrder (iid, tPONumber)
tblNewData (unique_ref, month, year)

Relations:
tblDirect.rowid - tblPOLine.ipoid
tblPOLine.ipoid - tblPurchaseOrder.iid

So I need to end up with something like:

update tblUpate
set month = (select month from tblNewData where ~ problem here, I need the composite key ~ = tblNewData.unique_ref),
year = (select year from tblNewData where ~ problem here, I need the composite key ~ = tblNewData.unique_ref)

Any help would be greatly appreciated.

Thanks in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-22 : 11:45:18

[code]UPDATE u
SET u.month=n.month,
u.year=n.year
FROM tblDirect u
JOIN tblPOLine l
ON u.rowid=i.ipoid
JOIN tblPurchaseOrder p
ON p.iid = l.ipoid
JOIN tblNewData n
ON n.unique_ref = p.tPONumber + l.iLineNumber
[/code]

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-22 : 11:46:54

Must be something like this:
update di
set [month]=ne.[month], [year]=ne.[year]
from tblDirect di
join tblPOLine po on po.ipoid = di.rowid
join tblPurchaseOrder pu on pu.iid = po.ipoid
join tblNewData ne on ne.unique_ref = convert(varchar(20),pu.tPONumber)+convert(varchar(20),po.iLineNumber)

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-22 : 11:48:08

No, you're never too old to Yak'n'Roll if you're too young to die.

cdgregory
Starting Member

9 Posts

Posted - 2009-11-22 : 12:21:20

Thank you both for the prompt response.

Could I also added a WHERE clause to the end?

E.G.
WHERE
di.iExpenditureType=3
OR di.iExpenditureType=4
AND di.bIsActive=1

Additionally I assume I could replace the first two lines with a select statement to see the number of records that the query would update?

Thanks again

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-22 : 12:26:26

1.) Yes.
2.) Yes.

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-22 : 12:28:34

And you can do:
begin tran
update...
rollback -- if number of records seems to be wrong or
commit -- if number of records seems to be ok

No, you're never too old to Yak'n'Roll if you're too young to die.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-11-22 : 12:57:54

quote:
Originally posted by cdgregory

Additionally I assume I could replace the first two lines with a select statement to see the number of records that the query would update?

You don't need to run a select statement for that, just query @@ROWCOUNT right after the update statement.

DECLARE @rc int

UPDATE statement...

SET @rc = @@ROWCOUNT

PRINT @rc

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

cdgregory
Starting Member

9 Posts

Posted - 2009-11-22 : 13:26:37

Thanks everyone - a very quick and excellent response from all.

cdgregory
Starting Member

9 Posts

Posted - 2009-11-23 : 03:13:30

quote:
Originally posted by cdgregory

Thanks everyone - a very quick and excellent response from all.

I do have one final question in terms of best practice/syntax.

Do the following both give the same result:

update tblDirect
set month =
(select tblNewData.month
from

And:

update di
set [month]=ne.[month], [year]=ne.[year]

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-23 : 09:35:50

quote:
Originally posted by cdgregory

quote:
Originally posted by cdgregory

Thanks everyone - a very quick and excellent response from all.

I do have one final question in terms of best practice/syntax.

Do the following both give the same result:

update tblDirect
set month =
(select tblNewData.month
from

And:

update di
set [month]=ne.[month], [year]=ne.[year]

Thanks

depends on how you've written the inner query. I like latter way though which involves join

cdgregory
Starting Member

9 Posts

Posted - 2009-11-23 : 09:57:02

Thats what I thought - thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources