Selecting min(date)

[dreameR25]

OK.... I will try and explain this as best as I could.

I have a table consisting of two columns, member_key and a date. Each record is unique combination of member_key and a date.

I also have a second table which hold multiple records for each member_key. For example,

Table1

Member_key | Date1
1000 | 01/01/2001

Table2
Member_key | Contribution_date
1000 | 01/01/1998
1000 | 01/01/1978
1000 | 01/01/1993
1000 | 01/01/2002

What I need to do is update the second table and replace the min(contribution_date) with the date that is in table1. So in other words, replace 01/01/1978 with 01/01/2001 which is the date in Table1.

Any ideas?

Thanks.

 

[carp]

UPDATE table2 t2
SET contribution_date = (SELECT date1
FROM table1 t1
WHERE t1.member_key = t2.member_key)
AND contribution_date = (SELECT min(contribution_date)
FROM table2 t22
WHERE t2.member_key = t22.member_key);

I THINK this will work (I haven't tested it and I STILL haven't had any coffee!); whether or not it is the most efficient approach remains to be seen.

 

[dreameR25]

Yes but I can already see that the query does not work.... the (AND contribution_date) but would not work as there is no FROM statment before it.
But your idea is correct...

below is the vesrion that I came up with, by the way, table1 is ch_min and table2 is ch.
dst is contribution_date.

update ch
set ch.dst = (select ch_min.dst from ch_min
where ch_min.Member_key = ch.Member_key
and ch.dst =
(select min(t2.dst) from ch t2 where t2.Member_key = ch.Member_key))

 

[blom0344]

Totally different approach:

(Select Member_key,Contribution_date from table)
Minus
(Select Member_key,Min(Contribution)date from table group by Member_key)

Step 2:

Insert table 1 into table 2

I did not test this.............. T. Blom
Information analyst
tbl@shimano-eu.com