September 18, 2013 at 10:19 am
Hi,
MyTotUnits field should get updated from field Units based on the condition below :
When Project ,Product ( UC only) and (LIKE) SSum (P-110 from ID = 1 IS CONTAINED IN ID = 2 FOR THE SAME PROJECT AND Product . ANd SNum for ID = 2 contains for characters than SNum for ID =1 ) then Units = 430 should be updated to MyTotUnits from ID =2
CREATE TABLE #temptable ( ID INT IDENTITY(1,1),Project VARCHAR(10),Product VARCHAR(20) ,SNum VARCHAR(20),Units FLOAT , MyTotUnis FLOAT)
INSERT INTO #temptable (Project ,Product ,SNum ,Units , MyTotUnis)
SELECT 'P1','xxx','P-110',20,0
UNION
SELECT 'P1','xxx','P-110',30,0
UNION
SELECT 'P1','UC','P-110',40,0
UNION
SELECT 'P1','xxx','P-110_ABC',40,0
UNION
SELECT 'P1','xxx','P-110_ABC',40,0
UNION
SELECT 'P1','xxx','P-110_ABC',40,0
UNION
SELECT 'P1','UC','P-110_ABC',430,0
UNION
SELECT 'P2','xxx','P-123',40,0
UNION
SELECT 'P2','xxx','P-123',70,0
SELECT * FROM #temptable
DROP TABLE #temptable
September 18, 2013 at 10:38 am
PSB (9/18/2013)
Hi,MyTotUnits field should get updated from field Units based on the condition below :
When Project ,Product ( UC only) and (LIKE) SSum (P-110 from ID = 1 IS CONTAINED IN ID = 2 FOR THE SAME PROJECT AND Product . ANd SNum for ID = 2 contains for characters than SNum for ID =1 ) then Units = 430 should be updated to MyTotUnits from ID =2
CREATE TABLE #temptable ( ID INT IDENTITY(1,1),Project VARCHAR(10),Product VARCHAR(20) ,SNum VARCHAR(20),Units FLOAT , MyTotUnis FLOAT)
INSERT INTO #temptable (Project ,Product ,SNum ,Units , MyTotUnis)
SELECT 'P1','xxx','P-110',20,0
UNION
SELECT 'P1','xxx','P-110',30,0
UNION
SELECT 'P1','UC','P-110',40,0
UNION
SELECT 'P1','xxx','P-110_ABC',40,0
UNION
SELECT 'P1','xxx','P-110_ABC',40,0
UNION
SELECT 'P1','xxx','P-110_ABC',40,0
UNION
SELECT 'P1','UC','P-110_ABC',430,0
UNION
SELECT 'P2','xxx','P-123',40,0
UNION
SELECT 'P2','xxx','P-123',70,0
SELECT * FROM #temptable
DROP TABLE #temptable
Great job posting ddl and sample data. However, the description has me completely scratching my head. I have read and reread that description about 15-20 times and each time it makes less and less sense to me. Can you please try to explain more clearly what you want to happen here?
It would probably be a good idea if you could also post the details of what the data should look like after your update.
_______________________________________________________________
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/
September 18, 2013 at 11:03 am
When joining #temptable T1 to #temptable T2 on T1.Project = T2.Project , AND T1.Product = T2.Product LEFT(T1.SNum ,5) = LEFT(T2.SNum ,5)
WHERE T.Product ='UC' AND T2.Product ='UC' AND LEN(T2.SNUM) > LEN(T1.SNUM)
we want to update T1.MyTotUnits field to T2.Units
I want changes to ID =1 only,as given below "
SELECT 1 AS ID,'P1' AS Project, 'UC' AS Product ,'P-110' AS SNum,40 AS Units,430 AS MyTotUnits
All others should be same.
September 18, 2013 at 12:59 pm
PSB (9/18/2013)
When joining #temptable T1 to #temptable T2 on T1.Project = T2.Project , AND T1.Product = T2.Product LEFT(T1.SNum ,5) = LEFT(T2.SNum ,5)WHERE T.Product ='UC' AND T2.Product ='UC' AND LEN(T2.SNUM) > LEN(T1.SNUM)
we want to update T1.MyTotUnits field to T2.Units
I want changes to ID =1 only,as given below "
SELECT 1 AS ID,'P1' AS Project, 'UC' AS Product ,'P-110' AS SNum,40 AS Units,430 AS MyTotUnits
All others should be same.
Your rules and your desired output don't match up. You said you want to update the row where ID = 1 but the where clause will not find that row because the Product <> 'UC'.
Keep in mind that I can't see your screen, I have no idea what your project is, the only details I have are what you have posted. There is no rhyme or reason to your logic here.
_______________________________________________________________
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/
September 18, 2013 at 6:50 pm
Perhaps like this?
UPDATE a
SET MyTotUnis = ISNULL(
(
SELECT TOP 1 Units
FROM #temptable b
WHERE a.Project = b.Project AND
LEFT(b.SNum, 5) = a.SNum AND a.SNum <> b.SNum
ORDER BY ID
), MyTotUnis)
FROM #temptable a
WHERE Product = 'UC' AND LEN(SNum) = 5
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply