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.
| Author |
Topic |
|
srinathb42
Starting Member
4 Posts |
 Posted - 2014-11-10 : 17:48:43
|
Hi,I have two tables:
Table A || Table B
ID (Primary Key) || Source_Cust_ID(allows multiple values)
Fname || Fname
Lname || Lname
gender || gender
|| Confirm_date
|| Source1(unique value)
|| Source2(unique value)
Compare Table A with Table B(on A.ID,B.Source_Cust_ID,B.Source1,B.Confirm_date) and UPDATE B.confirm_date to getdate if everything is matched.
Thanks in advance! |
|
|
redhat69
Starting Member
2 Posts |
Posted - 2014-11-10 : 18:46:29
|
So you want to match table A and Table B
A.ID = B.Source_Cust_ID
Then when this matches you want to update the field Confirm_date with the current time stamp? |
 |
|
|
Muj9
Yak Posting Veteran
75 Posts |
Posted - 2014-11-11 : 07:54:11
|
update [TableB]
set Confirm_date = GETDATE()
where Source_Cust_ID in (
SELECT a.[ID]
from dbo.TableA a
left join [dbo].[TableB] b on a.ID = b.Source_Cust_ID) |
|
|
|
srinathb42
Starting Member
4 Posts |
Posted - 2014-11-11 : 09:34:22
|
Thank you for your reply
I have to compare all fields in Table A with all fields in Table B and if every thing matches then update B.confirm_date to getdate.And one Source_Cust_ID may have two confirm_date so have to update only the latest record in B table.
quote:
Originally posted by Muj9
update [TableB]
set Confirm_date = GETDATE()
where Source_Cust_ID in (
SELECT a.[ID]
from dbo.TableA a
left join [dbo].[TableB] b on a.ID = b.Source_Cust_ID)
|
|
|
|
Muj9
Yak Posting Veteran
75 Posts |
Posted - 2014-11-12 : 07:27:44
|
Try this
--Create Tables-----------------------
create table TableA(
ID int Primary key clustered,
Fname varchar(50),
Lname varchar(50),
gender varchar(10))
create table TableB (
Source_Cust_ID int,
Fname varchar(50),
Lname varchar(50),
gender varchar(10),
Confirm_date datetime,
Source1 varchar(10),
Source2 varchar(10))
--Insert Data into tableA---------------------------
insert into TableA
select
1 as ID
,'Alex' as Fname
,'Rooney' as Lname
,'Male' as gender
union all
select
2 as ID
,'Roberto' as Fname
,'Carlos' as Lname
,'Male' as gender
union all
select
3 as ID
,'Pablo' as Fname
,'Aimar' as Lname
,'Male' as gender
union all
select
4 as ID
,'Crespo' as Fname
,'Ken' as Lname
,'Male' as gender
--Insert Data into tableB---------------------------
insert into TableB
select
1 as ID
,'Alex' as Fname
,'Rooney' as Lname
,'Male' as gender
, null as Confirm_date
,'R4' as Source1
,'B8'as Source2
union all
select
2 as ID
,'Roberto' as Fname
,'Carlos' as Lname
,'Male' as gender
, null as Confirm_date
,'R20' as Source1
,'B21'as Source2
union all
select
3 as ID
,'Pablo' as Fname
,'Aimar' as Lname
,'Male' as gender
, null as Confirm_date
,'R33' as Source1
,'B85'as Source2
union all
select
4 as ID
,'Crespo' as Fname
,'Ken' as Lname
,'Male' as gender
, null as Confirm_date
,'R75' as Source1
,'B65'as Source2
union all
select
5 as ID
,'Julie' as Fname
,'Clark' as Lname
,'Female' as gender
, null as Confirm_date
,'R96' as Source1
,'B98'as Source2
union all
select
6 as ID
,'Mal' as Fname
,'Maloy' as Lname
,'Female' as gender
, null as Confirm_date
,'R101' as Source1
,'B102'as Source2
union all
select 7 as ID
,'Mali' as Fname
,'Maloyyy' as Lname
,'Female' as gender
, null as Confirm_date
,'Rb654' as Source1
,'Bk456'as Source2
--update confirmdates
update Tableb
set Confirm_date = GETDATE()
--Check Tables-------------
select * from TableA
select * from TableB
--insert same data with different date---
insert into TableB
select
1 as ID
,'Alex' as Fname
,'Rooney' as Lname
,'Male' as gender
, null as Confirm_date
,'R1' as Source1
,'B2'as Source2
---update date
update Tableb
set Confirm_date = GETDATE()
where Source_Cust_ID = 1 and Confirm_date is null
---check tables
select * from TableA
select * from TableB
---create third table with most recent dates------
;with cte as (
select
ROW_NUMBER() over (partition by Source_Cust_ID order by [Confirm_date] desc)rn
, [Source_Cust_ID]
,[Fname]
,[Lname]
,[gender]
,[Confirm_date]
,[Source1]
,[Source2]
FROM [dbo].[TableB])
select *
into tableC
from cte
where rn = 1
---check if recent date has be selected---------
select * from tableC
--update table b -----------------------------------------
update [TableB]
set Confirm_date = GETDATE()
where Source_Cust_ID in
(
SELECT a.[ID]
from dbo.TableA a
left join [dbo].[TableC] b on
a.ID = b.Source_Cust_ID and
a.Fname = b.Fname and
a.Lname= b.Lname
and a.gender = b.gender ) and
Confirm_date in (
SELECT a.Confirm_date
from dbo.TableC a
left join tableB b on a.Fname = b.Fname and a.Lname = b.Lname and a.Confirm_date = b.Confirm_date
and a.Source1=b.Source1 and a.Source2 = b.Source2
where b.Source_Cust_ID is not null
)
and
Source1 in (
SELECT a.Source1
from dbo.TableC a
left join tableB b on a.Fname = b.Fname and a.Lname = b.Lname and a.Confirm_date = b.Confirm_date
and a.Source1=b.Source1 and a.Source2 = b.Source2
where b.Source_Cust_ID is not null
)
and
Source2 in (
SELECT a.Source2
from dbo.TableC a
left join tableB b on a.Fname = b.Fname and a.Lname = b.Lname and a.Confirm_date = b.Confirm_date
and a.Source1=b.Source1 and a.Source2 = b.Source2
where b.Source_Cust_ID is not null
)
---------------------------------------------------------------------
select * from tableB
/*
Drop table tableA
Drop table tableB
Drop table tableC
*/ |
|
|
|
|
|
|
|
|
Looking for T-sql Query to update date field