Update a field during the select process.

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 2008 Forums

Transact-SQL (2008)

Update a field during the select process.

Author

Topic

ugh3012
Yak Posting Veteran

62 Posts

Posted - 2013-06-26 : 10:31:24

I have a situation where I need to update a field with either ‘Y’ or ‘N’. I prefer to do it in one step instead of two separate queries. Here is how it is done in two steps. Can this be done in one step?


First Step:
Select 
field1,
field2,
field3,
‘N’ as field4,
field5
INTO #Tmp
From Table1

Second Step:
Update INTO #Tmp Set field4 = ‘Y’ From #Tmp 
inner join Table2 on #Tmp.field1 = Table2.Field1
Where Table2.Field9 in ('81','84','88')

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-06-26 : 10:43:13

[CODE]

Select
field1,
field2,
field3,
(CASE WHEN Field9 in ('81','84','88')
THEN ‘Y’ ELSE 'N' END) as field4,
field5
INTO #Tmp
From Table1

[/CODE]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-26 : 11:21:12

[code]
Update t1
Set field4 = 'Y'
From Table1 t1
inner join Table2 t2
on t2.field1 = t2.Field1
Where t2.Field9 in ('81','84','88')
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

ugh3012
Yak Posting Veteran

62 Posts

Posted - 2013-06-26 : 11:37:21

Cannot do that as that will filter out records.

quote:

Originally posted by visakh16


Update t1
Set field4 = 'Y' 
From Table1 t1 
inner join Table2 t2 
on t2.field1 = t2.Field1
Where t2.Field9 in ('81','84','88')

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-06-26 : 11:54:43

Try this

quote:
Originally posted by MuMu88

[CODE]

Select
t1.field1,
t1.field2,
t1.field3,
(CASE WHEN t2.Field9 in ('81','84','88')
THEN ‘Y’ ELSE 'N' END) as field4,
t1.field5
INTO #Tmp
From Table1 t1
inner join Table2 t2
on t2.field1 = t2.Field1

[/CODE]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-26 : 13:11:08

quote:
Originally posted by MuMu88

Try this
quote:
Originally posted by MuMu88

[CODE]

Select
t1.field1,
t1.field2,
t1.field3,
(CASE WHEN t2.Field9 in ('81','84','88')
THEN ‘Y’ ELSE 'N' END) as field4,
t1.field5
INTO #Tmp
From Table1 t1
inner join Table2 t2
on t2.field1 = t2.Field1

[/CODE]

can give a different result if tables are related in one to many relationship.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

ugh3012
Yak Posting Veteran

62 Posts

Posted - 2013-06-26 : 13:18:10

After trying several different ways, I decided to keep it as two steps. It’s not one to one relationship, so it is over my head at this point of time. I will revisit as I get more experience.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-27 : 00:59:28

this is a way to make this in single step and still avoid duplicates due to one to many relationship


Select 
t1.field1,
t1.field2,
t1.field3,
CASE WHEN Cnt >0 THEN  ‘Y’ ELSE 'N' END as field4,
t1.field5
INTO #Tmp
From Table1 t1 
inner join (
            SELECT field1,
            SUM(CASE WHEN Field9 in ('81','84','88') THEN 1 ELSE 0 END) AS Cnt
            FROM Table2 
            GROUP BY field1)
            t2 
on t2.field1 = t2.Field1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources