I am able to select the rows I'd like to update but when I use that same basic logic to do an update it gives me some grief.

I have three tables that I am working with.

I have the table I want to update some old data with new data(Table A)

I have a table that contains a mapping between the old data and the new data (Table B)

But I only want to update those rows in Table A that are a subset of Table C.

So here's what I got -- the question is what am I missing:

Update Table_A

SET Table_A.current_id=
(select Table_B.new_id
from Table_B
where Table_B.old_id = Table_A.current_id)

INNER JOIN TABLE_C on
Table_A.col1 = Table_C.col1
and Table_A.col2 = Table_C.col2
where Table_C.status = 'X' or Table_C.status = 'Y';

Any help would be appreciated.

Thanks

You have to tell us which database you are using. The syntax for multi-table UPDATE varies a *LOT* from database to database.

I'm using SQL Server 2005

Look at Example C near the bottom of this page:
http://msdn.microsoft.com/en-us/library/ms177523.aspx

Judging from that, I'd try this:
SQL Server's UPDATE syntax is the weirdest around, requiring you to repeat the table name of the updated table in the FROM clause. Truly bizarre compared to, say, Access or MySQL.

Let me be a little more clear.

I have a mapping table (TABLE_B) that contains the old_id and the new_id.

I want to update TABLE_A setting the ID to the new_id where TABLE_A.old_id = TABLE_B.old_id

But I only want to update those records in TABLE_A that match the criteria in TABLE_C, which is
where TABLE_A.col1 = TABLE_C.col1
and TABLE_A.col2 = TABLE_C.col2
and (TABLE_C.status in 'X','Y')

what gives me heartburn in the part after the INNER JOIN.

how can I keep my subselect and still do the INNER JOIN?

update Table_A
set Table_A.current_id =
(select Table_B.new_id
from Table_B
where Table_B.current_id = Table_A.current_id)
INNER JOIN Table_A
on TABLE_C
(Table_A.col1 = TABLE_C.col1
and TABLE_A.col2 = TABLE_C.col2
and TABLE_C.status in ('X','Y');

??? I *THOUGHT* that is what my answer would do.

Did you try it???

What part did it not do?

You could use INNER JOIN syntax, but the *implicit* inner joins that I show there should be 100% equivalent.

Oh, W.T.H. Using INNER JOIN:
Looks to me like the same thing.

Despite my on-going trust issues, you were right.

Your script worked like a charm, and I'm sorry I EVER doubted you.

It just seemed to simple - and maybe that's part of MY problem -- expecting things to be harder than they actually are.

thank you, Thank You, THANK YOU!

And again, my humblest apologies.

Scout14

LOL! Hard to blame you. SQL Server's UPDATE syntax really is the weirdest in the industry, I think.

:) That's a common problem in web development, especially after you've been beating your head against something for awhile. This is one of the reasons that it's so important to write simple, understandable application code. SQL doesn't give you much latitude, but when working in C#, PHP, Java, VB.NET, etc. good code is always simple and readable. I remember reading in a book somewhere that there are two types of "good code". Code in which there are no longer any obvious errors, and code in which there obviously are no errors. I think that's an excellent distinction between code "that works" and code that is truly "good".

__________________
-------------------------

Whatever you can do or dream you can, begin it. Boldness has genius, power and magic in it. Begin it now.
-Johann von Goethe

When Two Hearts Race... Both Win.
-Dove Chocolate Wrapper

Chroniclemaster1, Founder of www.EarthChronicle.com
A Growing History of our Planet, by our Planet, for our Planet.

And in any case, perhaps *THE* most important thing to do is write *MAINTAINABLE* code.

That is, write code that somebody else can come along an read 3 years from now and make needed changes. All without tearing their hair out.