Looking for T-sql Query to update date field

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

SQL Server 2012 Forums

Transact-SQL (2012)

Looking for T-sql Query to update date field

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

*/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources