CODEWITHSUNDEEP
sqlsql-server
user2722043
user2722043

Reputation: 53

Updating SQL Table with MAX DATE

I am trying to get Max(year) from a date field to Varchar field by returning only the Year I hope some one could help.

this what I have so far.

  UPDATE MasterDraft
            SET 
            Last_title_changed = (select Max((deed_dt) ) 
            From APPRAISAL_Deed_History )
            WHERE MasterDraft.Prop_ID = APPRAISAL_Deed_History.prop_id

this my source Table:

+---------+------------+
| prop_id |  deed_dt   |
+---------+------------+
|      40 | 1988-07-22 |
|      40 | 1984-03-01 |
|      40 | 2005-07-12 |
|      40 | 1983-07-01 |
|      40 | 1992-01-31 |
|      40 | 1983-01-01 |
|      40 | 1990-01-31 |
+---------+------------+

Output on MasterDraft.Last_title_Changed should be 2005

Upvotes: 0

Views: 55

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

That should be Max(YEAR(deed_dt))

EDIT: So you want to update all records and use the max year of the prop id? Then move the where clause to your sub select. APPRAISAL_Deed_History.prop_id is not known outside the inner query.

  UPDATE MasterDraft
    SET Last_title_changed = 
    (
      select Max( Year(deed_dt) ) 
      from APPRAISAL_Deed_History 
      where APPRAISAL_Deed_History.prop_id = MasterDraft.Prop_ID
    );

Upvotes: 1

Manoj Naik
Manoj Naik

Reputation: 396

    UPDATE MasterDraft
    SET Last_title_changed = Max(YEAR(deed_dt)
    From APPRAISAL_Deed_History INNER JOIN MasterDraft
    WHERE MasterDraft.Prop_ID = APPRAISAL_Deed_History.prop_id

Upvotes: 0

Related Questions