update fields with searched first date record fields

[tilak.negi]

Hello !
I'm trying to update one table field with another table searched first
date record.
getting some problem.
If anyone have experience similar thing or have any idea about it,
please guide.

Sample case is given below.

Thanks in adv.
T.S.Negi
--Sample case

DROP TABLE TEST1
DROP TABLE TEST2

CREATE TABLE TEST1
(
CUST_CD VARCHAR(10),
BOOKING_DATE DATETIME,
BOOKPHONE_NO VARCHAR(10)
)

CREATE TABLE TEST2
(
CUST_CD VARCHAR(10),
ENTRY_DATE DATETIME,
FIRSTPHONE_NO VARCHAR(10)
)

DELETE FROM TEST1
INSERT INTO TEST1 VALUES('C1',GETDATE()+5,'11111111')
INSERT INTO TEST1 VALUES('C1',GETDATE()+10,'22222222')
INSERT INTO TEST1 VALUES('C1',GETDATE()+15,'44444444')
INSERT INTO TEST1 VALUES('C1',GETDATE()+16,'33333333')

DELETE FROM TEST2
INSERT INTO TEST2 VALUES('C1',GETDATE(),'')
INSERT INTO TEST2 VALUES('C1',GETDATE()+2,'')
INSERT INTO TEST2 VALUES('C1',GETDATE()+11,'')
INSERT INTO TEST2 VALUES('C1',GETDATE()+12,'')

--SELECT * FROM TEST1
--SELECT * FROM TEST2
/*
Sample data
TEST1
CUST_CD BOOKING_DATE BOOKPHONE_NO
C1 2005-04-08 21:46:47.780 11111111
C1 2005-04-13 21:46:47.780 22222222
C1 2005-04-18 21:46:47.780 44444444
C1 2005-04-19 21:46:47.780 33333333

TEST2
CUST_CD ENTRY_DATE FIRSTPHONE_NO
C1 2005-04-03 21:46:47.800
C1 2005-04-05 21:46:47.800
C1 2005-04-14 21:46:47.800
C1 2005-04-15 21:46:47.800

DESIRED RESULT
CUST_CD ENTRY_DATE FIRSTPHONE_NO
C1 2005-04-03 21:46:47.800 11111111
C1 2005-04-05 21:46:47.800 11111111
C1 2005-04-14 21:46:47.800 44444444
C1 2005-04-15 21:46:47.800 44444444
*/

[Hugo Kornelis]

On 3 Apr 2005 09:26:57 -0700, ti********@mind-infotech.com wrote:

Hello !
I'm trying to update one table field with another table searched first
date record.
getting some problem.
If anyone have experience similar thing or have any idea about it,
please guide.

Sample case is given below.

(snip)

Hi T.S.Negi,

Thanks for the create table and insert statements and expected output.
This query provides the desired results:

UPDATE TEST2
SET FIRSTPHONE_NO =
(SELECT TOP 1 TEST1.BOOKPHONE_NO
FROM TEST1
WHERE TEST1.BOOKING_DATE > TEST2.ENTRY_DATE
ORDER BY TEST1.BOOKING_DATE)

It does make use of proprietary T-SQL syntax (TOP 1), so it's not very
portable. Let me know if you prefer an ANSI-standard version.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[tilak.negi]

Thanks for for your reply.
Please suggest, if there are more then 1 fields to update.

for e.g. newphone_no field in both table. and I want to update
t2.newphone_no with t1.newphone_no

Thanks,
T.S.Negi

[Hugo Kornelis]

On 3 Apr 2005 22:17:10 -0700, ti********@mind-infotech.com wrote:

Thanks for for your reply.
Please suggest, if there are more then 1 fields to update.

for e.g. newphone_no field in both table. and I want to update
t2.newphone_no with t1.newphone_no

Thanks,
T.S.Negi

Hi T.S.Negi,

This is the easiest way (though probably not the fastest):

UPDATE Test2
SET FirstPhone_no =
(SELECT TOP 1 Test1.BookPhone_no
FROM Test1
WHERE Test1.Booking_Date > Test2.Entry_Date
ORDER BY Test1.Booking_Date),
NewPhone_no =
(SELECT TOP 1 Test1.NewPhone_no
FROM Test1
WHERE Test1.Booking_Date > Test2.Entry_Date
ORDER BY Test1.Booking_Date)
(untested)

And this version gets rid of the proprietary TOP 1 syntax but uses the
proprietary UPDATE FROM syntax instead - probably faster than the
previous version, but test it to know for sure:

UPDATE T2
SET FirstPhone_no = T1.BookPhone_no,
NewPhone_no = T1.NewPhone_no
FROM Test2 AS T2
INNER JOIN Test1 AS T1
ON T1.Booking_Date = (SELECT MIN(T1b.Booking_Date)
FROM Test1 AS T1b
WHERE T1b.Booking_Date > T2.Entry_Date)
(untested)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)