Single Update Query for.....

[Sanjay K Gupta]

create table trns.a(id int,name varchar(20))
insert into TRNS.a values(1,'A')
insert into TRNS.a values(2,'B')
insert into TRNS.a values(3,'C')
insert into TRNS.a values(4,'D')
insert into TRNS.a values(5,'E')
insert into TRNS.a values(6,'F')

select *from a
if you run select Query, you will get
id name

1 A
2 B
3 C
4 D
. .
. .

Q. I need to Update the above table in single update statement to get result like
select *from a

id name

1 B
2 A
3 D
4 C
. .
. .

Note: I know the solution, So I have put this question under "Clever Code".

[Luc Pattyn]

UPDATE trns.a SET id=1+((id-1)^1)

:)

Luc Pattyn [My Articles] Nil Volentibus Arduum

[Sanjay K Gupta]

You have updated the Id. What would you do if there may some more columns like Address and Mobile.... Only Update Name column. The result should be.... Id Address Name --------------------------------------- 1 Address1 B 2 Address2 A 3 Address3 D 4 Address4 C . .... . . .... .

[Luc Pattyn]

you could do similar exor operations on the ASCII ordinal of a 1-character "name" field. :)

Luc Pattyn [My Articles] Nil Volentibus Arduum

[Sanjay K Gupta]

Thank you Sir! Today I have learned new things about Bitwise XOR. For updating name column, the query may be like .. UPDATE trns.a SET Name=CHAR(1+((ASCII(Name)-1)^1)). But if the Name column is not a single character eg ('Alexzendar','Bob','Caldle','Delle','Ellen','Fusion')??

[Luc Pattyn]

Then you would need a different approach, and a better example. BTW: this forum is for showing existing clever code, not for asking questions that may need clever code to get resolved. As such it is the antipode of the "Hall of Shame". :)

Luc Pattyn [My Articles] Nil Volentibus Arduum

[Al_Brown]

Slight modification of Luc's idea:

UPDATE a INNER JOIN a AS a2 ON a.id = 1+((a2.id-1)^1) SET a.name = a2.name

[Sanjay K Gupta]

No, your query is giving error.. Incorrect syntax near the keyword 'INNER'.

[Al_Brown]

Which database system are you using? Try it without the word INNER.

[Sanjay K Gupta]

SQL Server 2008

[Al_Brown]

Ah, OK. In that case use:

UPDATE a SET a.name = a2.name FROM a INNER JOIN a AS a2 ON a.id = 1+((a2.id-1)^1)

[Sanjay K Gupta]

Clever Code!! That is what I was for looking for. Thanks. .. But here is id in sequence. Consider the condition when Id column is not present then how could you update Odd row to Next Even Row and Even Row to Previous Odd Row.

[Al_Brown]

This is just theoretical, right....? You could do something like this:

UPDATE a SET name = (SELECT TOP 1 a2.name FROM a AS a2
WHERE a2.id * ((a.id & 1) * 2 - 1) > a.id * ((a.id & 1) * 2 - 1)
AND (a2.id ^ a.id) & 1 = 1
ORDER BY a2.id * ((a.id & 1) * 2 - 1))

But then you're going into Hall Of Shame territory

[PIEBALDconsult]

You could have made this a Friday Programming Quiz. UPDATE a SET id = CASE WHEN id/2.0=CAST(id/2.0 AS INTEGER) THEN id-1 ELSE id+1 END

[PIEBALDconsult]

That was not part of the specification.

[ian dennis 0]

Surely anything to the power of 1 is itself? so (id-1)^1 = id-1

[Luc Pattyn]

ian dennis wrote:

anything to the power of 1 is itself

yes

ian dennis wrote:

so (id-1)^1 = id-1

no, that is simply never true in the programming languages I use. Including SQL. :)

Luc Pattyn [My Articles] Nil Volentibus Arduum

---

Fed up by FireFox memory leaks I switched to Opera and now CP doesn't perform its paste magic, so links will not be offered. Sorry.