Updating data from 2 rows to 2 columns

[cirno]

Dear Codegurus,

How do I updatezz data from 2 rows to 2 columns?
tool: vb.net
server: MySQL (SQLyog)

I have 2 tables, membercardlist and memberinfo:

table membercardlist:

------------- ------------ ------------
|  AccountNo  |    CardNo  |   status   |
 ------------- ------------ ------------
|       9999  |      B009  |  inactive  |
 ------------- ------------ ------------
|       9999  |      C009  |  inactive  |
 ------------- ------------ ------------
|       9999  |      C009  |   active   |
 ------------- ------------ ------------

table memberinfo:

-------- ------------- ------------ ------------ ------------ ------------
|  Name  |  AccountNo  |  MainCard  |  SubCard1  |  SubCard2  |    Expiry  |
 -------- ------------- ------------ ------------ ------------ ------------
| Chilly |       9999  |      A009  |    (null)  |     (null) |   11/2011  |
 -------- ------------- ------------ ------------ ------------ ------------

How do I insert B009 and C009 from membercardlist into memberinfo like this:

result:

-------- ------------- ------------ ------------ ------------ ------------
|  Name  |  AccountNo  |  MainCard  |  SubCard1  |  SubCard2  |    Expiry  |
 -------- ------------- ------------ ------------ ------------ ------------
| Chilly |       9999  |      A009  |      B009  |      C009  |   11/2011  |
 -------- ------------- ------------ ------------ ------------ ------------

please help me.

[cirno]

Sorry, membercardlist is actually like this:

table membercardlist:

------------- ------------ ------------
|  AccountNo  |    CardNo  |   status   |
 ------------- ------------ ------------
|       9999  |      B009  |  inactive  |
 ------------- ------------ ------------
|       9999  |      C009  |  inactive  |
 ------------- ------------ ------------
|       9999  |      A009  |   active   |
 ------------- ------------ ------------

Please help..

[Unhnd_Exception]

dim con as new sqlceconnection
dim cmd as new sqlcecommand

cmd.connection = con
cmd.commandtext = "Update MemberInfo Set Set SubCard1 = @card1, SubCard2 = @Card2 Where AccountNo = 9999 and name = 'Chilly'"

cmd.parameters.addwithvalue("@card1","B009")
cmd.parameters.addwithvalue("@card2","C009")
try
con.open
cmd.executenonquery
con.close
catch ex as exception
finally
con.dispose
cmd.dispose
end try

Didn't it run it but should work

[Unhnd_Exception]

9999 and Chilly should have also been parameritized

Where AccountNo = @accountNumber and name = @name"
cmd.parameters.addwithvalue("@accountNumber",9999)
cmd.parameters.addwithvalue("@name","Chilly")

[cirno]

Unhnd_Exception, thanks for your help, I really appreciate it. Your code has given me the solution for one of the problems I am facing.

This problem actually comes up when a customer register a membership (e.g:hypermarket) and creates 3 membership card- 1 main card and 2 subcard; with expiry date 10/2010. the expiry date for the main card is inherited into the subcards meaning if main card expires, all card would expire. subcards do not own an expiry date, they share the expiry date with main card as you can see in table 'memberinfo'.

The following year, Chilly renews her membership for another year, but only renews her main card- the subcards would then be 'inactive'- and her expiry date for main card got updated to 10/2011. therefore, the field 'subcard1' and 'subcard2' is updated to '(null)'.

After the transaction has been made, she cancels the decision and said she may be needing the subcards too this year, and will do the renewal for the 3 cards later. therefore, the changes need to be reverted, and the subcard1, subcard2 fields must be updated again so that it contains 'B009' and 'C009' again.

It doesn't matter which card goes into which field(subcard1 can contain C009 and subcard2, B009) but the point is I need to have these field updated with the subcardno data again...

The furthest I get is to use this query:
Code:

SELECT cardno 
FROM membercardlist 
WHERE AccountNo = 9999' 
AND STATUS = 'inactive'

and it returns:

Code:

------------
|    CardNo  |
 ------------
|      B009  |
 ------------
|      C009  |
 ------------

..and after that, maybe store those data in 2 hidden labels in the form (vb) then updates memberinfo with them. I don't know how to code that, though. I know in VB, there is the HasRows() function, but I don't think it helps much..

Really appreciate the help, thanks in advance.

[Netcode]

instead, make a relationship between both tables.

[cirno]

SELECT sub1.subcardz1, sub2.subcardz2
FROM (SELECT CardNo AS subcardz1 FROM membercardlist WHERE AccountNo = '9999' AND status = 'inactive' ORDER BY CardNo ASC) sub1, 
(SELECT CardNo AS subcardz2  FROM membercardlist WHERE AccountNo = '9999' AND status = 'inactive' ORDER BY CardNo DESC) sub2
WHERE subcardz1 <> subcardz2
LIMIT 1

This solves the problem for me.

then i just need to have some object like labels in the form to store the data of subzcard1 and subzcard2. after that, update table memberinfor using this set of data.

thanks guys, without the help here i wouldnt be able to get this solved.