Results 1 to 11 of 11

Thread: Update query doubt

  1. #1
    Join Date
    Feb 2003
    Posts
    23

    Update query doubt

    I have a table with 2 fields

    JOBID PARAMS
    FFR054 Test1=1;Test2=2
    FFR054 Test1=5;Test2=6
    FFR054 Test1=2;Test2=7


    Now when i run an update query. I want to remove all the Test1 entries from PARAMS field. The new table structure should be like

    JOBID PARAMS
    FFR054 Test2=2
    FFR054 Test2=6
    FFR054 Test2=7

    Thanks for any help

  2. #2
    Join Date
    Mar 2003
    Location
    NJ
    Posts
    201
    Try this


    update TB1 set jobid = left(jobid,charindex('Test',jobid)-1) from TB1

  3. #3
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    Try this

    update TB1
    set PARAMS = substring(PARAMS,charindex(';',PARAMS)+1,len(PARAM S)-charindex(';',PARAMS))

  4. #4
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    create table TB1 (JOBID varchar(10),PARAMS varchar(20))
    insert into TB1 select 'FFR054', 'Test1=1;Test2=2'
    insert into TB1 select 'FFR054', 'Test1=5;Test2=6'
    insert into TB1 select 'FFR054', 'Test1=2;Test2=7'


    update TB1 set params = right(params,(len(params)-charindex(';',params,1)))

    select * from TB1

  5. #5
    Join Date
    Feb 2003
    Posts
    23
    Thanks guys for ur input. But unfortunately, I think I failed to convey my requirement properly.

    The table and fields are just an example structure.

    I am actually executing the query against a SQL Server from a VB.NET code.

    JOBID PARAMS
    FFR054 Param1=1;Param2=2;Param3=5
    FFR054 Param1=5;Param2=6;Param3=6
    FFR054 Param1=2;Param2=7;Param3=7

    I will only know, Which Parameter to be removed at the RUN TIME OF THE CODE.

    So for example, if Param3 is to be removed. I would like only Param1 and Param2 alone in the tables. So is the case.

    It does not matter to have a Semicolon ( at the end like

    Param1=1;Param2=2;

    But two semicolons should not be there continuously as in (this might happen if Param2 is to be removed)

    Param1=1;;Param3=3

    Pls let me know, if this is possible through a SQL query, if not I sd manipulate it through VB code.

    Thanks for any help

  6. #6
    Join Date
    Mar 2003
    Location
    NJ
    Posts
    201
    Might be more than one parameter got removed ?

  7. #7
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    You can write a stored procedure which takes a parameter to indicate which parameter to be removed, then write three different update statements to chop off the parameter.

  8. #8
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    I havent optimized it, but this works

    declare @params varchar(50)
    --pass 'param1=' or 'param2=' or 'param3='


    set @params = 'param1='
    --case1
    update TB1 set params= case charindex(';',params,charindex(@params,params,1) )
    when 0 then substring(params, charindex(@params,params,1), (len(params)-charindex(@params,params,1))+1)
    else substring(params, charindex(@params,params,1),charindex(';',params,c harindex(@params,params,1) ) -charindex(@params,params,1))
    end from tb1

  9. #9
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    OOPS. I have to do a inverse of the above sql statement I gave. Here you go


    --truncate table tb1
    --insert into tb1 select 'FFR054','Param1=1;Param2=2;Param3=5'
    --insert into tb1 select 'FFR054','Param1=5;Param2=6;Param3=6'
    --insert into tb1 select 'FFR054','Param1=2;Param2=7;Param3=7'

    declare @params varchar(50)
    --pass 'param1=' or 'param2=' or 'param3='

    set @params = 'param3='
    --case1
    update TB1 set params= case charindex(';',params,charindex(@params,params,1) )
    when 0 then replace(params,substring(params, charindex(';'+@params,params,1),(len(params)-charindex(';'+@params,params,1))+1) ,'')
    else replace(replace(params,substring(params, charindex(@params,params,1), 1+charindex(';',params,charindex(@params,params,1) ) -charindex(@params,params,1)),''),';;','')
    end from tb1

  10. #10
    Join Date
    Feb 2003
    Posts
    23
    Thanks MAK for your response. I got it. I used the ur second method and modified it a little to suit my output.

    This is the query that I have now to do that

    UPDATE dbo.TABLE

    SET PARAMS =

    CASE CHARINDEX('PARAM=1', PARAMS)

    WHEN 0 THEN PARAMS

    ELSE

    REPLACE(PARAMS,SUBSTRING(PARAMS, CHARINDEX ('PARAM=1', PARAMS),
    (CHARINDEX(';', PARAMS, CHARINDEX('PARAM=1', PARAMS)) - CHARINDEX('PARAM=1', PARAMS)) + 1), '')

    END

    Thanks for your help again.

  11. #11
    Join Date
    Feb 2003
    Posts
    23
    Oops I posted the query wrongly.. here is the right one. I changed the PARAM=1 in the previous to PARAM1= Where PARAM1 is the Selected Parameter to be deleted.

    This is the updated query

    UPDATE dbo.TABLE

    SET PARAMS =

    CASE CHARINDEX('PARAM1=', PARAMS)

    WHEN 0 THEN PARAMS

    ELSE

    REPLACE(PARAMS,SUBSTRING(PARAMS, CHARINDEX ('PARAM1=', PARAMS),
    (CHARINDEX(';', PARAMS, CHARINDEX('PARAM1=', PARAMS)) - CHARINDEX('PARAM1=', PARAMS)) + 1), '')

    END

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •