Join Two Tables and get the updated information

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Join Two Tables and get the updated information

Author

Topic

alhakimi
Starting Member

23 Posts

Posted - 2013-06-04 : 15:50:35

Dear friends

I have two tables with the same columns one of them is the initial data while the second table is the updates data of the first table means first data gets created in first table and if changes are there it gets stored in the updated data table so i need to get from both tables the data of first table and if data has been changed then the updated data should be returned in the query for example
table 1

column1 column2 column3 column4
1 4000 John 2jun131004pm
2 2000 Jock 1jun131103am
3 3000 Julie 1jun131133am

table2

column1 column2 column3 column
1 6000 John 4jun131103am

so i want the result to be

column1 column2 column3 column
1 6000 John 4jun131103am
2 2000 Jock 1jun131103am
3 3000 Julie1jun131133am

thank you
Hakimi

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-04 : 16:41:11

[code]SELECT
a.column1,
COALESCE(b.column2,a.column2) AS column2,
COALESCE(b.column3,a.column3) AS column3
FROM
Table1 a
LEFT JOIN Table2 b ON a.column1=b.column1;
[/code]

alhakimi
Starting Member

23 Posts

Posted - 2013-06-04 : 17:00:23

Dear James, thank you..
I actually changed little bit in my question i am not sure if you noticed that.
as you can see that i included one column that has the last modification date and time. so i need the last modification to be brought as i might have more than one record in the updated table for the same row number.
thank you

quote:

Originally posted by James K

SELECT
	a.column1,
	COALESCE(b.column2,a.column2) AS column2,
	COALESCE(b.column3,a.column3) AS column3
FROM
	Table1 a
	LEFT JOIN Table2 b ON a.column1=b.column1;

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-04 : 17:22:07

[code]SELECT
a.column1,
COALESCE(b.column2,a.column2) AS column2,
COALESCE(b.column3,a.column3) AS column3,
COALESCE(b.column4,a.column4) AS column4
FROM
Table1 a
OUTER APPLY
( SELECT TOP (1) * FROM Table2 b WHERE a.column1=b.column1
ORDER BY b.column4 DESC ) b
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources