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',GET DATE()+5,'11111 111')
INSERT INTO TEST1 VALUES('C1',GET DATE()+10,'2222 2222')
INSERT INTO TEST1 VALUES('C1',GET DATE()+15,'4444 4444')
INSERT INTO TEST1 VALUES('C1',GET DATE()+16,'3333 3333')

DELETE FROM TEST2
INSERT INTO TEST2 VALUES('C1',GET DATE(),'')
INSERT INTO TEST2 VALUES('C1',GET DATE()+2,'')
INSERT INTO TEST2 VALUES('C1',GET DATE()+11,'')
INSERT INTO TEST2 VALUES('C1',GET DATE()+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_D ATE > TEST2.ENTRY_DAT E
ORDER BY TEST1.BOOKING_D ATE)

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_D ate > Test2.Entry_Dat e
ORDER BY Test1.Booking_D ate),
NewPhone_no =
(SELECT TOP 1 Test1.NewPhone_ no
FROM Test1
WHERE Test1.Booking_D ate > Test2.Entry_Dat e
ORDER BY Test1.Booking_D ate)
(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_Dat e > T2.Entry_Date)
(untested)

Best, Hugo
--

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