Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

need help with update statement

Status
Not open for further replies.
Jun 1, 2006
58
US
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
 
Um, isn't this what you are looking for?

Code:
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)
[noevil]
 
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)
 
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
 
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]
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top