TSQL Query

[mattcolley@gmail.com]

Hi All--
I have a column which contains an ID value. I also have a URL column
(within same table) which contains a NULL value, that I am trying to
update with a URL value + the value in the ID column. Here is the
update statement:
---------------------------------------------

DECLARE @URLToUpdate VARCHAR(30)
SET @URLToUpdate = (select PRI_ID from TABLE1 where URL is null)
UPDATE TABLE1 SET URL = 'http://www.fakeweb.org/maps/reports/
webtms.asp?PRI_ ID=' + @URLToUpdate
where url is null
------------------------------------------------

The error message returned is:

'Subquery returned more than 1 value.'

Appreciate any help to get pointed in the right direction,
Thank You,

[Plamen Ratchev]

Re: TSQL Query

The error is because the subquery to set @URLToUpdate returns multiple
values (as you have multiple NULL values in the URL column). Based on
your description, seems you need this query:

UPDATE Table1
SET url = 'http://www.fakeweb.org/maps/reports/webtms.asp?PRI_ ID=' + id
WHERE url IS NULL;

If the ID column is numeric data type, then you can cast it:

UPDATE Table1
SET url = 'http://www.fakeweb.org/maps/reports/webtms.asp?PRI_ ID=' +
CAST(id AS VARCHAR(10))
WHERE url IS NULL;


--
Plamen Ratchev

http://www.SQLStudio.com