Using if exists in update - 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 2000 Forums

Transact-SQL (2000)

Using if exists in update

Author

Topic

getfar
Starting Member

15 Posts

Posted - 2005-11-25 : 05:04:12

Hi guys,
i've a little problem.
I have to make a query what needs to use "if exists" into "update"

Is it possible to write:

update tab1
set tab1.att= (if exists(select att from tab2) else '')

Thanks a lot

surendrakalekar
Posting Yak Master

120 Posts

Posted - 2005-11-25 : 06:03:45

You can't use
if exists (select statement...)
update statement
else
else...part...

Surendra

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-25 : 06:34:44

>>if exists(select att from tab2)

Do you want to compare and update?

Update T1 set T1.att=T2.att from Table1 T1 inner join Table2 T2 on T1.kycol=T2.keycol

Madhivanan

Failing to plan is Planning to fail

getfar
Starting Member

15 Posts

Posted - 2005-11-25 : 08:56:14

The problem is that when i write
update set ...= select ....
the system give me NULL if the subquery has not rows.
I don't want NULL in table updated.
How can i write?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-25 : 08:59:33

Use ISNULL
Select ISNULL(col,'') from yourTable

Madhivanan

Failing to plan is Planning to fail

getfar
Starting Member

15 Posts

Posted - 2005-11-25 : 09:13:13

Thanks a lot guys

Kristen
Test

22859 Posts

Posted - 2005-11-25 : 09:29:04

"Select ISNULL(col,'') from yourTable"

That won't do it if there are no rows, will it?

update set ...= COALESCE((Select col from yourTable), '')

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-25 : 09:40:18

>>update set ...= COALESCE((Select col from yourTable), '')

Perfect. Thanks Kris

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources