Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Update statement Subquery using a group by

Posted on 2009-04-21
17
Medium Priority
?
609 Views
Last Modified: 2013-11-05
I want to perform a Update on my table based on the results from my query that uses a group by statement. Is there a better way to do this??? Currently I get a synatx error)
Update WM_QMDS_LINV_Det set cc_status = 'IGNORE' 
 
from 
 
 
(
SELECT     dbo.WM_QMDS_LINV_Det.warehouse_no, dbo.WM_QMDS_LINV_Det.inv_doc, dbo.WM_QMDS_LINV_Det.status, dbo.WM_QMDS_LINV_Det.batch_no, 
                      dbo.WM_QMDS_LINV_Det.storage_unit_no, dbo.WM_QMDS_LINV_Det.stock_cat, dbo.WM_QMDS_LINV_Det.spec_stock_ind, 
                      dbo.WM_QMDS_LINV_Det.spec_stock_no, dbo.WM_QMDS_LINV_Det.storage_no, dbo.WM_QMDS_LINV_Det.storage_loc
FROM         dbo.WM_QMDS_LINV_Det 
 
where (WM_QMDS_LINV_Det.created_by <> 'system - Rollup') 
 
GROUP BY dbo.WM_QMDS_LINV_Det.warehouse_no, dbo.WM_QMDS_LINV_Det.inv_doc, dbo.WM_QMDS_LINV_Det.status, 
                      dbo.WM_QMDS_LINV_Det.batch_no, dbo.WM_QMDS_LINV_Det.storage_unit_no, dbo.WM_QMDS_LINV_Det.stock_cat, 
                      dbo.WM_QMDS_LINV_Det.spec_stock_ind, dbo.WM_QMDS_LINV_Det.spec_stock_no, dbo.WM_QMDS_LINV_Det.storage_no, 
                      dbo.WM_QMDS_LINV_Det.storage_loc HAVING COUNT(*) >= 2
)

Open in new window

0
Comment
Question by:Maritimer
  • 8
  • 6
  • 3
17 Comments
 
LVL 12

Expert Comment

by:Nathan Riley
ID: 24196246
What's the error?
0
 

Author Comment

by:Maritimer
ID: 24196336
Server: Msg 170, Level 15, State 1, Line 18
Line 18: Incorrect syntax near ')'.

However even if i remove the bracket (s) it still does not work.

Then I would get

Server: Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'SELECT'.
0
 
LVL 6

Expert Comment

by:segurah
ID: 24196480
Who is your primary key?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:Maritimer
ID: 24196540
There is no primary key.
0
 

Author Comment

by:Maritimer
ID: 24196548
In this case a primary key would have no affect as to the correct way of writing this statement??
0
 
LVL 12

Expert Comment

by:Nathan Riley
ID: 24196641
Try this:
Update WM_QMDS_LINV_Det set cc_status = 'IGNORE' 
 
from  dbo.WM_QMDS_LINV_Det D
 inner join
 
(
SELECT     dbo.WM_QMDS_LINV_Det.warehouse_no, dbo.WM_QMDS_LINV_Det.inv_doc, dbo.WM_QMDS_LINV_Det.status, dbo.WM_QMDS_LINV_Det.batch_no, 
                      dbo.WM_QMDS_LINV_Det.storage_unit_no, dbo.WM_QMDS_LINV_Det.stock_cat, dbo.WM_QMDS_LINV_Det.spec_stock_ind, 
                      dbo.WM_QMDS_LINV_Det.spec_stock_no, dbo.WM_QMDS_LINV_Det.storage_no, dbo.WM_QMDS_LINV_Det.storage_loc
FROM         dbo.WM_QMDS_LINV_Det 
 
where (WM_QMDS_LINV_Det.created_by <> 'system - Rollup') 
 
GROUP BY dbo.WM_QMDS_LINV_Det.warehouse_no, dbo.WM_QMDS_LINV_Det.inv_doc, dbo.WM_QMDS_LINV_Det.status, 
                      dbo.WM_QMDS_LINV_Det.batch_no, dbo.WM_QMDS_LINV_Det.storage_unit_no, dbo.WM_QMDS_LINV_Det.stock_cat, 
                      dbo.WM_QMDS_LINV_Det.spec_stock_ind, dbo.WM_QMDS_LINV_Det.spec_stock_no, dbo.WM_QMDS_LINV_Det.storage_no, 
                      dbo.WM_QMDS_LINV_Det.storage_loc HAVING COUNT(*) >= 2
) E on D.warehouse_no = e.warehouse_no and d.batch_no = e.batch_no

Open in new window

0
 

Author Comment

by:Maritimer
ID: 24196679
Thanks Gallitin - that partially works it gives no errors. But there seems to be some sort of issue. it updates zero records and I know there are 24 to update.

I am reviewing the work still.
0
 
LVL 12

