DML Help 1

[TitleistDBA]

I have the following query that I need to turn into an update to set the oh.OAMODL field to 'USM'

select oh.oaorno, sum(OBORQA) as totqt
from MVXCJDTTST.oohead oh, MVXCJDTTST.ooline oo, MVXCJDTTST.mitmas m
where oh.OAMODL='RSG'
and oh.oaorst < '44'
and oo.obitno=mmitno
and oh.oaorno = oo.oborno
and m.mmitcl='2200'
group by oh.oaorno

The problem is I only want to update the records where the 'totqt' value is > 6.

Can anyone help with this.

Thanks

http://www.acushnetcompany.com

http://www.titleist.com

 

[vongrunt]

Suppose these rows are in table oohead:

oaorno  ooaorst  OBORQA  OAMODL
1             1	   3    RSG
1            20       4    RSG
1            45       2    RSG

Do you want to set OAMODL to 'USM' for only first two rows (where oaorst < 44) or all three (with the same oaorno=1 value)?

Also: Column 'mmitno', where it belongs? Table MVXCJDTTST.mitmas ?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[TitleistDBA]

OOHEAD is the Order Header table. OOLINE is the order line item table and MITMAS is the Item Master table. I have a request to change the field in the Header table to 'USM' where it is currnetly 'RSG' But only the order that have 6 or more line items and are a specific item type of 2200. The problem I have run into is that you can not put an aggregate function in a where clause. So how can I update only the header records that have more than 6 line items?

http://www.acushnetcompany.com

http://www.titleist.com

 

[vongrunt]

> OOHEAD is the Order Header table.

OK. This probably means that oaorno is unique in that table?

And I guess column 'mmitno' (see my 2nd question) belongs to MITMAS table?

If both these facts are true, you can try correlated subquery in WHERE clause:

-- select oh.*
update oh set OAMODL = 'USM'
from MVXCJDTTST.oohead oh
where oh.oaorst < '44'
and oh.OAMODL='RSG'
and
(	select sum(OBORQA)
	from MVXCJDTTST.ooline oo, MVXCJDTTST.mitmas m
	where oo.obitno=m.mmitno        
		and m.mmitcl='2200'
		and oo.oborno= oh.oaorno 
) > 6

There are other ways (faster queries), but this request is fire&forget so...

Btw. wanna check what will be updated, uncomment SELECT line and comment UPDATE. This is a good practice anyway since updates w/ joins can be sometimes tricky.

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[TitleistDBA]

That worked.
Thanks!

http://www.acushnetcompany.com

http://www.titleist.com