simple update field in same column? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

simple update field in same column?

Author

Topic

dirwin26
Yak Posting Veteran

81 Posts

Posted - 2005-11-01 : 09:38:12

Can anyone tell me how to do update Col2 XX with 25 ?

TABLE
Col1..............Co12
2005-10-31........25
2005-11-01........XX

Any good suggestions?

Thanks,
dirwin

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-11-01 : 09:39:54

Somthing like this ..

Update <Table_Name> Set Col2 = 25
Where Col2 = 'XX'

Complicated things can be done by simple thinking

dirwin26
Yak Posting Veteran

81 Posts

Posted - 2005-11-01 : 09:46:53

The value for Col2 is actually unknown to the user as it's built off of transactions that occur throughout the day. At the end of the day, I want to take this variable (col2 value) and insert it one field down so the transactions can pick up where they left off for the next day.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-11-01 : 09:58:31

Hope this helps

Update TableNAme Set Col2 = (Select MAx(Col2) From TableName Where Col1 = GetDate())
Where Col2 = DateAdd(dd,1,GetDAte())

??

and if you inserting the record then you can try like this

Insert TableName
Select DateAdd(dd,1,GetDAte()),Max(Col2) From TableName
Where Col1 = GetDate()

Complicated things can be done by simple thinking

dirwin26
Yak Posting Veteran

81 Posts

Posted - 2005-11-01 : 10:20:14

Unfortunately no dice.

What about making a new column col3,and setting it equal to col2 and then using addate to add 1 day to the col3 value?

update tablename
set col3=col2
where col1 = @thedate (or getdate())
and dateadd(day,1,theday)<=col1

Seems logical, but it't not working.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-11-01 : 10:34:46

hey .. try this if its help you ..

Declare @table Table
(col1 SmalldateTime,
col2 varchar(10))

Insert Into @Table
Select DateAdd(dd,-1,Getdate()),25
Union All
Select DateAdd(dd,-1,Getdate()),15
Union All
Select DateAdd(dd,-1,Getdate()),10
Union All
Select DateAdd(dd,-1,Getdate()),10
Union All
Select GetDate(),'xx'

Update @Table Set col2 = (Select Top 1 col2 From @Table Where Convert(Varchar(10),Col1) = Convert(Varchar(10),DateAdd(dd,-1,Getdate())) order by Col1 desc)
Where Convert(Varchar(10),Col1 )= Convert(Varchar(10),GetDate())

Select * From @Table

Post some more sample data.. so that we can get the better idea about it..

Complicated things can be done by simple thinking

dirwin26
Yak Posting Veteran

81 Posts

Posted - 2005-11-01 : 10:42:32

I ended up doing this:

insert into #res (thedate, cash1)
select theday, cashfinish
from cashbalance

UPDATE cashbalance
SET cashstart = cash1
FROM #res
WHERE DATEADD(dd,1,thedate) = cashbalance.TheDay

It seemed to work.

Thanks for the help...your stuff gave me some good ideas.
Dirwin

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources