November 21, 2008 at 12:19 pm
I'm trying to fill in a column in table A from a column in table B where the ID's match in table A and B.
Im trying:
UPDATE A
SET A.MeetingID = B.MeetingID
FROM TableA A, TableB B
WHERE B.BookingID = A.BookingID
Also tried:
UPDATE A
SET A.MeetingID = B.MeetingID
FROM TableA AS A INNER JOIN
TableB AS B ON A.BookingID = B.BookingID
I get a syntax error that reads:
Column or expression 'MeetingID' cannot be updated.
Sample Tables:
Current Table A
MeetingID BookingID ExpenseID Discription Amount
NULL 1 10020
NULL 1 10021
NULL 1 10022
NULL 1 10023
NULL 2 10017
NULL 2 10018
NULL 2 10019
NULL 3 10088
NULL 3 10089
Table B
MeetingID BookingID
1423 1
5543 2
9789 3
Goal Table A
MeetingID BookingID ExpenseID Discription Amount
1423 1 10020
1423 1 10021
1423 1 10022
1423 1 10023
5543 2 10017
5543 2 10018
5543 2 10019
9789 3 10088
9789 3 10089
Each MeetingID has a unique BookingID match in Table B, but appear multiple times (once per ExpenseID).
I need to grab the MeetingID from Table B and fill in Table A's empty MeetingID table.
I'm new to SQL so im not sure how to go about doing this, but it seems like it should be really simple. I've been searching and reading for days but cant find this sort of example. Thanks for any advice you and provide!
-David
November 21, 2008 at 12:26 pm
Not sure, but give this a try:
with UniqueBookings (
MeetingID,
BookingID
) as (
select distinct
MeetingID,
BookingID
from
TableB
)
UPDATE TableA SET
MeetingID = B.MeetingID
FROM
TableA AS A
INNER JOIN UniqueBookings AS B
ON A.BookingID = B.BookingID
November 21, 2008 at 12:37 pm
Thanks for that... I'll try that out, but I just noticed I got my sample tables a little different. The MeetingIDs are in the the First table. So each row of the first table has a meetingID and a bookingID, 1 of each, no duplicates. The second table with the expenceID column and the bookingID column, there are multiple of the same BookingID (one for each expense) but the MeetingID is NULL... I need to fill in the MeetingID of the second example table. I'm not sure if that effects your sample. But I'll try it.. .but with the table sources reversed. 🙂
November 21, 2008 at 12:45 pm
Could you report with corrected example? I'm having a Missouri moment and you need to Show Me.
I think it should be easier if it is reversed.
November 21, 2008 at 1:22 pm
Ok, here you go. 🙂 I've edited my original post with the correct table examples. Sorry about that mix up. The source table has only one MeetingID and BookingID pair, no duplicates. I've included an example Goal Table A... so you can see what Table A should look like after the update. Obviously the real tables have many more fields, and thousands of rows. Thank you so much for looking into this for me!
November 21, 2008 at 2:07 pm
Without testing, this should work.
UPDATE TableA SET
MeetingID = B.MeetingID
FROM
TableA AS A
INNER JOIN UniqueBookings AS B
ON A.BookingID = B.BookingID
November 21, 2008 at 5:09 pm
I replied moments after your last post... but the post didn't appear I've just noticed.
Anyway, your example works! Except you forgot to rename UniqueBookings to TableB in your example, but I understood what you meant. But yeah, it worked like a charm! Thank you so much! I just don't understand what I did wrong in my first post, my second attempt example is almost identical, except i used a table alias (UPDATE A... SET A.MeetingID... etc). Is that where I went wrong?
Thanks again Lynn!!
Working Code:
UPDATE TableA SET
MeetingID = B.MeetingID
FROM
TableA AS A
INNER JOIN TableB AS B
ON A.BookingID = B.BookingID
November 21, 2008 at 5:43 pm
First, my first example was using a CTE called UneBookings to reduce what was Table B (original post, not modified) to one record per booking.
This wasn't needed when what was need was based on the reverse.
Second, I try not to use aliases in the UPDATE tablename SET portion of the UPDATE statement. I have run into issues doing so.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply