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 quer开发者_开发技巧y. 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.
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
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
精彩评论