Updating Column where only one instance occurs

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

Updating Column where only one instance occurs

Author

Topic

rugby_fan
Starting Member

21 Posts

Posted - 2008-08-05 : 07:10:48

Hey guys,

I have this query that will update a column in a table with the same information from a column in another table but i have a problem.

UPDATE    dbo.tblEx1
SET              OrderStatus =(SELECT OrderStatus FROM tblEx2 WHERE tblEx1.OrderID = tblEx2.OrderID AND ????)

As you can see from the above query I update tblEx1.OrderStatus with the OrderStatus from tblEx2 but this query won't work where there is more than one instance of OrderID.

In tblEx2 you can have lots of rows with the same OrderID so the query returns an error saying the sub query returns more than one result.

So my question is how do I modifiy my query so that it only runs when it can only find one instance of the orderID in the tblEx2?

What do I need to put where the question marks are?
Thanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-08-05 : 07:19:25

Don't know what you really want in this case.
Maybe you have to look at these multiple rows to see and specify your WHERE-Clause.
Maybe you use
OrderStatus =(SELECT max(OrderStatus) FROM tblEx2 WHERE tblEx1.OrderID = tblEx2.OrderID)
or
OrderStatus =(SELECT top 1 OrderStatus FROM tblEx2 WHERE tblEx1.OrderID = tblEx2.OrderID)

Webfred

rugby_fan
Starting Member

21 Posts

Posted - 2008-08-05 : 07:31:57

Thanks for the reply

no, neither of those worked.

any other ideas?

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-08-05 : 08:34:30

I think you would find it a useful exercise to fully define your problem. You have already stated that there are multiple orderIDs in Ex2.
So you need to define what you want to do in this case. It's no use asking us - we really don't know what your requirements are! Once you know what you want it's relatively easy to either write the SQL (or prove that it can't be done with the data model).
So - what exactly do you want OrderStatus to be?

rugby_fan
Starting Member

21 Posts

Posted - 2008-08-05 : 09:08:07

LoztInSpace, thanks for the reply.

OrderStatus in tblEx1 is to be the same as OrderStatus is tblEx2.

I'm updating tblEx1 to have the same values in the OrderStatus Column as tblEx2.

My problem is that in tblEx1 there is only one OrderID that is unique to each row but that is NOT the case in tblEx2. I can have multiply rows in tblEx2 with the same OrderID.

So when I run the query it gives me an error because the subquery gives back more than 1 result.

So i need to change my query to find only OrderID's that occur once in tblEx2. Then I can update the ones that have multiply OrderID's manaually

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2008-08-05 : 13:47:23

quote:
Originally posted by rugby_fan
So i need to change my query to find only OrderID's that occur once in tblEx2. Then I can update the ones that have multiply OrderID's manaually

So add a HAVING clause to the end of your SELECT, like HAVING Count(OrderID) = 1.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-08-05 : 16:39:26

Do you have any other criteria? Like getting the most recent status or something? Maybe this will help:

UPDATE   
	dbo.tblEx1
SET 
	OrderStatus =(SELECT TOP 1 OrderStatus FROM tblEx2 WHERE tblEx1.OrderID = tblEx2.OrderID)

-- OR

UPDATE   
	T1 
SET 
	OrderStatus = T2.OrderStatus 
FROM
	dbo.tblEx1 AS T1
INNER JOIN
	dbo.tblEx2 
	ON T1.OrderID = T2.OrderID

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-08-05 : 22:57:15

UPDATE
dbo.tblEx1
SET
OrderStatus =(SELECT OrderStatus FROM tblEx2 WHERE tblEx1.OrderID = tblEx2.OrderID)
WHERE
(SELECT count(*) FROM tblEx2 WHERE tblEx1.OrderID = tblEx2.OrderID)=1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources