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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy