Update statement Subquery using a group by

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

MaritimerAsked:
Who is Participating?
 
Nathan RileyFounderCommented:

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
 
Nathan RileyFounderCommented:
What's the error?
0
 
MaritimerAuthor Commented:
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
segurahCommented:
Who is your primary key?
0
 
MaritimerAuthor Commented:
There is no primary key.
0
 
MaritimerAuthor Commented:
In this case a primary key would have no affect as to the correct way of writing this statement??
0
 
Nathan RileyFounderCommented:
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
 
MaritimerAuthor Commented:
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
 
Nathan RileyFounderCommented:
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
 
Nathan RileyFounderCommented:
Nevermind I see now why your doing the subselect, are there any unique fields?
0
 
MaritimerAuthor Commented:
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
 
Nathan RileyFounderCommented:
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
 
MaritimerAuthor Commented:
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
 
segurahCommented:
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
 
MaritimerAuthor Commented:
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
 
MaritimerAuthor Commented:
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
 
segurahCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.