updating databases

[woaku]

Hi!

My problem is that I wish to update a database through a visual basic application.
The database I wish to update has two columns "name" and "date".
There could be more than one instance of the same name in a field. For example you can have the name "Jim" about 4 times, but for every name Jim,there is only one "date" value. That means that name and date will make a unique field. The issue is that for every name, I would like to update only the record that
corresponds to the latest date. So if the " name" field has many names and the name "jim" occurs 4 times with corresponding dates such as 1980, 1970, 1960 and 1990, I only want to change the 1990. Is there anyway i can do
this through an SQL. I was able to run a select SQL which selects the
latest dates group by name. However, this cannot work with UPDATE as a subquery.
Hope you can help me out. Thanks.

Chris [sig][/sig]

 

[TNN]

Chris,
I suggest you move this thread over to the SQL server Forum.
TNN, Tom [sig]<p>TOM<br><a href=mailto:WWW.TNPAYROLL@AOL.COM>WWW.TNPAYROLL@AOL.COM</a><br>[/sig]

 

[NevG]

Woaku !

This is no really that difficult although I may not be quite intepruting the sitaution here.

I would simply write a stored procedure saying

UPDATE Table SET (Name = Var1,Date = Var2)
WHERE Name = ['Param1'] AND Date = [#Param2#];

The square brackets may not be necessary though !!
Try it you never know ! [sig][/sig]

 

[gantibabu]

Hi chris, wouldnt this work.

Scenario :

i) First i would retrieve the record which has the max date. Here i should get 1990.
something like (Select max(year) where name = 'Jim') should do this.
maxyear = max(year)[what ever may be the value]
ii) Then i would use this value in my update statement
something like (Update tablename set year = (newyear) or name = (newname) where year = maxyear)

all the best...vijay.