October 7, 2009 at 10:49 pm
This is the problem: i have a table called ORDERS with 4000 rows then every row has a column name called CLIENTNAME and other IDCLIENT , then i have another table called CLIENTS with a column name called CLIENTNAME and other column called ID, Well i need to update the ORDERS rows with the ID from CLIENTS table,
example:
if first row CLIENTNAME from ORDERS = any row CLIENTNAME from CLIENTS then get ID from CLIENTS row and update the IDCLIENT in ORDERS
and do it for every row in ORDERS
October 8, 2009 at 12:02 am
i see this example please confirm =( im here waiting for an answer but please with real SQL code, i don't know how to put TABLE.COLUM in sql server y get errors
UPDATE A
SET A.LocationCode = B.LocationCode
FROM tableA A INNER JOIN tableB B ON A.id = B.id
October 8, 2009 at 8:20 am
Hi,
This is an example of denormalised data and it's not good database design for an OLTP database.
In your case, the Orders table should not contain a clientname field at all, it should contain only a link to the ID field in the Client table which is created as a Foreign Key in order to maintain referential integrity in the database.
If the data was designed in this way, you wouldn't have this problem - you can always use views for ease of querying data if required.
In answer to your question however, without any sample data I will assume that ClientName is unique in the client table. If so, the following should help you out:
Update O
Set O.IDCLIENT=C.IDCLIENT
FROM Orders O
INNER JOIN Client C on C.CLIENTNAME=O.CLIENTNAME
I would stronly recommend looking at the below links and getting an understanding of normalisation and referential integrity or you're in for a world of pain!
October 8, 2009 at 8:20 am
I think this is what you are looking for:
UPDATE A
SET A.Idclient = B.ID
FROM Orders A INNER JOIN Clients B ON A.CleintName = B.ClientName
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply