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

General SQL Server Forums

New to SQL Server Programming

IF plus UPDATE

Author

Topic

Neftoma
Starting Member

12 Posts

Posted - 2012-08-30 : 06:07:54

Hi everyone,

Is there a way to write the same in one statement using IF:

update table1 set E=1
where a=b;

update changes131 set E=0
where a!=b;

Thank you!

sql-programmers
Posting Yak Master

190 Posts

Posted - 2012-08-30 : 06:22:58

Try this

update table1 set E= (case when a=b then 1 else 0 end)

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

shaggy
Posting Yak Master

248 Posts

Posted - 2012-08-30 : 06:28:18

update table1 set E = Case when a=b then 1
when a!=b then 1
else E
END

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-08-30 : 06:36:36

[code]UPDATE dbo.Table1
SET E = CASE A
WHEN B THEN 1
ELSE 0
END;

UPDATE dbo.Table1
SET E = 1 - ABS(SIGN(A - B));[/code]

N 56°04'39.26"
E 12°55'05.63"

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-30 : 06:40:25

quote:
Originally posted by Neftoma

Hi everyone,

Is there a way to write the same in one statement using IF:

update table1 set E=1
where a=b;

update changes131 set E=0
where a!=b;

Thank you!

If I read your requirement correctly, you are trying to update two different tables - table1 and changes131. You cannot do both updates in a single statement. What you have shown is the correct and perhaps the only thing that you can do in all except very specialized cases.

The only common thing between the two tables based on your example is that they both have similarly named columns, E, a and b.

Neftoma
Starting Member

12 Posts

Posted - 2012-08-30 : 07:09:24

Sorry, this is the same table, I just tried to make the query more general:

update table1 set E=1
where a=b;

update table1 set E=0
where a!=b;

quote:
Originally posted by sunitabeck

quote:
Originally posted by Neftoma

Hi everyone,

Is there a way to write the same in one statement using IF:

update table1 set E=1
where a=b;

update changes131 set E=0
where a!=b;

Thank you!
If I read your requirement correctly, you are trying to update two different tables - table1 and changes131. You cannot do both updates in a single statement. What you have shown is the correct and perhaps the only thing that you can do in all except very specialized cases.

The only common thing between the two tables based on your example is that they both have similarly named columns, E, a and b.

shaggy
Posting Yak Master

248 Posts

Posted - 2012-08-30 : 07:12:16

I think this will work

update table1 set E = Case when a=b then 1
when a!=b then 0 else E
END

Neftoma
Starting Member

12 Posts

Posted - 2012-08-30 : 07:14:33

Thank you! Works perfectly!

quote:
Originally posted by sql-programmers

Try this

update table1 set E= (case when a=b then 1 else 0 end)

SQL Server Programmers and Consultants
http://www.sql-programmers.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources