T-SQL Query Help - SQL Server Forums

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

SQL Server 2008 Forums

Transact-SQL (2008)

T-SQL Query Help

Author

Topic

jscot
Posting Yak Master

106 Posts

Posted - 2011-05-18 : 14:32:53

Here is the situation and I need help,

Source data like this
INVOICE
C-125
A-567
C-125
Here what I want
INVOICE
C-125
A-567
C-125-A

Note:- I can’t do manually because I have 25 thousand duplicate that I want to fix. Thanks for help.

X002548
Not Just a Number

15586 Posts

Posted - 2011-05-18 : 14:35:33

and so the "next" C-125 would be C-125-B??? Are there any other columns in the table?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-18 : 14:35:52

What is the rule you used to change the third row from C-125 to C-125-A? Is the "-A" a randomly picked value, or does it have anything to do with the previous row that has A-567? If it does have anything to do with A-567, how did you decide the ordering in the table?

By default, data in a table is an unordered set, so unless you explicitly specify the ordering scheme, the order is undefined.

jscot
Posting Yak Master

106 Posts

Posted - 2011-05-18 : 14:36:09

Yes.

jscot
Posting Yak Master

106 Posts

Posted - 2011-05-18 : 14:38:33

There is no certan rule, I have duplicate Invoice #s i.e( 123,123,123,123,123) and i want be a unique Invoice# i.e (123,123-A,123-B,123-C,123-D).

X002548
Not Just a Number

15586 Posts

Posted - 2011-05-18 : 14:49:09

please post the table DDL

Can we use numbers instead of letters?

and, ummm, you know this is just plain bad

Why not just alter the table, add an identity column and make invoice and the identity column a composite key

boom

done

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

jscot
Posting Yak Master

106 Posts

Posted - 2011-05-18 : 15:05:56

This is just a sample data bro, I can't alter table, there is historial record that we need it. I can use number insted ot Letters.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-18 : 15:29:18

What do you want to happen if there are more than 27 identical invoices? In any case, one way to do it:

CREATE TABLE #tmp (invoice VARCHAR(255));
insert into #tmp values ('C-125'),('A-567'),('C-125');

;WITH cte1 AS 
(
   SELECT invoice, ROW_NUMBER() OVER (PARTITION BY invoice ORDER BY (SELECT NULL)) - 1 AS rn
   FROM #tmp
)
SELECT
   invoice + CASE WHEN rn > 0 THEN '-'+CHAR(ASCII('A')+rn-1) ELSE '' END
FROM
   cte1

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-05-18 : 15:31:58

quote:
Originally posted by jscot

This is just a sample data bro, I can't alter table, there is historial record that we need it. I can use number insted ot Letters.

What Brett suggested isn't changing the 'historical record'... it's just adding a unrelated unique identifier to each row in a new column...

Corey

I Has Returned!!

X002548
Not Just a Number

15586 Posts

Posted - 2011-05-18 : 15:37:04

wow..very clever


CREATE TABLE #tmp (invoice VARCHAR(255));
insert into #tmp values ('C-125'),('A-567'),('C-125'),('C-125'),('C-125'),('C-125');

;WITH cte1 AS 
(
   SELECT invoice, ROW_NUMBER() OVER (PARTITION BY invoice ORDER BY (SELECT NULL)) - 1 AS rn
   FROM #tmp
)
SELECT
   invoice + CASE WHEN rn > 0 THEN '-'+CHAR(ASCII('A')+rn-1) ELSE '' END
FROM
   cte1
   
   DROP TABLE #tmp

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

jscot
Posting Yak Master

106 Posts

Posted - 2011-05-18 : 16:55:05

AWESOME! its works fine, but i want to update my table field "INVOICE" i am using this syntax to update
UPDATE #tmp
SET INVOICE = ?

I really appreciate your help.

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-05-18 : 17:22:23

May be you need to move this into subquery in order to use it for update.

CREATE TABLE #tmp (invoice VARCHAR(255));
insert into #tmp values ('C-125'),('A-567'),('C-125'),('C-125'),('C-125'),('C-125');