Expert Comment

by:Nathan Riley
ID: 24196688
Well on my join I'm not sure what fields you want to join on so I just put a couple in for examples.  Why are you doing the subselect?
0
 
LVL 12

Expert Comment

by:Nathan Riley
ID: 24196714
Nevermind I see now why your doing the subselect, are there any unique fields?
0
 

Author Comment

by:Maritimer
ID: 24196736
I have a Insert select statement that group all my records if there are 2 or more of the rows the same (Roll them up) these I have set as system-rollup. The now the records that I used to create the new rows I will disable by setting them to ignore. Hope that makes since.
0
 
LVL 12

Expert Comment

by:Nathan Riley
ID: 24196776
Or try this maybe easier if I'm going off the correct field
Update WM_QMDS_LINV_Det set cc_status = 'IGNORE' 
from WM_QMDS_LINV_Det
where dbo.WM_QMDS_LINV_Det.warehouse_no in 
(
SELECT     dbo.WM_QMDS_LINV_Det.warehouse_no, dbo.WM_QMDS_LINV_Det.inv_doc, dbo.WM_QMDS_LINV_Det.status, dbo.WM_QMDS_LINV_Det.batch_no, 
                      dbo.WM_QMDS_LINV_Det.storage_unit_no, dbo.WM_QMDS_LINV_Det.stock_cat, dbo.WM_QMDS_LINV_Det.spec_stock_ind, 
                      dbo.WM_QMDS_LINV_Det.spec_stock_no, dbo.WM_QMDS_LINV_Det.storage_no, dbo.WM_QMDS_LINV_Det.storage_loc
FROM         dbo.WM_QMDS_LINV_Det 
 
where (WM_QMDS_LINV_Det.created_by <> 'system - Rollup') 
 
GROUP BY dbo.WM_QMDS_LINV_Det.warehouse_no, dbo.WM_QMDS_LINV_Det.inv_doc, dbo.WM_QMDS_LINV_Det.status, 
                      dbo.WM_QMDS_LINV_Det.batch_no, dbo.WM_QMDS_LINV_Det.storage_unit_no, dbo.WM_QMDS_LINV_Det.stock_cat, 
                      dbo.WM_QMDS_LINV_Det.spec_stock_ind, dbo.WM_QMDS_LINV_Det.spec_stock_no, dbo.WM_QMDS_LINV_Det.storage_no, 
                      dbo.WM_QMDS_LINV_Det.storage_loc HAVING COUNT(*) >= 2
)

Open in new window

0
 

Author Comment

by:Maritimer
ID: 24196835
Server: Msg 116, Level 16, State 1, Line 1
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
0
 
LVL 12

Accepted Solution

by:
Nathan Riley earned 1000 total points
ID: 24196863

Update WM_QMDS_LINV_Det set cc_status = 'IGNORE' 
from WM_QMDS_LINV_Det
where dbo.WM_QMDS_LINV_Det.warehouse_no in 
(
SELECT     dbo.WM_QMDS_LINV_Det.warehouse_no
FROM         dbo.WM_QMDS_LINV_Det 
 
where (WM_QMDS_LINV_Det.created_by <> 'system - Rollup') 
 
GROUP BY dbo.WM_QMDS_LINV_Det.warehouse_no, dbo.WM_QMDS_LINV_Det.inv_doc, dbo.WM_QMDS_LINV_Det.status, 
                      dbo.WM_QMDS_LINV_Det.batch_no, dbo.WM_QMDS_LINV_Det.storage_unit_no, dbo.WM_QMDS_LINV_Det.stock_cat, 
                      dbo.WM_QMDS_LINV_Det.spec_stock_ind, dbo.WM_QMDS_LINV_Det.spec_stock_no, dbo.WM_QMDS_LINV_Det.storage_no, 
                      dbo.WM_QMDS_LINV_Det.storage_loc HAVING COUNT(*) >= 2
)

Open in new window

0
 
LVL 6

Expert Comment

by:segurah
ID: 24196976
Happens that if you don't have a primary key (or can identify wich are the fields that makes a primary key) you are probably updating a record that you don't wish.

Can you explain in 'bussiness language' what is supossed the query does?
0
 

Author Comment

by:Maritimer
ID: 24196994
I have a Insert select statement that group all my records if there are 2 or more of the rows the same (Roll them up) these I have set as system-rollup. The now the records that I used to create the new rows I will disable by setting them to ignore. Hope that makes since.
0
 

Author Comment

by:Maritimer
ID: 24197070
Thanks Galliti

This is starting to pull together now. I probably need to make a few more adjustments, but thank-you very munch for the help.
0
 
LVL 6

Expert Comment

by:segurah
ID: 24197246
Well, a 'businees view'  means as exmaple "I have a warehouse where the stock is, etc etc etc", ask because I think your query is not doing that you expect ...
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

579 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