UPDATE Query

[Shaun]

Hi,

I have (amogst others) three tables in my database named Bookings, User, and
Representative. A User and a Representative are different types of user,
however I now want to merge these tables into one - User. When a Booking is
made, the User_ID and the Rep_ID are stored in the Booking table. I have now
merged the two tables (User and Representative) , how can I update the Rep_ID
column in Bookings so that it refers to the User_ID for each Representative
in the User table?

Thanks for your help

[David Portas]

Re: UPDATE Query

Without DDL and sample data it's just a wild guess, but maybe you are
looking for something like this:

UPDATE booking
SET rep_id =
(SELECT U.user_id
FROM User AS U
JOIN Representative AS R
ON U.firstname = R.firstname
AND U.lastname = R.lastname
AND U.user_type = 'REPRESENTATIVE '
AND R.rep_id = booking.rep_id)

(untested, and assuming that the names are unique)

If you need more help, post CREATE TABLE statements for your tables
(simplified if possible but including keys and constraints) and include some
sample data as INSERT statements.

--
David Portas
------------
Please reply only to the newsgroup
--