SELECT invoice + CASE WHEN rn > 0 THEN '-'+CHAR(ASCII('A')+rn-1) ELSE '' END
FROM (
SELECT invoice, ROW_NUMBER() OVER (PARTITION BY invoice ORDER BY (SELECT NULL)) - 1 AS rn
FROM #tmp)A
DROP TABLE #tmp

Cheers
MIK

jscot
Posting Yak Master

106 Posts

Posted - 2011-05-18 : 17:29:18

UPDATE #TMP
SET INVOICE = (SELECT invoice + CASE WHEN rn > 0 THEN '-'+CHAR(ASCII('A')+rn-1) ELSE '' END
FROM (
SELECT invoice, ROW_NUMBER() OVER (PARTITION BY invoice ORDER BY (SELECT NULL)) - 1 AS rn
FROM #tmp)A)

ERROR = Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

ANY ADVICE?

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-05-18 : 17:36:41

Error is clear enough. you are trying to update a unit of cell of the #temp table with a list of values returned by the query .. which is not possible. Make cell to cell mapping via join, something like this

Update Table1
Set Table1.ColumnX=TmpTable.Colx

From Table1
Join

(SELECT RelationColumn,invoice + CASE WHEN rn > 0 THEN '-'+CHAR(ASCII('A')+rn-1) ELSE '' END
FROM (
SELECT invoice, ROW_NUMBER() OVER (PARTITION BY invoice ORDER BY (SELECT NULL)) - 1 AS rn
FROM #tmp)A)tmpTable ON tmpTable.RelationColumn=Table1.RelationColumn

Cheers
MIK

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-18 : 18:20:50

Alternatively, since there are no aggregates, pivots etc. in the cte, you could update the cte itself, which would be like this:


CREATE TABLE #tmp (invoice VARCHAR(255));
insert into #tmp values ('C-125'),('A-567'),('C-125'),('C-125'),('C-125'),('C-125');

;WITH cte1 AS 
(
   SELECT invoice, ROW_NUMBER() OVER (PARTITION BY invoice ORDER BY (SELECT NULL)) - 1 AS rn
   FROM #tmp
)
SELECT
update cte1 set invoice = 
   invoice + CASE WHEN rn > 0 THEN '-'+CHAR(ASCII('A')+rn-1) ELSE '' END
FROM
   cte1

jscot
Posting Yak Master

106 Posts

Posted - 2011-05-18 : 18:21:24

Mike appreciate your help, I am sorry your query make me confuse, dont mind if i am asking to many questions,

Update Table1
Set Table1.ColumnX=TmpTable.Colx

From Table1
Join

(SELECT RelationColumn,invoice + CASE WHEN rn > 0 THEN '-'+CHAR(ASCII('A')+rn-1) ELSE '' END
FROM (
SELECT invoice, ROW_NUMBER() OVER (PARTITION BY invoice ORDER BY (SELECT NULL)) - 1 AS rn
FROM #tmp)A)tmpTable ON tmpTable.RelationColumn=Table1.RelationColumn

Which table should i use for TABLE1 ? i beliver TMPTable is my #TMP,

I HAVE table structure like this

CREATE TABLE tmp (invoice VARCHAR(255),
ID INT IDENTITY)

Help me out what next? THANKS.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-18 : 18:36:44

With the table DDL you posted, you could still use the query I posted at 18:20:50 if you simply change the table name from #tmp to tmp, although you might want to do two things:

First, run a query to see if there are any invoices that have more than 27 dups, like this:


select invoice, count(*) from tmp group by invoice having count(*) > 27

If this returns any rows at all, that means you will run out letters to append and so whatever comes after the letter Z in ASCII character set will get appended.

Second, you might want to change the "ORDER BY (SELECT NULL)" to "ORDER BY ID". It's not required, however, if you do, the appended letters will be in the same order as the IDs.

jscot
Posting Yak Master

106 Posts

Posted - 2011-05-18 : 18:52:35

AWESOME, Thank You, this query solve my problem

CREATE TABLE #tmp (invoice VARCHAR(255));
insert into #tmp values ('C-125'),('A-567'),('C-125'),('C-125'),('C-125'),('C-125');

;WITH cte1 AS
(
SELECT invoice, ROW_NUMBER() OVER (PARTITION BY invoice ORDER BY (SELECT NULL)) - 1 AS rn
FROM #tmp
)
update cte1 set invoice =
invoice + CASE WHEN rn > 0 THEN '-'+CHAR(ASCII('A')+rn-1) ELSE '' END
FROM
cte1

I really appreciate all of you guys to giving me time to solve my problem, GOD BLESS YOU guys!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources