Update a record from a value in a select statement

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 a record from a value in a select statement

Author

Topic

sqlhelpneeded
Starting Member

1 Post

Posted - 2003-04-09 : 10:38:20

I can't see what is wrong with this update statement. What I am trying to do is update a value in the KCRT_REQUEST_DETAILS table from all values returned in my select statement.

UPDATE KCRT_REQUEST_DETAILS c
SET c.parameter3 = (select a.parameter1, a.parameter1
from knta_references a, kcrt_requests b
where a.source_id='[REQ.REQUEST_ID]'
and a.parameter1=b.request_id),
c.visible_parameter3 = (select a.parameter1, a.parameter1
from knta_references a, kcrt_requests b
where a.source_id='[REQ.REQUEST_ID]'
and a.parameter1=b.request_id)
where a.source_id='[REQ.REQUEST_ID]'

nr
SQLTeam MVY

12543 Posts

Posted - 2003-04-09 : 11:01:24

You can't update a field or compare it to two fields.
You also can't use the subquery tables in the where clause for the update.

maybe change
a.parameter1, a.parameter1
to a.parameter1

Also the subqueries must return one value per row updated.

haven't looked carefully but couldn't this be

UPDATE KCRT_REQUEST_DETAILS c
SET c.parameter3 = a.parameter1 ,
c.visible_parameter3 = a.parameter1
from knta_references a, kcrt_requests b
where a.source_id='[REQ.REQUEST_ID]'
and a.parameter1=b.request_id

The subqueries don't join to the updated table so
select a.parameter1
from knta_references a, kcrt_requests b
where a.source_id='[REQ.REQUEST_ID]'
and a.parameter1=b.request_id

would have to return just one value.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

sqlforumhelp
Starting Member

1 Post

Posted - 2003-04-09 : 13:27:18

Thanks nr, but what I think I need to use is a cursor so that I can add all the results (records) of the select statement into one field of the KCRT_Request_Details table. Do you know a way I could do that?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources