Update table using newest value in another table

Question

So we had a bit of an issue where an entire tables unique "CRC" was set to "N/A", luckily we have another table that stored multiple versions of "CRC" and if we use this we should be able to pull back the original "CRC"s

my basic concept is

UPDATE Table1
SET Table1.CRC = History.CRC
FROM Table1 JOIN History ON Table1.PhoneNum = History.PhoneNum
where Table1.field2 = 'Uniquefield2 here'

The problem with this is I don't know what CRC's are being pulled

I want to pull the newest History.crc based on the column "calldatetime" and update Table1.CRC with this value

Source of query above Update table based on all values in another table as parameters

what format does crc have? if it's a basic integer value then select Max(t1.crc) from table t1 where .... will get you the one you are looking for, or if you have an auto incrementing id in your tables then it'll be easy aswell — Jeremy C., Jun 03 '15 at 10:07

score 1 · Accepted Answer · answered Jun 03 '15 at 10:07

If you have an ID field you can search for the maximum ID and only return the rows with the highest ID, grouped on a field you wish.

I used an inline view here to get the maximum IDs per PhoneNum:

update Table1
SET    Table1.CRC = History.CRC
FROM   Table1
JOIN   History
ON     Table1.PhoneNum = History.PhoneNum
JOIN   ( select PhoneNum
         ,      max(id) id
         from   history
         group
         by     PhoneNum
       )
       History_MAX
on     History.ID = History_MAX.id
where  Table1.field2 = 'Uniquefield2 here'

@Patrick_Hofman you saved my bacon again, half a year later haha. — , Dec 14 '15 at 11:12

score 1 · Answer 2 · answered Jun 03 '15 at 10:08

You can fix your query by adding a condition that no records with later calldatetime exist in the History table:

UPDATE Table1
SET Table1.CRC = History.CRC
FROM Table1 JOIN History h ON Table1.PhoneNum = History.PhoneNum
WHERE Table1.field2 = 'Uniquefield2 here'
  AND NOT EXISTS (
    SELECT * FROM History hh
    WHERE hh.PhoneNum=h.PhoneNum AND hh.calldatetime > h.calldatetime
)

This query adds an alias h to the History table from your query, and requires that no records with later calldatetime exist in a coordinated subquery inside the EXISTS expression.

Thanks for the help, I used the other guys answer though :D – Anon Ymouse Jun 03 '15 at 10:14 — Anon Ymouse, Jun 03 '15 at 10:14

Update table using newest value in another table

2 Answers2

Linked