• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 265
  • Last Modified:

Update each row in the table based on results

Hello,

I have a DataTable of my customers orders with one empty column. My columns:

ID
ID_Name

ID_Name is the empty column. I am getting values for this column from another table, called products, which has ID's and names of each product:

ID_PROD
ID_Name_prod


ID_PROD is a foreign key for customers table (ID column).

How to update ID_Name column from customers table? I wanna get ID_Name_prod for each row in the customers table and update that row, which is initially empty.

And idea?

I am selecting ID_Name_prod with the query:

Select Producst.ID_Name_prod
FROM customers LEFT JOIN products
ON customers.ID = products.ID_Name_prod

This query returns column with all the products names - but how to update each row in my customers table?


Greetings, Frenky
0
AntonioRodrigo
Asked:
AntonioRodrigo
4 Solutions
 
sachinpatil10dCommented:
Try this

update Products set ID_Name = ID_Name_prod
FROM customers LEFT JOIN products
ON customers.ID = products.ID_Name_prod

Open in new window

0
 
sachinpatil10dCommented:
ID_PROD replaced with ID_Name_prod


update Products set ID_Name = ID_Name_prod
FROM customers LEFT JOIN products
ON customers.ID = products.ID_PROD

Open in new window

0
 
Paul MacDonaldDirector, Information SystemsCommented:
Strictly speaking, you shouldn't store information in more than one place.  Having Name in two places de-normalizes your database. Still, what you want is something like this:

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
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
update with join explained here:
http://www.experts-exchange.com/A_1517.html
0
 
Bhavesh ShahLead AnalysistCommented:

Hi,
your question is bit unclear for me.
you saying you will getting products name from ID_Name_prod,
but you put same in
customers.ID = products.ID_Name_prod

there are many products for one customer.....


- Bhavesh
0
 
Ephraim WangoyaCommented:

try
update customers
set ID_Name = B.ID_Name_prod
from products B
inner join customers on customers.ID = B.ID_PROD

Open in new window

0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now