Combine two tables by column

should be easy: I ha开发者_如何学Cve two tables:

table 1

  ID  NAME   NEWID   
  1   DOG   
  2   CAT   
  3   HORSE


table 2

NAME   ID
DOG    100
CAT    200
Horse  300

I need the result to be as follows:

 ID  NAME   NEWID   
  1   DOG   100
  2   CAT   200
  3   HORSE 300

My thoughts are:

Update table1
set NewId = (select ID from table2 where Id =NewID)

it says: Invalid column name NewId

---

Because this is a fairly basic and oft-seen question, I assume you are new to the SQL Server product with affect from its 2008 R2 version.

I encourage you not to seek to write new code using the proprietary UPDATE..FROM syntax, arguably only retained in the product to remain compatible with legacy code.

Also note the UPDATE..FROM syntax can cause unexpected and unpredictable behaviour. e.g. if your table2 contained a second row for HORSE with a different ID value e.g.

CREATE TABLE table1 (ID INTEGER, NAME VARCHAR(10), NEWID INTEGER);
CREATE TABLE table2 (NAME VARCHAR(10), ID INTEGER);

INSERT INTO table1 VALUES (1, 'DOG', NULL), 
                          (2, 'CAT', NULL),
                          (3, 'HORSE', NULL);
INSERT INTO table2 VALUES ('DOG', 100), 
                          ('CAT', 200),
                          ('HORSE', 300),
                          ('HORSE', 400);

There are two possible values for HORSE, 300 and 400. Which one will be picked is undocumented behaviour and can be unpredictable.

I instead encourage you to use the MERGE syntax, being an extension to Standard SQL e.g.

MERGE INTO table1
   USING table2 
      ON table1.NAME = table2.NAME
WHEN MATCHED THEN 
   UPDATE
      SET NEWID = table2.ID;

Note that with the addition of the row as described above, the MERGE syntax will raise an error ("The MERGE statement attempted to UPDATE or DELETE the same row more than once..."), which is more desirable than silently using an arbitrary and potentially incorrect value. Consider that if the designers of SQL Server today really think the UPDATE..FROM behaviour is desirable then why didn't they make MERGE work the same way?

---

Kind of confusing, as it seems you would need to match on name, and not Id? Assuming that is the case:

UPDATE 
   t1 
SET
  t1.NEWID = t2.ID
FROM
  table1 t1
  INNER JOIN table2 AS t2 ON t1.NAME = t2.NAME;

---

The only way to map these to tables is by name, so you need to tweak you UPDATE a little bit:

UPDATE table1
   SET NEWID = (
                SELECT ID 
                  FROM table2 
                 WHERE table2.NAME = table1.NAME
               );