SQL Table Updates

0 pts.
Tags:
SQL
This is probably a simple question, but I seem to be butting my head against a brick wall. Here's the scenario: T have two databases, call them A and B. A is created when a user registers on a forum site here at the Uni, and B is their "profile". There is a common ID in both files. Because of a bug in the way Invision works (the forum software), there is one field in A that is out of sync with B. A.member_type is a numeric based on their "membership type" (student, teacher, etc) - single digit. B.member_group is the membership type in text, based on what they put in A - for example, if A.member_type = 3, then B.member_group would be "Student". But both tables are editable by the user, so they don't match up (that is being fixed over Christmas !). What I need to do is retrieve all records in both tables, joined by their ID, and change B.member_group based on the contents of A.member_type. But every time I try it, I get various and sundry errors, and one of our developers has told me that it cannot be done programmatically. As it is not I who will be running this script, but someone with zero SQL knowledge, is there an easy way of doing it ?
ASKED: December 19, 2005  5:47 PM
UPDATED: December 19, 2005  9:09 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

You should be able to do a single SQL update statement. Something like:

upate b set member_group = (select case member_type when 1 then ‘Teacher’ when 2 then ‘Adminstrator’ when 3 then ‘Student’ else ‘Other’ end from a when a.id = b.id)

Of course you will have to include every membership type in the case statement.

hth

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following