Update on list of fields - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Update on list of fields

Author

Topic

Stephanie Baroux
Starting Member

3 Posts

Posted - 2004-07-06 : 05:21:40

Hello,

I try to "translate" this oracle query to transact SQL

update ct_fy04Q3 set
(purchase, order_total, nb_order,channel,store,coupon, first_order) =
(select 0,sum(order_total),
count( distinct web_order_number),
max( channel),
min(store),
0,min(web_order_number)
from order_fy04q3 b
where
ct_fy04Q3.email = b.email
group by email);

I receive the following error : Msg 170, Level 15 ...
Incorrect syntax near '('

can somebody help me to modify this query?

Thanks

stephanie

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-07-06 : 06:31:45

[code]
update ct_fy04Q3 set
purchase = 0,
order_total = sum(order_total),
nb_order = count( distinct web_order_number),
channel = max( channel),
store = min(store),
coupon = 0,
first_order = min(web_order_number)
from order_fy04q3 b
where
ct_fy04Q3.email = b.email
group by email
[/code]

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2004-07-06 : 06:34:07

lol kselvia, you beat me to it

------------->>> BREAKING NEWS!!! <<<-------------

Saddam Hussien has weapons of mass destrcution

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-07-06 : 06:35:35

Yours was better formatted :-)

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2004-07-06 : 07:01:24

But neither of them will work, you will get...

An aggregate may not appear in the set list of an UPDATE statement.

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2004-07-06 : 07:06:55

quote:
Originally posted by RickD

But neither of them will work, you will get...

An aggregate may not appear in the set list of an UPDATE statement.

Oh dear

I can't be bothered to change this to a derived table so I'll just remove my answer instead.

------------->>> BREAKING NEWS!!! <<<-------------

Saddam Hussien has weapons of mass destrcution

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2004-07-06 : 07:46:31

quote:
Originally posted by Amethystium

quote:
Originally posted by RickD

But neither of them will work, you will get...

An aggregate may not appear in the set list of an UPDATE statement.

Oh dear I can't be bothered to change this to a derived table so I'll just remove my answer instead.

------------->>> BREAKING NEWS!!! <<<-------------
Saddam Hussien has weapons of mass destrcution

Oh dear... lol...

Something like this:-


select
	sum(order_total) OrdTot,
	count(distinct web_order_number) WOrdNo,
	max(channel) channel,
	min(store) store,
	min(web_order_number) MinWOrdNo
into #tmpA
from ct_fy04Q3 a
inner join order_fy04q3 b
on a.email = b.email

update ct_fy04Q3 set
	purchase = 0, 
	order_total = a.OrdTot, 
	nb_order = a.WOrdNo,
	channel = a.channel,
	store = a.store,
	coupon = 0, 
	first_order = a.MinWOrdNo
from #tmpA a

drop table #tmpA

Amethystium
Aged Yak Warrior

701 Posts

Posted - 2004-07-06 : 08:27:22

Garçon astucieux!

------------->>> BREAKING NEWS!!! <<<-------------

Saddam Hussien has weapons of mass destrcution

tlongwell
Starting Member

1 Post

Posted - 2004-07-14 : 09:33:08

How about this solution?

update ct_fy04Q3
set purchase = 0,
order_total = b.order_total,
nb_order = b.nb_order,
channel = b.channel,
store = b.store,
coupon = 0,
first_order = b.first_order
From (
Select sum(order_total) as order_total,
count(distinct web_order_number) as nb_order,
max(channel) as channel,
min(store) as store,
min(web_order_number) as first_order
from order_fy04q3
group by email
) as b
Where b.email = ct_fy04Q3.email

T-Dizzle

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources