joins... - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

joins...

Author

Topic

eddie
Starting Member

45 Posts

Posted - 2002-04-22 : 16:05:56

It must be Monday because I am having a brain cramp. I have two tables,
t1 contains an cust_id and a date column.
t2 contains an id and a date column.

T1 has only one row per customer, t2 contains several rows for each customer. I need to update t1.date with the minimum date per customer from t2. T1.cust_id=t2.id

So in T2 if I have these rows

id date
12541 02/01/2000
12541 01/01/1999
12541 03/02/1997

I need to update with 12541 row in t1 setting the date to 03/02/1997
How can I do this?

Thanks,
Eddie

skond
Yak Posting Veteran

55 Posts

Posted - 2002-04-22 : 17:41:15

update t1
set t1.data = A.date
from
t1,
(
select t2.id,min(date) as min_data
from t2
) A
where t1.cust_id = t2.id

skond
Yak Posting Veteran

55 Posts

Posted - 2002-04-22 : 17:42:17

-- correct the last post , add group by
update t1
set t1.data = A.date
from
t1,
(
select t2.id,min(date) as min_data
from t2
group by t2.id
) A
where t1.cust_id = t2.id

skond
Yak Posting Veteran

55 Posts

Posted - 2002-04-22 : 17:43:21

--one more correction, use A.min_date
update t1
set t1.data = A.min_date
from
t1,
(
select t2.id,min(date) as min_data
from t2
) A
where t1.cust_id = t2.id

skond
Yak Posting Veteran

55 Posts

Posted - 2002-04-22 : 17:44:27

--one more correction, join using A.id
update t1
set t1.data = A.min_date
from
t1,
(
select t2.id,min(date) as min_data
from t2
) A
where t1.cust_id = A.id

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-04-22 : 18:21:53

Skond,

There is a "edit" facility on SQLTeam....

DavidM

Tomorrow is the same day as Today was the day before.

eddie
Starting Member

45 Posts

Posted - 2002-04-23 : 08:43:42

Thanks, that appears to work!

Thanks,
Eddie

skond
Yak Posting Veteran

55 Posts

Posted - 2002-04-23 : 09:13:14

quote:

--one more correction, join using A.id
update t1
set t1.data = A.min_date
from
t1,
(
select t2.id,min(date) as min_data
from t2
group by t2.id
) A
where t1.cust_id = A.id

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources