timoteoga
asked on
How to create an update query with a group by clause
I have the following query that provides the result set that I want to update...
select customer.customer_id, MAX(order_date) as last_order, credit_status
from oe_hdr inner join customer on customer.company_id = oe_hdr.company_id
AND customer.customer_id = oe_hdr.customer_id
WHERE oe_hdr.company_id = '01'
AND (oe_hdr.delete_flag = 'N') AND (oe_hdr.projected_order = 'N') AND (oe_hdr.cancel_flag = 'N')
and credit_status not in ('REJECT', 'BAD DEBT')
and customer.delete_flag = 'N'
and customer.company_id = '01'
group by customer.customer_id, credit_status
HAVING MAX(order_date) < DATEADD(yy,-3,getdate())
However, I cannot get the correct syntax for the update query in order to be able to update the credit_status field.
I was trying to do something simple like...
update customer
set credit_status = 'REJECT'
from oe_hdr inner join customer on customer.company_id = oe_hdr.company_id
AND customer.customer_id = oe_hdr.customer_id
WHERE oe_hdr.company_id = '01'
AND (oe_hdr.delete_flag = 'N') AND (oe_hdr.projected_order = 'N') AND (oe_hdr.cancel_flag = 'N')
and credit_status not in ('REJECT', 'BAD DEBT')
and customer.delete_flag = 'N'
and customer.company_id = '01'
group by customer.customer_id, credit_status
HAVING MAX(order_date) < DATEADD(yy,-3,getdate())
But it doesn't like that I have a 'group by' in there. Can anybody help me get the correct syntax?
Thanks
select customer.customer_id, MAX(order_date) as last_order, credit_status
from oe_hdr inner join customer on customer.company_id = oe_hdr.company_id
AND customer.customer_id = oe_hdr.customer_id
WHERE oe_hdr.company_id = '01'
AND (oe_hdr.delete_flag = 'N') AND (oe_hdr.projected_order = 'N') AND (oe_hdr.cancel_flag = 'N')
and credit_status not in ('REJECT', 'BAD DEBT')
and customer.delete_flag = 'N'
and customer.company_id = '01'
group by customer.customer_id, credit_status
HAVING MAX(order_date) < DATEADD(yy,-3,getdate())
However, I cannot get the correct syntax for the update query in order to be able to update the credit_status field.
I was trying to do something simple like...
update customer
set credit_status = 'REJECT'
from oe_hdr inner join customer on customer.company_id = oe_hdr.company_id
AND customer.customer_id = oe_hdr.customer_id
WHERE oe_hdr.company_id = '01'
AND (oe_hdr.delete_flag = 'N') AND (oe_hdr.projected_order = 'N') AND (oe_hdr.cancel_flag = 'N')
and credit_status not in ('REJECT', 'BAD DEBT')
and customer.delete_flag = 'N'
and customer.company_id = '01'
group by customer.customer_id, credit_status
HAVING MAX(order_date) < DATEADD(yy,-3,getdate())
But it doesn't like that I have a 'group by' in there. Can anybody help me get the correct syntax?
Thanks
ASKER
If you go without the group by, it then says that it's incorrect syntax near 'HAVING'
Hi
A sample is here.
A sample is here.
Drop table test
Go
Create table test
(ID int,
[SName] nvarchar(50),
[Marks] int)
Go
Drop table testMarks
Go
Create table testMarks
(ID int,
[Subject] nvarchar(50),
[Marks] int)
Go
Insert into test values (1,'John',0)
Insert into test values (2,'Robert',0)
Insert into test values (3,'William',0)
GO
Insert into testMarks values (1,'Math',40)
Insert into testMarks values (1,'English',90)
Insert into testMarks values (1,'Computer',80)
GO
Insert into testMarks values (2,'Math',80)
Insert into testMarks values (2,'English',80)
Insert into testMarks values (2,'Computer',90)
GO
Update test set [Marks]=(Select SUM([Marks]) from [testMarks]
Where test.[ID]=[testMarks].[ID] Group By [ID] )
GO
Select * from test
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Perfect - thanks!
Yes, you can do it that way, but I would look at what you intend and rewrite. It appears you are simply using the GROUP BY for the HAVING clause to filter out where a given customer doesn't have any orders meeting a specific criteria after three years ago.
You can eliminate the JOIN completely since you are NOT actually using the results of the derived table in your UPDATE. And do not need to GROUP BY since MAX(order_date) < {some date} is the same as NOT EXISTS {order_date >= {some date}}.
You can eliminate the JOIN completely since you are NOT actually using the results of the derived table in your UPDATE. And do not need to GROUP BY since MAX(order_date) < {some date} is the same as NOT EXISTS {order_date >= {some date}}.
update customer
set credit_status = 'REJECT'
where not exits (
select 1
from oe_hdr
where customer.company_id = oe_hdr.company_id
and customer.customer_id = oe_hdr.customer_id
and (oe_hdr.delete_flag = 'N') AND (oe_hdr.projected_order = 'N') AND (oe_hdr.cancel_flag = 'N')
and oe_hdr.order_date >= DATEADD(yy,-3,getdate())
)
and customer.credit_status not in ('REJECT', 'BAD DEBT')
and customer.delete_flag = 'N'
and customer.company_id = '01'
;
BTW, I was just posting since I had been typing this up to show you an example of what I meant before seeing you had accepted. I figured I would post it anyway for your and future readers' benefit. I am in no way discounting what you already have. If you truly need to do a GROUP BY with an update, using a derived table and UPDATE with JOIN is a very viable option.
Anyway, best regards and happy coding,
Kevin
Anyway, best regards and happy coding,
Kevin
but from what i see you dont need it for the update query?
if you were to try running it without the group by - does it update the record(s) of interest?
if not, you could just do a rollback and it will "undo" the update.