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

General SQL Server Forums

New to SQL Server Programming

query help

Author

Topic

anjali5
Posting Yak Master

121 Posts

Posted - 2012-02-29 : 18:40:50

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 the first record from the table that has filterkey = ---- and EQuipIndex = ----

There are exactly three dashes for filterkey and EquipIndex.

any help will be appreciated.

Thanks.

anjali5
Posting Yak Master

121 Posts

Posted - 2012-02-29 : 20:00:05

Pleaselet me know if any clarification is needed. I freally need help with this.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-29 : 22:33:02

[code]
UPDATE t
SET t.mID = MaxmID
FROM
(
SELECT MAX(mID) OVER (PARTITION BY HNum, DTG) AS MaxmID,mID
FROM Table
WHERE EQuipIndex ='-----'
AND filterKey = '------'
)t
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

anjali5
Posting Yak Master

121 Posts

Posted - 2012-03-01 : 10:45:44

I did this and applied your update statement, it didn't update anything.

I have the following table. the query you provided is not updating anything

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

UPDATE t
SET t.mID = MaxmID
FROM
(
SELECT MAX(mID) OVER (PARTITION BY HNum, DTG) AS MaxmID,mID
FROM TableA
WHERE EQuipIndex ='----'
AND filterKey = '----'

)t
where filterkey='999'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-01 : 10:49:23

what about this?


UPDATE t
SET t.mID = MaxmID 
FROM
(
SELECT MAX(mID) OVER (PARTITION BY HNum, DTG) AS MaxmID,mID
FROM TableA
WHERE filterKey = '999'

)t

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

anjali5
Posting Yak Master

121 Posts

Posted - 2012-03-01 : 11:07:25

It says 4 rows affected, but I don't see any changes in the table itself.

anjali5
Posting Yak Master

121 Posts

Posted - 2012-03-01 : 11:22:00

ok, figured out the solution with your help though.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-01 : 11:27:46

ok..glad that you sorted it out

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources