Professional Web Applications Themes

Help with Tricky UPDATE sql statements. - Microsoft SQL / MS SQL Server

Hi all, I have 4 rows in a table and I need to update row 3 and 4 to 1 and 2 with a new values and delete row 3 and 4 after the update is done. Any help would greatly appreciate. Please look at me desire result. Once again, Thank you for your help. drop table #Temp go CREATE TABLE #Temp ( Policy_id INT NULL, NamedInsured_id INT NULL, First_nm VARCHAR(15) NULL, Last_nm VARCHAR(15) NULL, Birth_dt DATETIME NULL, HomePhone_nb CHAR(10) NULL ) GO INSERT #Temp VALUES (183074, 1, 'Peter', 'BUMACOD', '1960- 09-02', '3105497197') INSERT #Temp VALUES (183074, 2, 'EVELYN', 'BUMACOD', ...

  1. #1

    Default Help with Tricky UPDATE sql statements.

    Hi all,

    I have 4 rows in a table and I need to update row 3 and 4
    to 1 and 2 with a new values and delete row 3 and 4 after
    the update
    is done. Any help would greatly appreciate. Please look
    at me desire result. Once again, Thank you for your help.


    drop table #Temp
    go
    CREATE TABLE #Temp
    (
    Policy_id INT NULL,
    NamedInsured_id INT NULL,
    First_nm VARCHAR(15) NULL,
    Last_nm VARCHAR(15) NULL,
    Birth_dt DATETIME NULL,
    HomePhone_nb CHAR(10) NULL
    )
    GO

    INSERT #Temp VALUES (183074, 1, 'Peter', 'BUMACOD', '1960-
    09-02', '3105497197')
    INSERT #Temp VALUES (183074, 2, 'EVELYN', 'BUMACOD', '1960-
    07-06', '3105497197')
    INSERT #Temp VALUES (183074, 3, 'Peter', 'BUMACOD', '1950-
    10-02', '310123456')
    INSERT #Temp VALUES (183074, 4, 'EVELYN', 'BUMACOD', '1966-
    04-08', '310123456')
    go


    select *
    from #Temp
    go

    -- Result want:
    -- Update the Birth_dt and HomePhone_nb with new values.
    Policy_id NamedInsured_id First_nm
    Last_nm
    Birth_dt
    HomePhone_nb
    ----------- --------------- --------------- ---------------
    ------------------------------------------------------ ---
    ---------
    183074 1 Peter
    BUMACOD 1950-10-02
    00:00:00.000 310123456
    183074 2 EVELYN
    BUMACOD 1966-04-08
    00:00:00.000 310123456


    Lam Guest

  2. #2

    Default Re: Help with Tricky UPDATE sql statements.

    Try:

    update a set birth_dt = b.birth_dt,
    homephone_nb=b.homephone_nb
    from #temp a, #temp b
    where a.NamedInsured_id in (1,2)
    and b.NamedInsured_id in(3,4)
    and a.first_nm =b.first_nm
    and a.last_nm = b.last_nm

    delete from #temp where NamedInsured_id in(3,4)

    select * from #temp

    -Vishal
     

    after 
    look 
    help. 
    1, 'Peter', 'BUMACOD', '1960- 
    2, 'EVELYN', 'BUMACOD', '1960- 
    3, 'Peter', 'BUMACOD', '1950- 
    4, 'EVELYN', 'BUMACOD', '1966- 
    -- 
    -- 
    Vishal Guest

  3. #3

    Default Re: Help with Tricky UPDATE sql statements.


    What if the name changes. Example insert 4 rows and
    nothing would update. Can update statement base on the
    keys which is Policy_id, NamedInsured_id. Thanks for
    trying.

    INSERT #Temp VALUES (183074, 1, 'Peter', 'BUMACOD', '1960-
    09-02', '3105497197')
    INSERT #Temp VALUES (183074, 2, 'EVELYN', 'BUMACOD', '1960-
    07-06', '3105497197')
    INSERT #Temp VALUES (183074,
    3, 'Peterx', 'BUMACOD', '1950-10-02', '310123456')
    INSERT #Temp VALUES (183074,
    4, 'EVELYNx', 'BUMACOD', '1966-04-08', '310123456')
    go



     
    >4 
    >after 
    >look 
    >help. 
    >1, 'Peter', 'BUMACOD', '1960- 
    >2, 'EVELYN', 'BUMACOD', '1960- 
    >3, 'Peter', 'BUMACOD', '1950- 
    >4, 'EVELYN', 'BUMACOD', '1966- 
    >-- 
    >-- 
    >.
    >[/ref]
    Lam Guest

  4. #4

    Default Re: Help with Tricky UPDATE sql statements.

    Like this?:

    DELETE FROM #Temp WHERE NamedInsured_id < 3
    UPDATE #Temp SET NamedInsured_id = NamedInsured_id - 2

    Normally this might conflict with any FK constraints but since your table as
    posted doesn't actually have a primary key it seems like a resonable
    solution!

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --



    David Guest

Similar Threads

  1. Only select statements work (update/insert hang)
    By pdmackay in forum Coldfusion Database Access
    Replies: 3
    Last Post: December 28th, 08:28 AM
  2. Syntax error in INSERT INTO and UPDATE statements
    By Snowball2050 in forum Coldfusion Database Access
    Replies: 3
    Last Post: September 11th, 07:38 PM
  3. #24682 [Com]: DELETE/ Update SQL Statements do not work
    By strattonbrazil at hotmail dot com in forum PHP Development
    Replies: 0
    Last Post: November 12th, 02:17 PM
  4. #24682 [Fbk->NoF]: DELETE/ Update SQL Statements do not work
    By sniper@php.net in forum PHP Development
    Replies: 0
    Last Post: July 21st, 06:27 AM
  5. #24682 [Opn->Fbk]: DELETE/ Update SQL Statements do not work
    By derick@php.net in forum PHP Development
    Replies: 0
    Last Post: July 16th, 04:59 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139