Conditionnal UPDATE

[LEGEAS]

Hi all !

I'm desperately in need for some help on that one...
I have two tables, say A and B. B is linked to A with a foreign key NUM_A. B contains a series of business actions on A, each one having a close date. A contains an open date and a close date. Now comes the interesting bit :
I would like to update A.close_date with :
- The last B.close_date if it exists.
- A.open_date otherwise.
I've tried quite a lot of things by now, but am really new to SQL Server and am loosing a lot of time there.
Hope somebody can help !
Cheers,

 

[donutman]

Join table A to a derived table that does a Select on table B with a Group By. The Aggregate for the Group By is the Max(Date). Then you can use that Max(Date) to update table A. Need more help?
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[LEGEAS]

Cheers !
Following your advice, I finally got it to work like that, the code having been just a bit modified to fit in this post :

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DATE_CLOTURE_LOTS]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table DATE_CLOSE

create table DATE_CLOSE (numA varchar(9), dateClose datetime)

insert into DATE_CLOTURE_LOTS select num_a, max(date_close) from b group by num_a

UPDATE A set date_close =
ISNULL(dc.dateClose, a.date_open)
FROM A a LEFT JOIN DATE_CLOSE dc ON dc.numA = a.num_a
WHERE a.date_close IS NULL

drop table DATE_CLOSE

I'm leaving it here in case somebody needs it one day...

 

[donutman]

Good job. Now push the envelope a little further! Do it without using a work table. Drived tables are in the following format:

   [Blue]SELECT[/Blue] [Gray]*[/Gray] [Blue]FROM[/Blue] TableA A [Blue]INNER[/Blue] [Gray]JOIN[/Gray]
      [Gray]([/Gray][Blue]SELECT[/Blue] [Fuchsia]MAX[/Fuchsia][Gray]([/Gray]Date[Gray])[/Gray] [Blue]FROM[/Blue] TableB [Blue]GROUP[/Blue] [Blue]BY[/Blue] SomeField[Gray])[/Gray] B 
   [Blue]ON[/Blue] A.x[Gray]=[/Gray]B.x

-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[EdwinGene]

Update TableA
Set close_date =
Case
When tbb.close_date is Not NULL Then
tbb.close_date
Else
tba.open_date
End
From
TableA tba
Left Outer Join TableB tbb
on tba.Num_A = tbb.Num_B

 

[EdwinGene]

Oops. Forgot about the multiple TableB recs. My solution won't work for that. Hold on a bit.

 

[EdwinGene]

LEGEAS, this should work


Update TableA
Set close_date =
Case
When tbb.close_date is Not NULL Then
tbb.close_date
Else
tba.open_date
End
From
TableA tba
Left Outer Join
(
Select *
From TableB tb1
Where tb1.close_date =
(
Select Max(close_date)
From TableB tb2
Where tb2.Num_B = tb1.Num_B
)
) tbb
on tba.Num_A = tbb.Num_B