Error On Update - 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

General SQL Server Forums

New to SQL Server Programming

Error On Update

Author

Topic

a.ashabi
Posting Yak Master

117 Posts

Posted - 2009-03-23 : 18:09:44

Hi.When I used my query to update a field like this:

UPDATE items
SET items.ITEMDocJ =(SELECT docjonson.ITEMDocJ
FROM dbo.docjonson INNER JOIN dbo.items ON dbo.docjonson.ITEMNew=dbo.items.ItemNew
WHERE dbo.items.ItemNew=dbo.docjonson.ITEMNew)

I got this error :

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.

So I removed all the duplications from both tables:

-- DELETE DUPLICATES FROM docjonson

SELECT docjonson.ITEMNew , COUNT(*) AS Expr1
into DocKeys
FROM (dbo.docjonson INNER JOIN
dbo.items ON dbo.docjonson.ITEMNew = dbo.items.ItemNew)
GROUP BY docjonson.ITEMNew
HAVING count(*) > 1

SELECT DISTINCT docjonson.*
INTO docDup
FROM docjonson, DocKeys
WHERE docjonson.ITEMNew = DocKeys.ITEMNew

DELETE docjonson
FROM docjonson, DocKeys
WHERE docjonson.ITEMNew = DocKeys.ITEMNew

-- DELETE DUPLICATES FROM items

SELECT DISTINCT items.*
INTO itemsDocDup
FROM items, DocKeys
WHERE items.ITEMNew = DocKeys.ITEMNew

DELETE items
FROM items, DocKeys
WHERE items.ITEMNew = DocKeys.ITEMNew

Now I cant find any duplicates on the tables but when I try to update still I've got the same error.
What should I do?what is wrong with my query?
Plz help me.
thanks in advanced.

revdnrdy
Posting Yak Master

220 Posts

Posted - 2009-03-23 : 20:20:08

Hello

quote:

Originally posted by a.ashabi


UPDATE items
SET items.ITEMDocJ =(SELECT docjonson.ITEMDocJ
FROM         dbo.docjonson INNER JOIN dbo.items ON   dbo.docjonson.ITEMNew=dbo.items.ItemNew
WHERE dbo.items.ItemNew=dbo.docjonson.ITEMNew)

Is the part in red necessary? Seems redundant to me..

r&r

a.ashabi
Posting Yak Master

117 Posts

Posted - 2009-03-24 : 13:27:53

It worked for some other tables when I've added the red satatement.
But this time I have the error with or without the red statement.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-24 : 14:23:12

This is what you need probably...

UPDATE items
SET items.ITEMDocJ = docjonson.ITEMDocJ
FROM dbo.docjonson INNER JOIN dbo.items ON dbo.docjonson.ITEMNew=dbo.items.ItemNew

a.ashabi
Posting Yak Master

117 Posts

Posted - 2009-03-24 : 14:52:00

yes.that was the right query.
thanks u so much :)

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-24 : 15:09:41

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources