Update a table with records from another table

Question

I am trying to update my table 1 with some field value from table 2 based on a condition. But am unable to get the proper query. Here's the condition:

I have a table 1 with date field which should be updated from table 2 date field. The condition is that the id of table 1 should be equal to id of table 2 (Table 2 has id of table 1 as FK). Another condition should be there is a varchar field in table 2 which should have specific value, say "Test". Wherever the value of field in table 2 is "Test" I want to update the date of with that record in table 1 date field. But there is another catch. It may be possible that more than 1 records for same id in table 2 can have value as "Test"

I was trying the query as:

UPDATE A
SET A.Date = Max(B.[Date])
FROM Table1 A
 INNER JOIN Table2 B ON A.ID = B.FK_ID
WHERE (B.Changed LIKE 'Test')
 AND A.Date IS NULL

But this is not working as sql does not allow Max in update when there is no group by. Please help. A bit urgent.

Answer 1

You could always use subqueries:

UPDATE Table1 a SET
    [Date] = (SELECT MAX([Date]) FROM Table2 b WHERE a.ID = b.FK_ID AND b.Changed LIKE 'Test')
WHERE [Date] IS NULL

Answer 2

You need to create another inner join where the ID's of Table1 are coupled with the maximum dates of Table2 like so:

UPDATE    Table1
SET       Date = BDate.MaxDate
FROM      Table1 A
          INNER JOIN Table2 B ON A.ID = b.FK_ID
          INNER JOIN (
              SELECT    A.ID, [MaxDate] = MAX(B.Date)
              FROM      Table1 A
                        INNER JOIN Table2 B ON A.ID = b.FK_ID
              GROUP BY  A.ID
          ) BDate ON BDate.ID = A.ID
WHERE     B.Changed LIKE 'Test'
          A.Date IS NULL