Problem with updating table with data from another

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Other SQL Server Topics (2005)

Problem with updating table with data from another

Author

Topic

derozza
Starting Member

13 Posts

Posted - 2010-11-12 : 19:46:41

Hello codemasters,

I am stuck with this problem since 2 days ago...

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:

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

please help me....thanks in advance

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-11-12 : 19:48:37

Are you actually trying to update the table or just want to display the result set like that?

If you are trying to update the table, then I'd have to say that your database design if flawed as you've got repeated data.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

derozza
Starting Member

13 Posts

Posted - 2010-11-14 : 19:58:16

Oh, sorry. Thank you tkizer for pointing it out. My bad. It's actually


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

C009 and B009 are dynamic data, meaning the input depends on the user and SQL's auto-increment system. I'm trying to find something like this:

UPDATE memberinfo 
SET SubCard1 = 'B009', SubCard2 = 'C009' 
WHERE accountno = '9999'

...but with nested SQL queries that returns the values B009 and C009 as a different column, not a different row. This is so that I can update the table memberinfo with the right data. It doesn't have to be subcard1 = B009 and subcard2 = C009, it can also be subcard = C009 and subcard2 = B009, as long as memberinfo got updated with the right data.

derozza
Starting Member

13 Posts

Posted - 2010-11-14 : 22:42:46

Update:

Using this query, I managed to get different cardno for each column.

SELECT sub1.subzcard1, sub2.subzcard2
FROM (SELECT cardno AS subzcard1 FROM membercardlist WHERE AccountNo = '9999' AND STATUS = 'inactive' ORDER BY cardno ASC) sub1, 
(SELECT cardno AS subzcard1 FROM membercardlist WHERE AccountNo = '9999' AND STATUS = 'inactive' ORDER BY cardno DESC) sub2
WHERE subzcard1 <> subzcard2

where it returns the result as follows:


| subzcard1 | subzcard2 |
    B009        C009
    C009        B009

problem is, the row count is still bigger than 1.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-11-14 : 23:29:24

[code]
declare @membercardlist table
(
AccountNo int,
CardNo varchar(5),
status varchar(10)
)

declare @memberinfo table
(
Name varchar(10),
AccountNo int,
MinCard varchar(5),
SubCard1 varchar(5),
SubCard2 varchar(5),
Expiry datetime
)

insert into @membercardlist
select 9999, 'B009', 'inactive' union all
select 9999, 'C009', 'inactive' union all
select 9999, 'A009', 'active'

insert into @memberinfo
select 'Chilly', 9999, 'A009', NULL, NULL, '2011-11-01'

; with inactive_card
as
(
select AccountNo, CardNo,
row_no = row_number() over (partition by AccountNo order by CardNo)
from @membercardlist
where status = 'inactive'
),
up as
(
select AccountNo, SubCard1 = [1], SubCard2 = [2]
from inactive_card
pivot
(
max(CardNo)
for row_no in ([1], [2])
) p
)
update m
set SubCard1 = i.SubCard1,
SubCard2 = i.SubCard2
from @memberinfo m
inner join up i on m.AccountNo = i.AccountNo

select *
from @memberinfo[/code]

KH
[spoiler]Time is always against us[/spoiler]

derozza
Starting Member

13 Posts

Posted - 2010-11-15 : 04:08:35

Sorry for the late reply.

To KHtan: great solution. the bad news is, I'm using MySQL. Which doesn't support WITH clause.

But your solution gave me a new light. I have tried this solution since you posted it, and still trying to convert it to MySQL. If I come up with a result, I'll let you guys know.

Again, Thanks for the solutions. SQL team ROCKS!

derozza
Starting Member

13 Posts

Posted - 2010-11-18 : 01:06:23

[code]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[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources