[SOLVED] Update in nested statements

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

[SOLVED] Update in nested statements

Author

Topic

eb1962
Starting Member

6 Posts

Posted - 2006-10-09 : 09:17:13

I am loosing my mind over a rather "simple" matter.

I have a table which I have created by summarizing some quantities from a detailed order table, in order to have summarized quantities by product, through the following SQL:
INSERT INTO SUMMARIZED_ORDER_TAB
(ORDER_ID, PRODUCT_ID, QUANTITY)
SELECT ORDER_ID, PRODUCT_ID, SUM(QUANTITY) AS QUANTITY
FROM DETAILED_ORDER_TAB
GROUP BY ORDER_ID, PRODUCT_ID
HAVING (ORDER_ID IN
(SELECT ORDER_ID
FROM DETAILED_ORDER_TAB AS Tmp
GROUP BY ORDER_ID, PRODUCT_ID
HAVING COUNT(*) > 0 AND PRODUCT_ID = DETAILED_ORDER_TAB.PRODUCT_ID))
ORDER BY ORDER_ID, PRODUCT_ID

Now I must number those lines.

The structure of the SUMMARIZED_ORDER_TAB is the following:
ORDER_ID varchar (20)
ORDER_LINE_ID smallint
PRODUCT_ID varchar (20)
QUANTITY decimal

The order line id is currently blank in ALL records and the following records are available:
+ ORDER_ID + ORDER_LINE_ID + PRODUCT_ID + QTY +
+----------+---------------+------------+-----+
| 1 | | ABC1 | 100 |
| 1 | | ABC2 | 130 |
| 1 | | ABC3 | 20 |
| 2 | | ABC1 | 200 |
| 2 | | ABC3 | 10 |

Result should be:
+ ORDER_ID + ORDER_LINE_ID + PRODUCT_ID + QTY +
+----------+---------------+------------+-----+
| 1 | 1 | ABC1 | 100 |
| 1 | 2 | ABC2 | 130 |
| 1 | 3 | ABC3 | 20 |
| 2 | 1 | ABC1 | 200 |
| 2 | 2 | ABC3 | 10 |

What would you suggest to create the ORDER_LINE_ID directly when creating the new table or to amend it at a later stage?

How can I do it?
I have tried in several ways but I am not getting anywhere...
Could you please help me?

Thanks,
Emanuele

nr
SQLTeam MVY

12543 Posts

Posted - 2006-10-09 : 09:25:44

Does your having clause do anything?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-09 : 09:34:49

I don't understand your INSERT statement. You subquery same table and check for COUNT(*) > 0?
Try this code, it performs the same task

INSERT		SUMMARIZED_ORDER_TAB
		(
			ORDER_ID,
			PRODUCT_ID,
			QUANTITY
		)
SELECT		ORDER_ID,
		PRODUCT_ID,
		SUM(QUANTITY) QUANTITY
FROM		DETAILED_ORDER_TAB
GROUP BY	ORDER_ID,
		PRODUCT_ID
HAVING 		COUNT(*) > 0
ORDER BY	ORDER_ID,
		PRODUCT_ID

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-09 : 09:37:31

[code]UPDATE b
SET b.OrderLineID = (SELECT COUNT(*) FROM SUMMARIZED_ORDER_TAB a WHERE a.ORDER_ID = b.ORDER_ID AND a.Product_ID <= b.ProductID)
FROM SUMMARIZED_ORDER_TAB b[/code]
Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-09 : 09:43:03

If you use Front end application, do numbering there

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources