sql - Update not working properly with derived table -

sql - Update not working properly with derived table -

please see query below:

update dbusns set thisdate = created (select max(created) created, dbcustody.reference dbusns inner bring together [server].custody.dbo.dbcustody on dbusns.urns = dbcustody.reference dbusns.datasetname = 'custody' grouping dbcustody.reference) custodydatetable dbusns.urns = custodydatetable.reference , dbusns.urns = '1'

the next query returns 01/01/2011:

select max(created) created, dbcustody.reference dbusns inner bring together server.database.dbo.dbcustody on dbusns.urns = dbcustody.reference dbusns.datasetname = 'custody' , dbcustody.reference = '1'

however, next query homecoming 31/10/2011 (after query 1 run):

select thisdate dbusns datasetname = 'custody' , urns = '1' --query 3

the query below returns 2 rows (31/10/2011 , 01/11/2011):

select created [server].custody.dbo.dbcustody reference = '1'

why query 3 homecoming 31/10/2011? should homecoming 01/11/2011? execution plan/linked server?

your update syntax seems wrong want. in case, best way utilize inner join:

update d set thisdate = t.created dbusns d inner bring together (select max(created) created, c.reference dbusns inner bring together [server].custody.dbo.dbcustody c on dbusns.urns = dbcustody.reference dbusns.datasetname = 'custody' grouping dbcustody.reference) t on d.urns = t.reference d.urns = 1

sql sql-server-2008