UPDATE from SELECT using SQL Server

In SQL Server 2008 (or better), use MERGE

MERGE INTO Table
   USING (
          SELECT id, col1, col2 
            FROM other_table 
           WHERE sql = 'cool'
         ) AS source
      ON Table.id = source.id
WHEN MATCHED THEN
   UPDATE 
      SET col1 = source.col1, 
          col2 = source.col2;

I'd modify Robin's excellent answer to the following:

UPDATE
     Table 
SET
     Table.col1 = other_table.col1,
     Table.col2 = other_table.col2 
FROM
     Table 
INNER JOIN     
     other_table 
ON     
     Table.id = other_table.id 
WHERE
     Table.col1 != other_table.col1 or 
     Table.col2 != other_table.col2 or
     (other_table.col1 is not null and table.col1 is null) or
     (other_table.col2 is not null and table.col2 is null)

Without a WHERE clause, you'll affect even rows that don't need to be affected, which could (possibly) cause index recalculation or fire triggers that really shouldn't have been fired.

One way

UPDATE t SET  t.col1 = o.col1,
t.col2 = o.col2
FROM other_table o 
join t on t.id = o.id
WHERE o.sql = 'cool'

Another possibility not mentioned yet is to just chuck the SELECT statement itself into a CTE then Update the CTE.

;WITH CTE
     AS (SELECT T1.Col1,
                T2.Col1 AS _Col1,
                T1.Col2,
                T2.Col2 AS _Col2
         FROM   T1
                JOIN T2
                  ON T1.id = T2.id
         /*Where clause added to exclude rows that are the same in both tables
           Handles NULL values correctly*/
         WHERE EXISTS(SELECT T1.Col1,
                             T1.Col2
                       EXCEPT
                       SELECT T2.Col1,
                              T2.Col2))
UPDATE CTE
SET    Col1 = _Col1,
       Col2 = _Col2  

This has the benefit that it is easy to run the SELECT statement on its own first to sanity check the results but it does requires you to alias the columns as above if they are named the same in source and target tables.

This also has the same limitation as the proprietary UPDATE ... FROM syntax shown in four of the other answers. If the source table is on the many side of a one to many join then it is undeterministic which of the possible matching joined records will be used in the Update (An issue that MERGE avoids by raising an error if there is an attempt to update the same row more than once).

UPDATE Table SET Col1=i.Col1 ,
Col2=i.Col2 FROM (SELECT Col1,Col2 FROM  other_table)i
where i.ID=Table.ID

Using alias:

UPDATE t
   SET t.col1 = o.col1
  FROM table1 AS t
         INNER JOIN 
       table2 AS o 
         ON t.id = o.id

This may be a niche reason to perform an update (for example, mainly used in a procedure), or may be obvious to others, but it should also be stated that you can perform an update-select statement without using join (in case the tables you're updating between have no common field).

update
    Table
set
    Table.example = a.value
from
    TableExample a
where
    Table.field = *key value* -- finds the row in Table 
    AND a.field = *key value* -- finds the row in TableExample a

For the record (and others searching like I was), you can do it in MySQL like this:

UPDATE first_table, second_table
SET first_table.color = second_table.color
WHERE first_table.id = second_table.foreign_id

The sample way to do it is:

UPDATE
    table_to_update,
    table_info
SET
    table_to_update.col1 = table_info.col1,
    table_to_update.col2 = table_info.col2

WHERE
    table_to_update.ID = table_info.ID

I add this only so you can see a quick way to write it so that you can check what will be updated before doing the update.

UPDATE Table 
SET  Table.col1 = other_table.col1,
     Table.col2 = other_table.col2 
--select Table.col1, other_table.col,Table.col2,other_table.col2, *   
FROM     Table 
INNER JOIN     other_table 
    ON     Table.id = other_table.id 

here is another useful syntax:

UPDATE suppliers
SET supplier_name = (SELECT customers.name
                     FROM customers
                     WHERE customers.customer_id = suppliers.supplier_id)
WHERE EXISTS (SELECT customers.name
              FROM customers
              WHERE customers.customer_id = suppliers.supplier_id);

it checks if it is null or not by using "WHERE EXIST"