Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Update a field during the select process.
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ugh3012
Yak Posting Veteran

62 Posts

Posted - 06/26/2013 :  10:31:24  Show Profile  Reply with Quote
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')

Edited by - ugh3012 on 06/26/2013 11:37:50

MuMu88
Aged Yak Warrior

549 Posts

Posted - 06/26/2013 :  10:43:13  Show Profile  Reply with Quote


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


Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 06/26/2013 :  11:21:12  Show Profile  Reply with Quote

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


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

ugh3012
Yak Posting Veteran

62 Posts

Posted - 06/26/2013 :  11:37:21  Show Profile  Reply with Quote
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')


Go to Top of Page

MuMu88
Aged Yak Warrior

549 Posts

Posted - 06/26/2013 :  11:54:43  Show Profile  Reply with Quote
Try this
quote:
Originally posted by MuMu88



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





Edited by - MuMu88 on 06/26/2013 12:00:22
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 06/26/2013 :  13:11:08  Show Profile  Reply with Quote
quote:
Originally posted by MuMu88

Try this
quote:
Originally posted by MuMu88



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







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
Go to Top of Page

ugh3012
Yak Posting Veteran

62 Posts

Posted - 06/26/2013 :  13:18:10  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 06/27/2013 :  00:59:28  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000