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)
 IF exists(select 1 ...
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

shilpash
Posting Yak Master

103 Posts

Posted - 02/11/2014 :  11:50:44  Show Profile  Reply with Quote
I am trying to understand select 1 here as this is my first time using this.I know I can do this query with update but trying to solve this using if exists(select 1..Let's say I have a table column1,column2.Now I want to insert Column 3 as a BIT datatype.Column3 is true when column1=>5.How do I accomplish this using if exists(select 1....from table).Here's my update query--
update table
set column3=1
where column1>=5

Thanks,Appreciated.

ScottPletcher
Constraint Violating Yak Guru

USA
425 Posts

Posted - 02/11/2014 :  16:35:22  Show Profile  Reply with Quote
Best is to add a computed column to the table.

ALTER TABLE dbo.tablename
ADD column3 AS CAST(CASE WHEN column1 >= 5 THEN 1 ELSE 0 END AS bit)
Go to Top of Page

shilpash
Posting Yak Master

103 Posts

Posted - 02/11/2014 :  17:03:10  Show Profile  Reply with Quote
Thank you Scott but I was trying to understand the select 1 functions why this query populates everything--
if exists(select 1 from tablename where column1=>5)
begin
update tablename
set column3=1


This one updates the whole table as column3=1.I believe this query looks the whole table and if it matches the condition then it populates the whole table.Is there a tweak in this query using select 1 for the same results.

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 02/12/2014 :  00:57:21  Show Profile  Reply with Quote
quote:
Originally posted by shilpash

Thank you Scott but I was trying to understand the select 1 functions why this query populates everything--
if exists(select 1 from tablename where column1=>5)
begin
update tablename
set column3=1


This one updates the whole table as column3=1.I believe this query looks the whole table and if it matches the condition then it populates the whole table.Is there a tweak in this query using select 1 for the same results.




Yes thats correct
it just searches whole table and if condition becomes true if it finds AT LEAST ONE record with column1 > = 5
and your update part has no condition check so it goes ahead and does update on all rows
What you actual need is below as you need to check the condition for EACH ROW and do update only if column1 OF THAT ROW satisfies the condition
so

update tablename
set column3=1
where column1>=5


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

shilpash
Posting Yak Master

103 Posts

Posted - 02/12/2014 :  14:03:00  Show Profile  Reply with Quote
Thank you Visakh,I see now.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 02/13/2014 :  08:09:09  Show Profile  Reply with Quote
welcome

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