need help with update statement

[aspiringprog]

Hi all,

I have this statement. I am trying to update a column in a table using values for a column in another table.

UPDATE a
SET DIAG = (SELECT b.ID
FROM b ,a
WHERE b.ID = a.ID
AND b.TYPE = 'c')
WHERE a.ID =
(SELECT a.ID
FROM b,a
WHERE b.ID = a.ID
AND b.TYPE = 'c')

I keep getting an error saying that the subquery returns multiple values and the command gets aborted.

I am new to SQL server. In oracle we can use cursors(although they are slow). How to work this problem in sql server?(using distinct clows the whole this down a lot).

Help is greatly appreciated.

thanks

 

[mrdenny]

Um, isn't this what you are looking for?

update a
set diag = b.id
from b
where a.id = b.id
	and b.type = 'c'

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[aspiringprog]

Hi,

Thanks Denny. That is what I did finally.

i am wondering if you can help me out with something else.

it is an update statement again, and I want to update a date column in table a with values from a date column in table b. The values from table b need to fall within a date range and I want the latest dates within that range from table b.

i have a statement like this, but it is taking forever. wondering if there is a better way of doing this.

UPDATE a
SET EFF_DATE = CONVERT(CHAR(10),b.EFF_DT,101)
FROM b
WHERE b.TYPE = 'c'
AND a.CK = b.CK
AND a.TERM_DATE >=
(SELECT MAX b.EFF_DT)
FROM b,a WHERE b.EFF_DT <= a.TERM_DATE
AND b.TERM_DT >= a.EFF_DATE)
AND a.EFF_DATE <=
(SELECT MAX(b.TERM_DT)
FROM b,a WHERE b.EFF_DT<=a.TERM_DATE AND b.TERM_DT >= a.EFF_DATE)

 

[aspiringprog]

UPDATE a
SET EFF_DATE = CONVERT(CHAR(10),b.EFF_DT,101)
FROM b
WHERE b.TYPE = 'c'
AND a.CK = b.CK
AND a.TERM_DATE >=
(SELECT MAX (b.EFF_DT)
FROM b,a WHERE b.EFF_DT <= a.TERM_DATE AND b.TERM_DT >= a.EFF_DATE)
AND a.EFF_DATE <=
(SELECT MAX(b.TERM_DT)
FROM b,a WHERE b.EFF_DT<=a.TERM_DATE AND b.TERM_DT >= a.EFF_DATE)

HOPE THIS FORMAT IS SLIGHTLY BETTER

 

[ESquared]

you need to ask a new question. Call it

"Update to max date from another table"

[COLOR=#aa88aa black]Cum catapultae proscriptae erunt tum soli proscript catapultas habebunt.[/color]

 

[SQLSister]

First stop using subqueries if performance is an issue. Learn to use derived tables instead. Next stop using nonAnsi standard joins. They are a very bad practice and newer versions of SQL server do no support them.

Questions about posting. See faq183-874