Please see the query below:

```
update dbusns 
set thisdate = created 
from 
    (select 
        MAX(created) AS CREATED, DBCUSTODY.REFERENCE 
     from dbusns 
     inner join [server].Custody.DBO.dbcustody on dbusns.urns = dbcustody.reference
     where dbusns.datasetname = 'CUSTODY' 
     group by dbcustody.reference) As CustodyDateTable 
WHERE 
   dbusns.urns = CustodyDateTable.reference 
   and dbusns.urns = '1' 

```

The following query returns `01/01/2011`:

```
select 
    MAX(created) AS CREATED, DBCUSTODY.REFERENCE 
from 
    dbusns 
inner join 
    server.database.DBO.dbcustody on dbusns.urns = dbcustody.reference
where 
    dbusns.datasetname = 'CUSTODY' AND DBCUSTODY.REFERENCE = '1'

```

However, the following query return `31/10/2011` (after query 1 is run):

```
select 
    THISDATE 
from 
    dbusns 
where 
    datasetname = 'CUSTODY' AND URNS = '1' --QUERY 3

```

The query below returns two rows (31/10/2011 and 01/11/2011):

```
select created 
from [server].Custody.DBO.dbcustody 
where reference = '1'

```

Why does query 3 return 31/10/2011? It should return 01/11/2011? Is this something to do with the execution plan/linked server?
['https://Stackoverflow.com/questions/24311754', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/937440/']
Your `UPDATE` syntax seems wrong for what you want. In this case, the best way would be to use an `INNER JOIN`:

```
UPDATE D
SET thisdate = T.created 
FROM dbusns D
INNER JOIN (SELECT  MAX(created) created,
                    C.reference 
            FROM dbusns 
            INNER JOIN [server].Custody.DBO.dbcustody C
                ON dbusns.urns = dbcustody.reference
            WHERE dbusns.datasetname = 'CUSTODY' 
            GROUP BY dbcustody.reference) T
    ON D.urns = T.reference
WHERE D.urns = 1

```
I think your `update` syntax is right, except for one small thing. Consider this line:

```
update dbusns set thisdate = created from ( . . .

```

The `created` column -- I am guessing -- is in `dbusns`. So, it is just setting `thisdate` to the created value i the same table.

You can fix this by using a table alias:

```
update dbusns set thisdate = CustodyDateTable.created from ( . . .

```