Could anyone help me with an update query? I need to populate Table 1
(bookings) with data from Table 3 (defaults), via a joining field in Table 2
(enquiries). All fields are of type INT, using SQL Server 2000.
Table 1 (bookings):
id, t_val, q_val
Table 2 (enquiries):
id, booking_id, enq_type
Table 3 (defaults):
id, def_enq_type, def_t_val, def_q_val
Table 3 data:
1,9,2,1
2,10,2,2
3,11,3,2
4,12,1,2
The fields to be updated are 'bookings.t_val' and 'bookings.q_val', from
'defaults.def_t_val' and 'defaults.def_q_val' respectively. 'bookings.id'
relates to 'enquiries.booking_id', and 'enquiries.enq_type' to
'defaults.def_enq_type'. Just to make it more complex, 'booking_id' in
enquiries can have duplicates, in which case I want the one with the highest
'enquiries.id' (ie the most recent record).
Any help very gratefully received, I've been staring at it for hours.Tony,
Do you have any DDL for the Tables? And also some sample data for the
Tables?
Any how do you perceive Table 1(Bookings) to look like?
Thanks
Barry|||Try This:
declare @.bookings table(id int, t_val int, q_val int)
declare @.enquiries table(id int, booking_id int, enq_type int)
declare @.defaults table(id int, def_enq_type int, def_t_val int,
def_q_val int)
insert @.defaults values(1,9,2,1)
insert @.defaults values(2,10,2,2)
insert @.defaults values(3,11,3,2)
insert @.defaults values(4,12,1,2)
insert @.enquiries values(1, 1, 9)
insert @.enquiries values(2, 2, 11)
insert @.enquiries values(3, 1, 10)
insert @.bookings
select e.booking_id, d.def_t_val, d.def_q_val
from @.enquiries e
inner join @.defaults d
on e.enq_type = d.def_enq_type
inner join (select booking_id, max(id) maxid from @.enquiries group by
booking_id) x
on e.booking_id = x.booking_id and e.id = x.maxid
select * from @.bookings|||Thanks for the replies so far, but I'm still struggling.
I think I should have made it clearer that the table 'bookings' is already
populated with data, and that it is just 2 new columns (t_val, q_val) that
need populating (so obviously I've omitted all other fields not needed for
this update query).
So if 'bookings' currently looks like:
id, t_val, d_val
1, null, null
2, null, null
3, null, null
4, null, null
and 'enquiries' looks like:
id, booking_id, enq_type
1,2,9
2,4,10
3,1,12
4,1,11
5,3,9
(note more than one record for booking_id 1)
and as mentioned before, 'defaults' looks like:
id, def_enq_type, def_t_val, def_q_val
1,9,2,1
2,10,2,2
3,11,3,2
4,12,1,2
then the end result for 'bookings' should be:
id, t_val, d_val
1, 3, 2
2, 2, 1
3, 2, 1
4, 2, 2
Again, any help very gratefully received.
"JeffB" wrote:
> Try This:
> declare @.bookings table(id int, t_val int, q_val int)
> declare @.enquiries table(id int, booking_id int, enq_type int)
> declare @.defaults table(id int, def_enq_type int, def_t_val int,
> def_q_val int)
> insert @.defaults values(1,9,2,1)
> insert @.defaults values(2,10,2,2)
> insert @.defaults values(3,11,3,2)
> insert @.defaults values(4,12,1,2)
> insert @.enquiries values(1, 1, 9)
> insert @.enquiries values(2, 2, 11)
> insert @.enquiries values(3, 1, 10)
> insert @.bookings
> select e.booking_id, d.def_t_val, d.def_q_val
> from @.enquiries e
> inner join @.defaults d
> on e.enq_type = d.def_enq_type
> inner join (select booking_id, max(id) maxid from @.enquiries group by
> booking_id) x
> on e.booking_id = x.booking_id and e.id = x.maxid
> select * from @.bookings
>|||Try this:
declare @.bookings table(id int, t_val int, q_val int)
declare @.defaults table(id int, def_enq_type int, def_t_val int,
def_q_val int)
declare @.enquiries table(id int, booking_id int, enq_type int)
insert @.bookings values(1, null, null)
insert @.bookings values(2, null, null)
insert @.bookings values(3, null, null)
insert @.bookings values(4, null, null)
insert @.defaults values(1,9,2,1)
insert @.defaults values(2,10,2,2)
insert @.defaults values(3,11,3,2)
insert @.defaults values(4,12,1,2)
insert @.enquiries values(1,2,9)
insert @.enquiries values(2,4,10)
insert @.enquiries values(3,1,12)
insert @.enquiries values(4,1,11)
insert @.enquiries values(5,3,9)
update b
set b.t_val = d.def_t_val,
b.q_val = d.def_q_val
from @.bookings b
inner join @.enquiries e
on b.id = e.booking_id
inner join @.defaults d
on e.enq_type = d.def_enq_type
inner join (select booking_id, max(id) maxid from @.enquiries group by
booking_id) x
on e.booking_id = x.booking_id and e.id = x.maxid
where b.t_val is null and b.q_val is null
select * from @.bookings
