CODEWITHSUNDEEP
sql
Anon Ymouse
Anon Ymouse

Reputation: 101

Update table using newest value in another table

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

Upvotes: 1

Views: 82

Answers (2)

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726479

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.

Upvotes: 1

Patrick Hofman
Patrick Hofman

Reputation: 156918

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'

Upvotes: 1

Related Questions