Solved

How to create an update query with a group by clause

Posted on 2011-09-06
7
266 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:timoteoga
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 9

Expert Comment

by:jerrypd
ID: 36488819
well you obviously cant update a group by...
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.
0
 

Author Comment

by:timoteoga
ID: 36488850
If you go without the group by, it then says that it's incorrect syntax near 'HAVING'
0
 
LVL 9

Expert Comment

by:mimran18
ID: 36488893
Hi
 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

Open in new window

0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 10

Accepted Solution

by:
dwe761 earned 500 total points
ID: 36488899
You may be able to use something like this but the join fields in the last 3 lines may have to be adjusted because I didn't know which table some of the fields came from:



update customer
set credit_status = 'REJECT'

from customer
INNER JOIN 
(
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())
) c
ON customer.customer_id = c.customer_id AND
	customer.order_date = c.last_order AND
	customer.credit_state = c.credit_status

Open in new window

0
 

Author Closing Comment

by:timoteoga
ID: 36488940
Perfect - thanks!
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36488964
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}}.
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'
;

Open in new window

0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36488991
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
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question