UPDATE MULTIPLE ROWS WITH VALUE FROM ANOTHER TABLE

Question

UPDATE MULTIPLE ROWS WITH VALUE FROM ANOTHER TABLE

phanatico-1156717

Ten Centuries

Points: 1015
More actions
October 7, 2009 at 10:49 pm

#385532

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
phanatico@live.com.mx

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

phanatico-1156717 Ten Centuries Points: 1015 More actions · Answer 1

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

HowardW One Orange Chip Points: 29109 More actions · Answer 2

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!

http://en.wikipedia.org/wiki/Database_normalization

http://en.wikipedia.org/wiki/Referential_integrity

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

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

UPDATE MULTIPLE ROWS WITH VALUE FROM ANOTHER TABLE

Cookies on SQLServerCentral