Question 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.

 

[Menthos]

Hi,

Not sure what the .NET stance is with this as it appears to be a straight general SQL problem that could be handled with a query and, if you want this run as part of a .NET app, handle the query through code.

From the SQL side - you list three records in your first table... are the CardNo's to be treated as unique? What's the relevance of the status field?

 

[cirno]

Reply to thread:Updating data from 2 rows to 2 columns

Hello Menthos, thank you for the fast reply.

I'm not sure how to tackle this problem, because I have a hard time finding the right SQL query to solve the problem in one step.

CardNo's are treated as unique, and as for the status field, they indicate whether the card can be used or not.

This problem actually comes up when a customer register a membership (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, she (Cirno) 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:

SELECT mcardno 
FROM membercardlist 
WHERE AccountNo = 'A009' 
AND STATUS = 'inactive'

and it returns:

 ------------
|    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.

Really appreciate the help, thanks.

 

[cirno]

Sorry, the table membercardlist is actually like this:

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

 

[Menthos]

Apologies on not getting back quicker.

My MySQL is a bit rusty, but couldn't you tag a 'rownumber' to your membercardlist query? Something along the lines of an example from here

SELECT t.CardNo, @rownum = @rownum + 1 AS rank
  FROM TABLE t, (SELECT @rownum := 0) r

Then insert your sub cards based on their @rownum from the query.

The longer route round would be to read the cardNo's into a datatable, then step through each row and either build a SQL insert based on the row contents, or handle each row individually and insert into the correct field accordingly.

Hope that helps!

 

[cirno]

Menthos,

SELECT t.cardno, @rownum = @rownum + 1 AS rank 
FROM membercardlist t,(SELECT @rownum := 0) r 
WHERE accountno = 9999 
AND status = 'inactive'

I tried this query and the result is:

 -----------  --------- 
|    CardNo  |   Rank   |
 -----------  --------- 
|      B009   |      0     |
 -----------  --------- 
|      C009  |       0    |
 -----------  --------

Is that how it should've worked?

 

[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.