Re: Using Update...Set to update a table

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@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:0FAC5BD0-F97B-441F-884E-8CF7E449215E@xxxxxxxxxxxxxxxx
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)


.

Relevant Pages

  • Re: Automating repetitive tasks-creating text strings from excel into
    ... You should be able to convert the list of numbers into a single row of numbers quite easily using find/replace. ... You would need to remove the final comma at the end of the last number, or just not select it when you copy to the clipboard for pasting into the database search box. ... keystrokes, or can someone help me "loop" the keystrokes in a macro so I can ...
    (microsoft.public.word.docmanagement)
  • Re: New record on a new day (re-post)
    ... In a well-normalized relational database design, ... CommentTypeID (a foreign key, pointing back to the Primary Key in a ... a single row in a table. ... be too difficult to maintain this data in an excel spreadsheet, ...
    (microsoft.public.access.formscoding)
  • [Info-ingres] Ingres/Replicator Stress Test
    ... stress levels, they weren't high enough. ... So on my source database I have a single row table called next_id. ... deadlocking problems. ...
    (comp.databases.ingres)
  • Database vs Data Structure?
    ... I'm trying to decided whether to store the objects in the database ... data-structure in a single row in the database (then unpickle the data ... -able to query objects based on name and id. ...
    (comp.lang.python)
  • Database vs Data Structure?
    ... I'm trying to decided whether to store the objects in the database ... data-structure in a single row in the database (then unpickle the data ... -able to query objects based on name and id. ...
    (comp.lang.python)