Home » Developer & Programmer » Forms » replacing one column value between two rows..help??
replacing one column value between two rows..help?? [message #134891] Sun, 28 August 2005 17:54 Go to next message
ramisy2k
Messages: 150
Registered: April 2005
Senior Member
HI,

i have form in which there is a block of db table T

it has five columns, all are in number data type
Col1 and col2 are jointly primary key i.e. they cannot be repeated combined

when i run this query..

SQL> Select * from t 
it shows me the data like this

Col1        Col2              Col3       Col4           Col5
1             1               78          58            12.76
2             1               128         446           32.10
3             1               468         566           52.10
4             1               878         58            52.05
5             1               987         348           22.02

... so on. 

my requirement is that i want to update this table by replacing the col1 value between any two rows through a button press tigger

suppose this is the first row

Col1        Col2          Col3        Col4         Col5
1             1             78          58        12.76

and this is any 2nd row

Col1        Col2          Col3        Col4        Col5
5             1            987         348       22.02


now i want that for first row the value of col1 replaces the value of col1 of 2nd row
and for 2nd row the value of col1 replaces the value of col1 of 1st row

i.e.

Col1        Col2          Col3        Col4         Col5
5             1            78           58         12.76

Col1        Col2          Col3        Col4         Col5
1             1            987        348          22.02

please tell how to achieve this in single query/procedure

regards,
Ramis
Re: replacing one column value between two rows..help?? [message #134902 is a reply to message #134891] Sun, 28 August 2005 19:05 Go to previous message
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia
Senior Member
Account Moderator
You can't swap the primary keys at the same time. Normally you would change one of the entries to '-1' or '999999' or something else 'special', save it, change the other entry to the original values of the first, save it, then change the 'special' entry to the primary key of the second.

Alternatively, don't change the keys values, swap the OTHER values with code like
temp_rec=a_record
a.col3 = b.col3
a.col4 = b.col4
a.col5 = b.col5
then
b.col3=temp_rec.col3
b.col4=temp_rec.col4
b.col5=temp_rec.col5
then save your form.

David
Previous Topic: Inserting more than 1 record
Next Topic: Problem working with Parent-Child Block
Goto Forum:
  


Current Time: Thu Sep 19 23:15:01 CDT 2024