earwig75
asked on
Update one table with results from another table in SQL
Hello, I have 2 tables and I need to update the results in tableOne with the results from tableTwo where the IDs match. My problem is, tableTwo can have multiple entries with the same ID, so I want to use the record in tableTwo with the max(dateCreated). Can someone help me alter the query below to accomplish this?
UPDATE tableOne
SET tableOne.col1 = tableTwo.col1
FROM tableOne, tableTwo
WHERE tableOne.ID = tableTwo.ID
ASKER
Thank you for the response but this didn't seem to work. It didn't error, but the column didn't update with the correct data.
I should have mentioned, but the column in tableTwo has a different name than the column in tableOne. For this sample, the column in tableTwo is called col3. Below is what I ran, but did not work.
I should have mentioned, but the column in tableTwo has a different name than the column in tableOne. For this sample, the column in tableTwo is called col3. Below is what I ran, but did not work.
UPDATE t1
SET t1.col1= t2.col3
FROM tableOne t1
INNER JOIN (SELECT ID, col3, MAX(dateCol) MaxDate
FROM tabletwo
GROUP BY ID, col3) t2 ON t1.ID = t2.ID
You gave only a standard example so we can only give you a standard answer.
If you have a specific case you need to provide specific info and include sample data for both tables.
If you have a specific case you need to provide specific info and include sample data for both tables.
ASKER
Below are examples of the data and tables.
I want to copy the value from col3, tableTwo with the max date to the record in tableOne with the same ID. tableOne can only have 1 record per ID.
In the example below, the query should update tableOne, record 105 with "blue" from col3 in tableTwo, because Blue has the newer/max date. Thanks again.
tableOne Sample:
ID col1
-------------------
105 green
tabletwo Sample:
ID col3 dateCol
-------------------------- -----
105 green 2017-03-16 08:25:44.687
105 blue 2017-03-16 08:27:44.687
I want to copy the value from col3, tableTwo with the max date to the record in tableOne with the same ID. tableOne can only have 1 record per ID.
In the example below, the query should update tableOne, record 105 with "blue" from col3 in tableTwo, because Blue has the newer/max date. Thanks again.
tableOne Sample:
ID col1
-------------------
105 green
tabletwo Sample:
ID col3 dateCol
--------------------------
105 green 2017-03-16 08:25:44.687
105 blue 2017-03-16 08:27:44.687
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
You're awesome; that worked, thank you!
Open in new window