March 15, 2011 at 8:42 am
Hello all,
This is probably so simple I will kick myself when I find the answer, but I had to get up 3 hours early to take my son to the airport so I'm not as sharp today as I would like, any help is greatly appreciated! That being said, here is my dilemma:
I want to change the value of the PRODNO to be the correct length based on the value in the TABLE_PRODNOLEN
Here's what I'm starting with, but it (obviously) doesn't work. (I have included DDL/DML below for you guru's to play with!)
TIA,
KK
UPDATE TABLE_PRODS
SET PRODNO = (SELECT DISTINCT RIGHT(TP.PRODNO, TPL.PRODNOLEN) As PRODNO
FROM TABLE_PRODS TP
LEFT OUTER JOIN TABLE_LOG TL ON TL.ID = TP.LOG_ID
LEFT OUTER JOIN TABLE_PRODNOLEN TPL ON TPL.DIST_ID = TL.DIST_ID
AND TPL.PROD_ID = TL.PROD_ID
WHERE PRODNO = TP.PRODNO)
----------------DDL/DML----
CREATE TABLE [dbo].[TABLE_PRODS](
[ID] [int] IDENTITY(1,1) NOT NULL,
[PRODNO] [varchar] (20) NOT NULL,
[LOG_ID] [int] NOT NULL,
[prodDESCRIPTION] [varchar](50) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[TABLE_LOG](
[ID] [int] IDENTITY(1,1) NOT NULL,
[DIST_ID] [int]) NOT NULL,
[PROD_ID] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[TABLE_PRODNOLEN](
[ID] [int] IDENTITY(1,1) NOT NULL,
[PRODNOLEN] [int]) NOT NULL,
[PROD_ID] [int] NOT NULL,
[DIST_ID] [int] NOT NULL
) ON [PRIMARY]
GO
INSERT TABLE_PRODS
(PRODNO, LOG_ID, prodDESCRIPTION)
SELECT '00000000000123456789', 1, 'Product1' UNION ALL --SHOULD BE '000123456789'
SELECT '00000000000121212121', 1, 'Product2' UNION ALL --SHOULD BE '000121212121'
SELECT '00000000000123456789', 2, 'Product3' UNION ALL --SHOULD BE '123456789
SELECT '00000000000999999999', 2, 'Product4' UNION ALL --SHOULD BE '999999999'
SELECT '00000000000987654321', 3, 'Product5' UNION ALL --SHOULD BE '0987654321'
INSERT TABLE_LOG
(DIST_ID, PROD_ID)
SELECT 10, 20, UNION ALL
SELECT 30, 40, UNION ALL
SELECT 50, 60, UNION ALL
INSERT TABLE_PRODNOLEN
(PRODNOLEN, PROD_ID, DIST_ID)
SELECT 12, 10, 20, UNION ALL
SELECT 9, 30, 40, UNION ALL
SELECT 10, 50, 60, UNION ALL
March 15, 2011 at 8:51 am
You are pretty close but you cant set a column equal to a select statement.
UPDATE TABLE_PRODS
SET PRODNO = RIGHT(TP.PRODNO, TPL.PRODNOLEN)
FROM TABLE_PRODS TP
LEFT OUTER JOIN TABLE_LOG TL ON TL.ID = TP.LOG_ID
LEFT OUTER JOIN TABLE_PRODNOLEN TPL ON TPL.DIST_ID = TL.DIST_ID
AND TPL.PROD_ID = TL.PROD_ID
That should get you close. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 15, 2011 at 9:10 am
SEE! I knew it was simple! Thanks a million! (The data in the length table was a little skewed, and I had some extra commas and parentheses in there, sorry about that... I'll check my DML better in the future!) 😀
SSC ROCKS!
Thanks!
KK
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply