update table help

Question

hello i have two tables: table_1 and table_2

table_1                       table_2
field_1   field_2             field_3      field_4
1          a                  1             
2          b                  4
3          c                  3
4          d                  2

now i need a query using update so that in table_2 i can have follwing values in field_4:

table_2
field_3    Field_4
1              a
4              d
3              c
2              b

should be obtained by update staement in single query using table_1 such that if field_3 of table_2 has 1 then filed_4 should have value equlas to field_2, field_1 in table_1

Answer 1

In SQL Server, it would be:

UPDATE table_2
 set field_4 = t1.field_2
 from table_2 t2
  inner join table_1 t1
   on t1.Field1 = t2.Field_3

This assumes that field_1 is a primary keys (has unique values). Anything in table_2 not found in table_1 would not be updated. Duplicate field_3 values in table_2 would be properly set.

Answer 2

Yeah, since you aliased the tables, I think he's right. The correct answer would be:

    UPDATE t2
       SET field_4 = t1.field_2
      FROM    table_2 t2
       INNER JOIN
          table_1 t1
       ON t1.Field1 = t2.Field_3