SQL Server Forums
Profile | Register | 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.
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ugh3012
Yak Posting Veteran

56 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

547 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
52249 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

56 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

547 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
52249 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

56 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
52249 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  
 New 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