Setting one field equal to another field

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

General SQL Server Forums

New to SQL Server Programming

Setting one field equal to another field

Author

Topic

Japboix1
Starting Member

24 Posts

Posted - 2011-07-14 : 15:50:19

Ignore this post, I wrote a better example down below XD

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-07-14 : 16:28:49

I don't understand your data sample with your explanation, but here's an example:

UPDATE t1
SET c2 = c1
WHERE ...

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

Subscribe to my blog

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-07-14 : 16:47:07

I think they mean that the [ID] value in the fourth row was set to 1 because the "previous true condition" (first row) had an [ID]=1.

if that is true then that is really bad sample data. The values in [column],[ID], and [Condition] are all the same in the first row. So you can't tell which column you are using to set the value in the fourth row.

and this: "(in column 1)"
means first row?
nice ;)

Be One with the Optimizer
TG

Japboix1
Starting Member

24 Posts

Posted - 2011-07-15 : 08:11:37

quote:
Originally posted by TG

I think they mean that the [ID] value in the fourth row was set to 1 because the "previous true condition" (first row) had an [ID]=1.

if that is true then that is really bad sample data. The values in [column],[ID], and [Condition] are all the same in the first row. So you can't tell which column you are using to set the value in the fourth row.

and this: "(in column 1)"
means first row?
nice ;)

Be One with the Optimizer
TG

Yeah I know, my bad it was a really bad example, now that I look at it XD I'll try a better example this time.

[Code]
Column ID Condition
1 1 | 1
2 2 | 0
3 3 | 0
4 4 | 1

So I have that.

I want to write a query that changes the ID to equal the row before it if condition is the same as the row before it.

So, after running the query, it needs to be:


Column     ID       Condition
1          1      |    1
2          2      |    0
3          2      |    0
4          4      |    1
[/code]
Since the condition in row 3 was the same as the row before it, the Id was set to also equal the row before it. But nothing else changed because they didn't meet those conditions. Is that a better example?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-07-15 : 15:56:05

Assuming that [column] defines the order of your table rows for the purpose of knowning which row is the "previous" row and that [column] values are sequential then this recursive common table expression would work:


;with yourTable ([Column], [ID], Condition)
as
(
       select 1, 1, 1 union all
       select 2, 2, 0 union all
       select 3, 2, 0 union all
       select 4, 4, 1
)

--recursive CTE

,rCTE ([Column], [ID], Condition)
as
(
       select [column]
              ,[ID]
              ,Condition
       from yourTable
       where  [column] = 1
       union all
       select t.[column]
              ,case when c.condition = t.condition then c.[id] else t.[id] end
              ,t.condition
       from   rCTE c
       join   yourTable t on t.[column] = c.[column]+1
)
select * 
from rCTE

OUTPUT:
Column      ID          Condition
----------- ----------- -----------
1           1           1
2           2           0
3           2           0
4           4           1

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources