September 28, 2010 at 11:27 pm
Hi All
I have a table like below
CREATE TABLE TABLE_1 S_NO INT IDENTITY,PRODUCT_NAME VARCHAR10,PRODUCT_ID INT,COUNT_1 INT DEFAULT 0
INSERT INTO TABLE_1 (PRODUCT_NAME,PRODUCT_ID)
SELECT 'SOAP',10001 UNION ALL
SELECT 'SOAP',10001 UNION ALL
SELECT 'SOAP',10001 UNION ALL
SELECT 'RUM',10002 UNION ALL
SELECT 'RUM',10002 UNION ALL
SELECT 'RUM',10002 UNION ALL
SELECT 'RUM',10002 UNION ALL
SELECT 'WHISKY',10003
Here i want to update the column count group by product name , and product id, but the count shoul be the below
format
1SOAP100011
2SOAP100012
3SOAP100013
4RUM100021
5RUM100022
6RUM100023
7RUM100024
8WHISKY100031
thanks in advance ..
September 28, 2010 at 11:41 pm
Try this:
CREATE TABLE TABLE_1 ( S_NO INT IDENTITY,PRODUCT_NAME VARCHAR(10),PRODUCT_ID INT,COUNT_1 INT DEFAULT 0 )
INSERT INTO TABLE_1 (PRODUCT_NAME,PRODUCT_ID)
SELECT 'SOAP',10001 UNION ALL
SELECT 'SOAP',10001 UNION ALL
SELECT 'SOAP',10001 UNION ALL
SELECT 'RUM',10002 UNION ALL
SELECT 'RUM',10002 UNION ALL
SELECT 'RUM',10002 UNION ALL
SELECT 'RUM',10002 UNION ALL
SELECT 'WHISKY',10003
SELECT PRODUCT_NAME,PRODUCT_ID
, [COUNT] = ROW_NUMBER() OVER (PARTITION BY PRODUCT_NAME,PRODUCT_ID ORDER BY PRODUCT_ID)
FROM TABLE_1
DROP TABLE TABLE_1
September 28, 2010 at 11:52 pm
Great output, but i want to update that column , please help me, i am trying this way but not success
UPDATE TABLE_1 SET COUNT_1 =
(
SELECT TOP 1 COUNT(1) FROM TABLE_1 A
WHERE A.PRODUCT_NAME = TABLE_1.PRODUCT_NAME AND A.PRODUCT_ID = TABLE_1.PRODUCT_ID
GROUP BY A.PRODUCT_NAME,A.PRODUCT_ID
)
September 29, 2010 at 12:01 am
Try this:
; WITH cte0 AS
(
SELECT S_NO , PRODUCT_NAME,PRODUCT_ID
, [COUNT] = ROW_NUMBER() OVER (PARTITION BY PRODUCT_NAME,PRODUCT_ID ORDER BY S_NO)
FROM TABLE_1
)
UPDATE TAB
SET TAB.COUNT_1 = CTE.[COUNT]
FROM TABLE_1 TAB
JOIN cte0 CTE
ON TAB.PRODUCT_NAME = CTE.PRODUCT_NAME AND
TAB.PRODUCT_ID = CTE.PRODUCT_ID AND
TAB.S_NO = CTE.S_NO
September 29, 2010 at 12:03 am
Give this a try:
WITH CTE AS (SELECT
S_NO,
ROW_NUMBER() OVER (PARTITION BY PRODUCT_NAME, PRODUCT_ID ORDER BY S_NO) AS RN
FROM TABLE_1)
UPDATE T1
SET
COUNT_1 = CTE.RN
FROM TABLE_1 T1
INNER JOIN CTE
ON T1.S_NO = CTE.S_NO;
You didn't specify how they should get numbered, so I assumed they should be numbered in S_NO order.
September 29, 2010 at 12:33 am
Many thanks I have finished that..
September 29, 2010 at 9:54 pm
Thanks for letting us know the queries we provided helped.
September 30, 2010 at 12:21 pm
CELKO (9/30/2010)
Have you even thought about using a relational design instead mimicking a deck of punch cards? Do you know about UPC and industry standards? Do you know why IDENTITY can never be a proper key for a table by definition? But it is great for a magnetic tape or deck of punch cards which have a physical insertion ordering.Here is a correct design, without all the violatiosn and redundancy your "deck of punch cards" had.
CREATE TABLE Products
(upc CHAR(13) NOT NULL PRIMARY KEY,
product_name VARCHAR(10) NOT NULL,
onhand_qty INTEGER DEFAULT 0 NOT NULL
CHECK (onhand_qty >= 0));
Please stop programming until you have read at least one book on RDBMS. You are hurting your company.
Seriously? Did you just quit smoking or not have your coffee yet or something? Wow.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 30, 2010 at 12:43 pm
Craig Farrell (9/30/2010)
CELKO (9/30/2010)
Have you even thought about using a relational design instead mimicking a deck of punch cards? Do you know about UPC and industry standards? Do you know why IDENTITY can never be a proper key for a table by definition? But it is great for a magnetic tape or deck of punch cards which have a physical insertion ordering.Here is a correct design, without all the violatiosn and redundancy your "deck of punch cards" had.
CREATE TABLE Products
(upc CHAR(13) NOT NULL PRIMARY KEY,
product_name VARCHAR(10) NOT NULL,
onhand_qty INTEGER DEFAULT 0 NOT NULL
CHECK (onhand_qty >= 0));
Please stop programming until you have read at least one book on RDBMS. You are hurting your company.
Seriously? Did you just quit smoking or not have your coffee yet or something? Wow.
Just a typical Celko response.
September 30, 2010 at 12:49 pm
Lynn Pettis (9/30/2010)
Just a typical Celko response.
I'd disagree. He's usually got the tact of a steamroller but that was obnoxious, even for him.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 30, 2010 at 12:57 pm
CELKO (9/30/2010)
Think I scared him enough he might stop writing danger code? I hope so
Are you sure he designed that table? Maybe he inherited it and isn't allowed to change the structure. (I know I have some databases/tables I have to support that aren't ideal.)
September 30, 2010 at 1:09 pm
CELKO (9/30/2010)
Think I scared him enough he might stop writing danger code? I hope so
Nope. I do think you've managed to make sure he won't buy your books on the subject, however. Anything from inherited design to vendor datadump staging cleanup can require strange coding, and that table above sure as hell isn't the final design, it's a sample set to figure out a problem for some more convoluted solution, especially if some datadump doesn't come with a unique row identification method and you've got to build your own concatonated key.
You want the perfect world, built on dream clouds. The real world's dirty, messy, and sometimes is a straight port over into SQL from those 1970 DB2 tragedies and your entire management is still working with an 'It ain't broke, stop asking to fix it' mentality.
The code isn't 'dangerous'. sp_MSforEachDBsp_executeSQL master.dbo.proc_dropallDBObjects is dangerous. Unparameterized and uncleansed dynamic SQL from a website is dangerous. What's above is just unoptimal.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 30, 2010 at 1:18 pm
Well said, Craig.
September 30, 2010 at 1:42 pm
Craig Farrell (9/30/2010)
Lynn Pettis (9/30/2010)
Just a typical Celko response.I'd disagree. He's usually got the tact of a steamroller but that was obnoxious, even for him.
I don't know, it was arrogant, pompous, and tactless. All typical of Celko response.
September 30, 2010 at 1:51 pm
CELKO (9/30/2010)
Craig Farrell (9/30/2010)
CELKO (9/30/2010)
Have you even thought about using a relational design instead mimicking a deck of punch cards? Do you know about UPC and industry standards? Do you know why IDENTITY can never be a proper key for a table by definition? But it is great for a magnetic tape or deck of punch cards which have a physical insertion ordering.Here is a correct design, without all the violatiosn and redundancy your "deck of punch cards" had.
CREATE TABLE Products
(upc CHAR(13) NOT NULL PRIMARY KEY,
product_name VARCHAR(10) NOT NULL,
onhand_qty INTEGER DEFAULT 0 NOT NULL
CHECK (onhand_qty >= 0));
Please stop programming until you have read at least one book on RDBMS. You are hurting your company.
Seriously? Did you just quit smoking or not have your coffee yet or something? Wow.
Think I scared him enough he might stop writing danger code? I hope so
Hey Joe,
I read atleast a book a month and have a list of authors and subjects to read up on. I've been doing this since 1997. At one point you were on my future book list. Because of your pompous attitudes toward people looking for help you are now on my banned authors list. Actually you're the only one on it.
---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply