Update Table

I have a field, Y, in table A that I need to update from table B,
matching on comman field X in both tables.

Table A has 10K+ records with field X in it (Field X multipul times).
Table B has only disctinct records in it based field X. Table B also has
a colume Y that I need to copy to table A's column Y.

So basically I want all of column Y's data in Table B copied to table
A's column Y.

One last point, I am using MS-Access to do the this.

I know this is a simple update command, but I can't seem to get it.

Can any one help me with this?

Thanks.
Mike
m charney at dunlap hospital dot net

Michael Charney

*** Sent via Developersdex http://www.droptable.com ***

On Tue, 28 Mar 2006 14:50:33 GMT, Nothing wrote:

>I have a field, Y, in table A that I need to update from table B,
>matching on comman field X in both tables.
>
>Table A has 10K+ records with field X in it (Field X multipul times).
>Table B has only disctinct records in it based field X. Table B also has
>a colume Y that I need to copy to table A's column Y.
>
>So basically I want all of column Y's data in Table B copied to table
>A's column Y.
>
>One last point, I am using MS-Access to do the this.
>
>I know this is a simple update command, but I can't seem to get it.
>
>Can any one help me with this?

Hi Mike,

You're using Access, but you've posted the question to a SQL Server
newsgroup. I know that there are some differences in SQL syntax between
SQL Server and Access but I don't know what they are - if my suggestions
don't work, you should consider reposting the question to an Access
group.

Anyway, my first recommendation would be not to do this at all. If the Y
value in table A always has to be equal to the Y value in table B,
storing it in table A is redundant. Drop the column from table A and set
up a view (I believe Access calls a view a "query") instead:

SELECT     A.X, B.Y
FROM       A
INNER JOIN B
ON   B.X = A.X;

But if you really want to keep the redundant information, check if this
works:

UPDATE A
SET    Y = (SELECT B.Y
FROM   B
WHERE  B.X = A.X);

(Or, not recommended because it's non-standard syntax, less portable,
somewhat error-prone, but admittedly faster in some situations)

UPDATE     A
SET        Y = B.Y
FROM       A
INNER JOIN B
ON   B.X = A.X;


--
Hugo Kornelis, SQL Server MVP