By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,939 Members | 611 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,939 IT Pros & Developers. It's quick & easy.

UPDATE Query

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
"Shaun" <re**********@mania.plus.com> wrote in
news:bj**********@reader-00.news.insnet.cw.net:
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


If you have kept your Representative table, and if there are field(s) that
you can use to relate Representative to User (such as a Name field), then
you can use:

UPDATE (Booking INNER JOIN Representative ON Booking.Rep_ID =
Representative.Rep_ID) INNER JOIN [User] ON Representative.Name = User.Name
SET Booking.Rep_ID = [User_ID];

hth
Richard
Nov 12 '05 #2

P: n/a
I don't know if this is possible unless you have the original tables. If you
do then I have a very uneligant solution for you, but it does mean that
there's no problems if people have say, the same name, or what ever.

First find the highest id in User, lets says it, 26, then change the type of
id in both the Representative table and the User table from autonumber to
number.

Then run this query on representative, replacing 26 with what ever number
you found earlier:
UPDATE Representative SET Representative.id = Representative.id+26;
and this one on the Bookings table, replacing 26 again:
UPDATE Bookings SET Bookings.rep_ID = Bookings.rep_ID+26;

Then copy the rows of the Represtative table and past them on the end of the
User table. Set the User tables id field back to autonumber and delete the
Representative table and your away.

Jamie

"Shaun" <re**********@mania.plus.com> wrote in message
news:bj**********@reader-00.news.insnet.cw.net...
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

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.