Update Issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Update Issue

Author

Topic

jscot
Posting Yak Master

106 Posts

Posted - 2011-04-04 : 21:14:54

Create Table Table1
(
ID VARCHAR(20),
ORDER_ID VARCHAR(20)
)

CREATE TABLE TABLE2
(
ID VARCHAR(20),
LOC_ID VARCHAR(20)
)

INSERT INTO Table1
VALUES ('1','CRB-123')
GO
INSERT INTO Table1
VALUES ('2','CRB-345')
GO
INSERT INTO Table1
VALUES ('1','CRB-678')
GO

INSERT INTO TABLE2
VALUES ('1','57')
GO
INSERT INTO TABLE2
VALUES('2','57')
GO
INSERT INTO TABLE2
VALUES('1','57')
GO

--USING UPDATE STATMENT

UPDATE TABLE2
SET LOC_ID = Table1.ORDER_ID
FROM TABLE2
INNER JOIN TABLE1
ON TABLE2.ID = Table1.ID

I am getting this result

ID LOC_ID
1 CRB-123
2 CRB-345
1 CRB-123

I want to be result like this after i run update statement

ID LOC_ID
1 CRB-123
2 CRB-345
1 CRB-678

Please let me know how i can fix this problem.

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2011-04-05 : 01:32:34

hi,

i don't know if you have a huge database behind this problem, but i would suggest you to have database normalized in terms to perform "correct" update. your update statement is okey, but your data lack some general consistency.
So add another Unique ID on table1 and table2 to match all the IDs together to support your model and then perform update.

if this is the case only of three update statements, just hard-code the new values for LOC_ID in table2.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources