sql server (alternate): 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

[quoted text, click to view]

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;


--