Update Query - 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 2000 Forums

Transact-SQL (2000)

Update Query

Author

Topic

h0003192
Starting Member

3 Posts

Posted - 2004-07-19 : 07:38:57

Dear all,
I am new to this forum and I have a question regarding the Update Query.

Here is the table:
Source Effective_dt Rate Return
WM11 6/30/2004 0.580568 0
WM11 5/31/2004 0.587976 0

I would like to write an update query to update the Field:Return which is computed by: last_month_rate/current_month_rate.

How can I do this?

Thanks

Michael

JasonGoff
Posting Yak Master

158 Posts

Posted - 2004-07-19 : 07:49:19

Something like...


UPDATE MyTable
    SET Return=(SELECT TOP 1 Rate 
                    FROM MyTable A 
                    WHERE A.Effective_dt < MyTable.Effective_dt 
                    ORDER BY Effective_dt DESC) / MyTable.Rate

Obviously, some defensive coding will be needed to catch DIV 0 etc. You'll also need to think about how you handle it when there isn't a last_month_rate.

SamC
White Water Yakist

3467 Posts

Posted - 2004-07-19 : 07:50:35

Something like

UPDATE MT2
SET MT2.Return = MT2.Rate / CASE WHEN MT1.Rate =0 THEN 1 ELSE MT1.Rate END
FROM MyTable MT1
INNER JOIN MyTable MT2
ON DATEDIFF(mm, MT2.Effective_dt, MT1.Effective_dt) = 1

h0003192
Starting Member

3 Posts

Posted - 2004-07-19 : 08:57:44

quote:
Originally posted by SamC

Something like

UPDATE MT2
SET MT2.Return = MT2.Rate / CASE WHEN MT1.Rate =0 THEN 1 ELSE MT1.Rate END
FROM MyTable MT1
INNER JOIN MyTable MT2
ON DATEDIFF(mm, MT2.Effective_dt, MT1.Effective_dt) = 1

Isnt it From clause cant exist w/i an Update Statement?

Kristen
Test

22859 Posts

Posted - 2004-07-19 : 09:15:34

FROM is fine in an UPDATE ...

You can either do


UPDATE MyTable
SET ...
FROM   MyTable
       JOIN SomeOtherTable
       ON ...

or use an alias (which I prefer):


UPDATE X
SET ...
FROM   MyTable AS X
       JOIN SomeOtherTable
       ON ...

Kristen

h0003192
Starting Member

3 Posts

Posted - 2004-07-19 : 22:29:24

Thanks for the reply. However, I still encounter problems. Actually, I'm using Access instead of SQL server. I tried to write a simple update, which is:

update A
set A.return = 10
from tbl_data_exrate A
inner join tbl_data_exrate B
on month(A.effective_dt)-1 = B.effective_dt

But I kept on getting error msg:
" Syntax error (missing operator) in Query expression '10
from tbl_data_exrate A
inner join tbl_data_exrate B
on Month(A.effective_dt)- 1 = B.effective_dt' "

Michael

Kristen
Test

22859 Posts

Posted - 2004-07-20 : 01:33:15

Sorry, don't know about Access (tehre is an Access forum here, might be worth asking there?)

Kristen

mk_garg20
Constraint Violating Yak Guru

343 Posts

Posted - 2004-07-20 : 01:49:05

Kristen,
What is benefit of using FROM in update.

Mostly i have seen queries like

UPDATE MYTABLE
SET COLNAME='xxx' WHERE COLNAME1='yyyy'

I guess if you are using more than one table to filter records, that will be updated.

Thanks
Manoj

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources