SQL server Update using JOIN

Question

I used this command in Sql Server to update a table using the maximum data in other table innerjoin

        UPDATE dbo.table2
        SET table2.LastGrantDate = max(table1.PlannedProjStartDate)
        from 
        table1 inner join table2
        on table2.Serial = table1.FundingEstablish

but it doesn't work

it workes only when I use it without the MAX() but

any way to solve this ??

But max command will always bring one value.

Krismorte
– Krismorte

2015-07-27 11:55:47 +00:00
Commented Jul 27, 2015 at 11:55 — Krismorte
– Krismorte, Commented Jul 27, 2015 at 11:55

Gordon Linoff · Accepted Answer · 2015-07-27 11:53:24Z

3

You need to use a subquery. Here is one way:

    UPDATE t2
    SET t2.LastGrantDate = t1.maxppsd
    from table2 t2 inner join
         (select FundingEstablish, max(PlannedProjStartDate) as maxppsd
          from table1
          group by FundingEstablish
         ) t1
         on t2.Serial = t1.FundingEstablish;

answered Jul 27, 2015 at 11:53

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Madhivanan · Accepted Answer · 2015-07-27 11:56:04Z

0

Try this

UPDATE t2
        SET t2.LastGrantDate = t1.PlannedProjStartDate
        from 
        table1 as t1 inner join 
(select FundingEstablish,max(PlannedProjStartDate) as PlannedProjStartDate
from table2 group by FundingEstablish) as t2
        on t2.Serial = t1.FundingEstablish

answered Jul 27, 2015 at 11:56

Madhivanan

13.7k1 gold badge28 silver badges30 bronze badges

Collectives™ on Stack Overflow

SQL server Update using JOIN

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related