title: How to update a all rows from a table with data from a different table's rows?
body: <p>I'm trying to update all rows (only 3 columns I have to update) from a table that have an equal value on another table...</p>
  
  <p>Here are my tables...</p>
  
  <pre><code>CREATE TABLE [dbo].[prods](
      [IdProducto] [int] IDENTITY(1,1) NOT NULL,
      [IdGrupo] [int] NULL,
      [IdCategoria] [int] NULL,
      [IdAlmacen] [varchar](50) NULL,
      [Codigo] [varchar](50) NULL,
      [Nombre] [varchar](50) NULL,
      [Descripcion] [varchar](max) NULL,
      [Cantidad] [int] NULL,
      [Imagen] [varchar](max) NULL,
      [StockMin] [int] NULL,
      [StockMax] [int] NULL,
      [Ancho] [varchar](50) NULL,
      [Alto] [varchar](50) NULL,
      [Largo] [varchar](50) NULL,
      [Peso] [varchar](50) NULL,
      [Volumen] [varchar](50) NULL,
      [Color] [varchar](50) NULL,
      [Material] [varchar](50) NULL,
      [Presentacion] [varchar](50) NULL,
      [bitPrecioVentaUnico] [int] NULL,
      [PrecioCompra] [money] NULL,
      [DescuentoCompra] [float] NULL,
      [PrecioVenta] [money] NULL,
      [DescuentoVenta] [float] NULL,
      [Estado] [varchar](20) NULL
  )
  
  CREATE TABLE [dbo].[prodnuevos](
      [Codigo] [int] NULL,
      [itemid] [int] NULL,
      [Item] [varchar](255) NULL,
      [Categoria] [varchar](255) NULL,
      [Cantidad] [int] NULL,
      [Minima] [nvarchar](255) NULL,
      [Costo] [money] NULL,
      [Valor] [money] NULL,
      [peso] [float] NULL,
      [unidades] [float] NULL
  )
  </code></pre>
  
  <p>What I want to do is the following..</p>
  
  <pre><code>Update prods 
  Set prods.PrecioCompra = prodnuevos.Costo, 
  prods.PrecioVenta = prodnuevos.Costo, 
  prods.Cantidad = prodnuevos.Cantidad 
  WHERE prods.Nombre = prodnuevos.Item;
  </code></pre>
  
  <p>(Update PrecioCompra, PrecioVenta and Cantidad in prods using prodnuevos' Costo and Cantidad where Nombre and Item is the same)</p>
  
  <p>Obviosly the query above does not work, but I just wanted to explain my problem.
  I have tried various combinations of queries with success at all. (Despite having to update 3 columns, I don't mind if I have to use 3 different queries)</p>
  
  <p>I have tried things like</p>
  
  <pre><code>    UPDATE       prods c
      SET          c.PrecioCompra =
        (SELECT        a.Costo
        FROM            prodnuevos AS a INNER JOIN
           prods AS z ON z.Nombre = a.Item 
  WHERE        (a.Item = c.Nombre))
  </code></pre>
  
  <p>but no result...</p>
  
  <p>Am I doing something wrong?? or it's a totally different query??
  Thanks in advance.</p>