|
|
Posted by Hugo Kornelis on 10/01/03 11:43
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
Navigation:
[Reply to this message]
|