I have a table (we'll call it table A) with two fields in it; one is an ID,
and the other (named "State") currently only contains null values. I'd like
to update the "State" field with values from another table in the database
(table B). The two databases share the same ID field, so I can join them
using these two values.

Below is what I've tried to run so far, but it's not working. My problem is
that I'm getting multiple records from my subquery, so I can't use "=". I
think I'm heading down the right track, but I can't figure this out. Any help
you guys can provide will be well-appreciated.

Thanks, Dan



set transaction isolation level read uncommitted

Update A
Set State = (select B.State from B with (nolock) join A with (nolock) on
B.ID=A.ID)

Dan,


Since you apparently have a single row in table A that matches more than 1
row from table B. This means that you have to choose to get only a single
value. (It is possible that all values for State in table B that have the
same ID are identical, but the SQL Server will not assume that.) So, let's
say that MAX is good enough.

This will not fail, but the answer will be wrong, because it would set every
row in A to the same maximum State that existed in the join between A and B,
which is not what you wanted, of course.

Update A
Set State = (select MAX(B.State) from B with (nolock) join A with (nolock)
on
B.ID=A.ID)

That is because the join is all in the subselect and is not correlated to
the row in A that you wish to update. If you remove the join from the
subselect and use the WHERE clause to refer to the table A in the Update,
you would get something like this:

Update A
Set State = (select MAX(B.State) from B with (nolock) WHERE B.ID=A.ID)

Actually, if the rows only match IDs 1 to 1, then you do not even need the
MAX any more, but assuming that it is not that simple, I prefer using a
derived table, as:

UPDATE A
SET A.State = C.State
FROM A JOIN
(SELECT ID, MAX(State) AS State
FROM B
GROUP BY ID) AS C
ON A.ID = C.ID

All the best,

RLF

"Dan" <Dan (AT) discussions (DOT) microsoft.com> wrote