February 29, 2012 at 4:43 pm
Hi All,
I have the following requirements. I can do this with cursor, but is it possible to do it without a cursor.
Table A
HNum DTG mID filterKey EQuipIndex
N123 200205291249 6759 ----- ----
N123 200205291246 ------ 999 754
N123 200205291246 ------- 999 765
I am not sure how to do this without cursor, but I want to get all the records that have
select Hnum, DTG, MId
from tableA
where filterKey='----'
and EqipIndex ='----'
and mid<> '-----'
and update the last two rows with the mID of the first row. Basically update the rows whose MID is ---- with the row who has the MID and whose filter key = '----' and EQuipIndex='----' and where the hull num and dtg is equal and filter key is 999
so
Update TableA
set mid =6759
where hnum= @hnum(hull number of the first row)
and DTg= @DTG of the first row
and filterkey = '999'
insert that first record in some text file
and then delete that first record from the table that has filterkey = ---- and EQuipIndex = ----
There are eactly four dashes in equiIndex and filterkey.
any help will be apprecaited.
Thanks.
February 29, 2012 at 7:11 pm
Hello Anjali..
Can you be more specific on your ask? I see some anamolies. The query you provided wont work.
Here is the set-up. Play with it and give exactly how your data looks like and what expected output u are wanting
IF OBJECT_ID('TempDB..#SourceTable') IS NOT NULL
DROP TABLE #SourceTable
IF OBJECT_ID('TempDB..#BlankKeys') IS NOT NULL
DROP TABLE #BlankKeys
CREATE TABLE #SourceTable
(
HNum VARCHAR(4)
,DTG BIGINT
,mID VARCHAR(6)
,filterKey VARCHAR(6)
,EQuipIndex VARCHAR(6)
)
INSERT INTO #SourceTable (HNum, DTG, mID, filterKey, EQuipIndex )
SELECT 'N123' ,200205291249 ,'6759' ,'----' ,'----'
UNION ALL SELECT 'N123' ,200205291246 ,'------' ,'999' ,'754'
UNION ALL SELECT 'N123' ,200205291246 ,'-----' ,'999' ,'765'
UNION ALL SELECT 'N456' ,200205291249 ,'5432' ,'----' ,'----'
UNION ALL SELECT 'N456' ,200205291246 ,'------' ,'999' ,'754'
UNION ALL SELECT 'N456' ,200205291246 ,'-----' ,'999' ,'765'
Some code to kick start you
SELECT * FROM #SourceTable
SELECT InrTab.HNum ,InrTab.DTG , InrTab.mID
INTO #BlankKeys
FROM #SourceTable InrTab
WHERE InrTab.filterKey = '----' AND EQuipIndex = '----'
SELECT * FROM #BlankKeys
--UPDATE ST
--SET ST.mID = BK.mID
SELECT *
FROM #SourceTable ST
INNER JOIN #BlankKeys BK
ON ST.DTG = BK.DTG
AND ST.HNum = BK.HNum
WHERE ST.filterKey = '999'
SELECT * FROM #SourceTable
DELETE #SourceTable
WHERE filterKey = '----' AND EQuipIndex = '----'
SELECT * FROM #SourceTable
March 1, 2012 at 6:32 am
What you really need is this result:
SELECT blank_mid.HNum, blank_mid.DTG, has_mid.mID, blank_mid.filterKey, blank_mid.EQuipIndex
FROM #TableA blank_mid -- Per each row with blank mid...
JOIN #TableA has_mid -- ...find a row that has mid.
ON has_mid.HNum = blank_mid.HNum
AND has_mid.DTG = blank_mid.DTG
WHERE blank_mid.mID like '%-%'
AND has_mid.mID NOT like '%-%'
AND blank_mid.filterKey = '999'
But, if you insist to update and delete that table, you can do it like this:
UPDATE blank_mid SET blank_mid.mID = has_mid.mID
FROM #TableA blank_mid -- Per each row with blank mid...
JOIN #TableA has_mid -- ...find a row that has mid.
ON has_mid.HNum = blank_mid.HNum
AND has_mid.DTG = blank_mid.DTG
WHERE blank_mid.mID like '%-%'
AND has_mid.mID NOT like '%-%'
AND blank_mid.filterKey = '999'
DELETE FROM #TableA
WHERE filterKey like '%-%' AND EQuipIndex like '%-%'
Kind regards,
Vedran
March 1, 2012 at 8:54 am
I created the table this way
create TABLE TableA
(
HNum VARCHAR(4)
,DTG BIGINT
,mID VARCHAR(6)
,filterKey VARCHAR(6)
,EQuipIndex VARCHAR(6)
)
INSERT INTO TableA (HNum, DTG, mID, filterKey, EQuipIndex )
SELECT 'N123' ,200205291249 ,'6759' ,'----' ,'----'
UNION ALL SELECT 'N123' ,200205291246 ,'----' ,'999' ,'754'
UNION ALL SELECT 'N123' ,200205291246 ,'----' ,'999' ,'765'
UNION ALL SELECT 'N456' ,200205291249 ,'5432' ,'----' ,'----'
UNION ALL SELECT 'N456' ,200205291246 ,'----' ,'999' ,'754'
UNION ALL SELECT 'N456' ,200205291246 ,'----' ,'999' ,'765'
select * from TableA
and someohow the below statement is returning 0 rows.
SELECT blank_mid.HNum, blank_mid.DTG, has_mid.mID, blank_mid.filterKey, blank_mid.EQuipIndex
FROM #TableA blank_mid -- Per each row with blank mid...
JOIN #TableA has_mid -- ...find a row that has mid.
ON has_mid.HNum = blank_mid.HNum
AND has_mid.DTG = blank_mid.DTG
WHERE blank_mid.mID like '%-%'
AND has_mid.mID NOT like '%-%'
March 1, 2012 at 11:34 am
DTG numbers are wrong.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply