Correlated 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

Correlated update

Author

Topic

lzdba
Starting Member

5 Posts

Posted - 2007-12-17 : 06:47:04

I am trying to perform an update based on the following query

select o.order_id,
from orders o, accounts a
where a.account_id=r.account_id and
a.event_type IN (2003, 3057, 4003, 7005)
order by o.order_id desc

I have tried several combinations but to no avail.

Can anybody provide a solution?

Thanks in advance

Declan

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-17 : 06:49:51

Where is the UPDATE part? What are you trying to update?
I can only see the SELECT part.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-17 : 06:50:37

UPDATE o
SET o.Col1 = p.ColGreen
FROM Orders AS o
INNER JOIN (SELECT ... ) AS p ON p.PkCol = o.PkCol

E 12°55'05.25"
N 56°04'39.16"

lzdba
Starting Member

5 Posts

Posted - 2007-12-17 : 07:05:24

Peso,

I excluded my update statement as i didn't want to complicate the post with stuff that wasn't working :o) Thanks for the update i'll give it a whirl now and let you know how i get on

lzdba
Starting Member

5 Posts

Posted - 2007-12-17 : 07:13:32

This is the kind of update i'm looking for....

update o
set o.order_id = (select a.order_id,
from orders o, accounts a
where a.account_id=o.account_id and
a.type IN (2003, 3057, 4003, 7005)
order by o.order_id desc)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-17 : 07:28:26

Try this:-
update o
set o.order_id = a.order_id
FROM orders o
inner join accounts a
on a.account_id=o.account_id and
a.type IN (2003, 3057, 4003, 7005)
order by o.order_id desc

b/w what's the use of order by here?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-17 : 07:37:02

[code]update o
set o.order_id = a.order_id
from orders as o
left join accounts as a ON a.account_id=o.account_id
and a.type IN (2003, 3057, 4003, 7005)[/code]

E 12°55'05.25"
N 56°04'39.16"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-12-17 : 07:38:38

http://weblogs.sqlteam.com/mladenp/archive/2007/08/19/60292.aspx

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com <- new version out

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-17 : 07:43:22

quote:

Originally posted by Peso

update		o
set		o.order_id = a.order_id
from		orders as o
left join	accounts as a ON a.account_id=o.account_id
			and a.type IN (2003, 3057, 4003, 7005)

E 12°55'05.25"
N 56°04'39.16"

@Peso:
Wont this put NULL values into order_id column when condition does not match?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-17 : 07:47:57

Yes, of course.
This way you get the same result as the correlated subquery as OP posted.

E 12°55'05.25"
N 56°04'39.16"

lzdba
Starting Member

5 Posts

Posted - 2007-12-17 : 11:04:09

Thanks guys, I think i've got it now.

Cheers for all the help

lzdba
Starting Member