IF exists(select 1 ... - SQL Server Forums

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)

IF exists(select 1 ...

Author

Topic

shilpash
Posting Yak Master

103 Posts

Posted - 2014-02-11 : 11:50:44

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
Aged Yak Warrior

550 Posts

Posted - 2014-02-11 : 16:35:22

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)

shilpash
Posting Yak Master

103 Posts

Posted - 2014-02-11 : 17:03:10

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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-02-12 : 00:57:21

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

shilpash
Posting Yak Master

103 Posts

Posted - 2014-02-12 : 14:03:00

Thank you Visakh,I see now.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-02-13 : 08:09:09

welcome

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