Update question - 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 2000 Forums

SQL Server Development (2000)

Update question

Author

Topic

stevo_3
Starting Member

20 Posts

Posted - 2006-01-10 : 08:04:34

Hello,

Tabel: workitem: id(key), officeid, contact_id
1, 55750 , 20
Tabel: contact: id(key), title,
2 , 55750
The values of workitem.officeid and contact.title are the same,
i want to update the workitem.contact_id field of workitem, lookup and put in the key here from contact (id). wich is 2 in this case

UPDATE workitem

SET contact_id= (select id FROM contact WHERE contact.title = workitem.officeid)
WHERE EXISTS
( select contact.id FROM contact WHERE contact.title = workitem.officeid);

when i do this he gives me the folowwing error:

Server: Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

(8 row(s) affected)

(4895 row(s) affected)

Anyone has an idea ?

Thx

Steve

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-10 : 08:10:45

Before Trying this make sure you have latest Backup

UPDATE W
SET W.contact_id= C.id FROM workitem W inner join contact C
ON W.officeid=C.title

Madhivanan

Failing to plan is Planning to fail