0

My table looks like this,

    RangeId    CellId  Some Coulmns more 
       101        1
       101        2

I ll get a list with [101,2],[101,1] now i have to swap cellId values in the above table. How to write an update query for this. I went through Swapping column values in MySQL but this swaps between two coulmns. Any suggestion..

EDIT: I am swapping the cells in my app and i ll get two cell ids. I have two just swap 2 with 1 and 1 with 2 and rest of the values in the rows remains the same

EDIT2: The table doesnt have any Id column nor a primary key.

Community
  • 1
  • 1
ACP
  • 34,682
  • 100
  • 231
  • 371
  • Are you trying to swap around the cellid values for rows with the same CellID values? Having trouble understanding what you are trying to do. – Simon at The Access Group Mar 27 '13 at 09:20
  • @Simon i am swapping the cells in my app and i ll get two cell ids. I have two just swap 2 with 1 and 1 with 2 and rest of the values in the rows remains the same – ACP Mar 27 '13 at 09:23
  • Ok so the ID is independent / PK (probably just getting confused from the use of "1" and "2" in multiple places) – Simon at The Access Group Mar 27 '13 at 09:34
  • @Simon Id is independent i wont use Id coulmn for the update ... – ACP Mar 27 '13 at 09:37
  • @Simon As of now i have list `[101,2],[101,1]` – ACP Mar 27 '13 at 09:38
  • RangeId and cellId i just need to swap the values say if get 2,1 and i have two swap cellids 1 with 2 and 2 with 1 .. Hope you get now what am i trying to do .. – ACP Mar 27 '13 at 09:39

1 Answers1

0

With your table as currently stated you cannot really do as you wish as there is no unique way to identify rows. I advise that you step back and look at what you are trying to do as it feels like either a: it's not been thought through, or b: you've not given enough information for this to really be solved

If b:, please provide more information on this table and the tables it links to and precisely what you are trying to achieve (yes I know you want to swap 2 numeric values however without knowing more information about the tables / what can be used to select it is VERY hard to advise accurately)

note below was written for OPs original edit

This isn't a nice way to do it but it may get what you are after, it relies on ID being a PKID

http://sqlfiddle.com/#!2/0c48c/2

Simon at The Access Group
  • 6,932
  • 22
  • 45
  • but my cellId coulmn is not a primary key.. It may not have unique value. For example RangedId = 102 the cellId coulmn may still have (1,2) ... – ACP Mar 27 '13 at 09:49
  • Without a method to identify single rows, you cannot do as you are trying to do any way but manually as far as I know. Is there a primary key on this table that can be used? – Simon at The Access Group Mar 27 '13 at 09:52
  • thats what we dont have a primary key field in this table. – ACP Mar 27 '13 at 09:56
  • Then what is Id, does that link to another table? Is this an n:m linking table? I'm afraid past the above there's not much more I can say without further information. – Simon at The Access Group Mar 27 '13 at 10:02
  • Is there anything at all unique about this table? Right now it seems you want to change from having 2 rows which contain (101,1),(101,2) to 2 rows which contain (101,1),(101,2). – Simon at The Access Group Mar 27 '13 at 10:14