Post reply

Stored procedure help

  • March 1, 2012 at 10:44 am

    #256893

    I need to know how to write stored procedure for updating column in my table.

    I need to search for string value from columnA and need to update this in Column B. My table countains 120,000 rows.

    Table Columns

    machine_id, firmwarerevision, pnpdeviceid0

    Criteria

    If column firmwarerevision is NULL or empty, extract firewarerevison value from deviceID, and update column deviceID

    Script Logic I'm using to determine the value for firmwarerevision

    declare @document varchar(256);

    declare @pos1 int;

    declare @pos2 int;

    declare @len int;

    declare @doc1 varchar(256);

    declare @firmware varchar(50);

    select @document = pnpdeviceid0 from customstorageinfo WHERE firmwarerevison is null

    set @len = LEN(@document)

    set @pos1 = CHARINDEX('REV_',@document)

    set @doc1 = SUBSTRING(@document, @pos1, @len)

    print @document

    print @doc1

    set @pos2 = CHARINDEX('&',@doc1)

    set @firmware = SUBSTRING(@doc1, 5, @pos2 - 5)

    For this to work do I need to use CURSOR, or can this be done another way.

    thx for the help

  • March 1, 2012 at 11:04 am

    #1453789

    does the select statement i'm providing produce the list of items that would need to be updated;

    you didn't mention the actual table naem to be updated...you'll need to change that.

    it looks like oyu could do a single pass UPDATE FROM, but without the acutal table structures and sample data, i'm just guessing.

    SELECT

    SUBSTRING(customstorageinfo.pnpdeviceid0, CHARINDEX('REV_',customstorageinfo.pnpdeviceid0)+4,256)

    AS NEWVALUE,

    * FROM SomeTable,customstorageinfo

    WHERE SomeTable.pnpdeviceid0 = SUBSTRING(customstorageinfo.pnpdeviceid0, CHARINDEX('REV_',customstorageinfo.pnpdeviceid0)+ 4,256)

    AND (SomeTable.firmwarerevision is NULL

    OR SomeTable..firmwarerevision = '')

    /*

    maybe the update

    UPDATE SomeTable

    SomeTable.firmwarerevision =

    From customstorageinfo

    WHERE SomeTable.pnpdeviceid0 = SUBSTRING(customstorageinfo.pnpdeviceid0, CHARINDEX('REV_',customstorageinfo.pnpdeviceid0)+4,256)

    AND (SomeTable.firmwarerevision is NULL

    OR SomeTable.firmwarerevision = '')

    */

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • March 1, 2012 at 3:57 pm

    #1453941

    Hi Lowell,

    The data/column I need to update with the new value is actually in the same table "CustomerStorageInfo"

    I need to do double string function to get the firmwarerevision value

    Example data: SCSIDISK&VEN_HITACHI&PROD_HTS725016A9A364&REV_PCBO4&3938C3D8&0&000

    Lookup1: Search for REV_ using the full string, create new string (Temp Value) ==> PCBO4&3938C3D8&0&000

    Lookup2: Search for '&' using string PCBO4&3938C3D8&0&000

    Output: PCBO4

    Any idea how to construct this in SELECT Statement, you provided already.

    Thx for your help.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply