Link to home
Start Free TrialLog in
Avatar of earwig75
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

Open in new window

Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Something like this, maybe:
UPDATE t1
SET t1.col1 = t2.col1
FROM tableOne t1
	INNER JOIN (SELECT ID, col1, MAX(dateCol) MaxDate
		FROM tableTwo 
		GROUP BY ID, col1) t2 ON t1.ID = t2.ID

Open in new window

Avatar of earwig75
earwig75

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.

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

Open in new window

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.
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
ASKER CERTIFIED SOLUTION
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
You're awesome; that worked, thank you!