Single Update Query for.....
-
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 name1 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 aid name
1 B
2 A
3 D
4 C
. .
. .Note: I know the solution, So I have put this question under "Clever Code".
-
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 . .... . . .... .
-
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 . .... . . .... .
you could do similar exor operations on the ASCII ordinal of a 1-character "name" field. :)
Luc Pattyn [My Articles] Nil Volentibus Arduum
-
you could do similar exor operations on the ASCII ordinal of a 1-character "name" field. :)
Luc Pattyn [My Articles] Nil Volentibus Arduum
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')??
-
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')??
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
-
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 name1 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 aid name
1 B
2 A
3 D
4 C
. .
. .Note: I know the solution, So I have put this question under "Clever Code".
-
No, your query is giving error.. Incorrect syntax near the keyword 'INNER'.
-
No, your query is giving error.. Incorrect syntax near the keyword 'INNER'.
-
SQL Server 2008
-
SQL Server 2008
-
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.
-
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.
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
-
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 name1 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 aid name
1 B
2 A
3 D
4 C
. .
. .Note: I know the solution, So I have put this question under "Clever Code".
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 -
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 . .... . . .... .
That was not part of the specification.
-
Surely anything to the power of 1 is itself? so (id-1)^1 = id-1
-
Surely anything to the power of 1 is itself? so (id-1)^1 = id-1
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.