Challenge - Swap two columns

Ok .. a challenge ...
How do you swap two columns based on some condition without using a cursor ( I mean a set based solution).alter table foo add tempcol

update foo set tempcol=field1

update foo set field1 = field2

update foo set field2=tempcol

alter table drop column tempcol

you did not say it had to be good, you did not say it had to be efficient, you said merely no cursor|||did you forget it is not possible to add a column and update it in the same sql batch ...|||forget? no, because i never knew that to begin with

okay, so just run those steps in separate batches|||forget? no, because i never knew that to begin with

You are joking ? Right ?|||not in the slightest, no, i was not joking

i don't know what a "batch" is

i just know it's something you DBAs concern yourself with

is a batch like a transaction block? isn't there a COMMIT statement you can use?|||not in the slightest, no, i was not joking

i don't know what a "batch" is

i just know it's something you DBAs concern yourself with

is a batch like a transaction block? isn't there a COMMIT statement you can use?
"Go" and Read up on "GO" statement in the holy book ...
Also
Try this

use pubs
go
create table Mytable99 (a int)
go
insert into Mytable99 select 1
go
begin tran
alter table MyTable99
add b int
commit tran
update MyTable99 set b = 2
go

Then Try this

drop table MyTable99
go
use pubs
go
create table Mytable99 (a int)
go
insert into Mytable99 select 1
go
begin tran
alter table MyTable99
add b int
commit tran
go
update MyTable99 set b = 2
go

drop table MyTable99
go|||Try this
use pubs
go
create table Mytable99 (a int)
go
insert into Mytable99 select 1
go
begin tran
alter table MyTable99
add b int
commit tran
update MyTable99 set b = 2
go

Error: Invalid column name 'b'. (State:S0022, Native Code: CF)

Then Try this
drop table MyTable99
go
use pubs
go
create table Mytable99 (a int)
go
insert into Mytable99 select 1
go
begin tran
alter table MyTable99
add b int
commit tran
go
update MyTable99 set b = 2
go

drop table MyTable99
go

This command did not return data, and it did not return any rows

sweet

and your point is?

and how does this swap two columns??????|||I am new to SQL Server so maybe I am misunderstanding the requirement but is there anything wrong with the following?

update table99 set a=b, b=a

with an optional where condition.|||I am new to SQL Server so maybe I am misunderstanding the requirement but is there anything wrong with the following?

update table99 set a=b, b=a

with an optional where condition. At least the way that I understand Enigma's question, your answer is exactly what I would have suggested. The part that I'm still fuzzy on is that the rest of this thread seems to have veered off on a tangent, which makes me wonder if I understood the original question.

-PatP|||That was the answer I was looking for ... coz I was intrigued by the way sql server handles updates in this case

R937 : the point is the sql batch ... all statements in a sql batch are compiled into one execution plan ... and you cannot alter and update in the same sql batch ... Transaction has no effect on a sql batch ...|||I am new to SQL Server so maybe I am misunderstanding the requirement but is there anything wrong with the following?

update table99 set a=b, b=a

with an optional where condition.

Not a shining moment for the gurus of dbforums.com...|||Yep. For those geeky enough to care, the ISO standard calls for all of the RValues in an UPDATE statement to evaluate before any of the LValues are resolved. This means that all of the computations have to be complete before the first change is allowed to happen.

-PatP