Professional Web Applications Themes

Insert into same table pulling one column from another table - Microsoft SQL / MS SQL Server

I have two tables --- table1 (grp_id, unit, score, mbr_cd) table2 (rl, cid,unit) The data in table1 is like this 4 CA 600 1 4 CA 600 2 4 CA 600 3 4 CA 600 4 Table2 is like this AU8.7 56 AK AU8.7 125 AL AU8.7 27 WA AU8.7 32 OK I need to copy the rows in table1, update the unit from table2 and then insert all those rows back into table1. So I end up with something like this 4 CA 600 1 4 CA 600 2 4 CA 600 3 4 CA 600 4 4 AL ...

  1. #1

    Default Insert into same table pulling one column from another table



    I have two tables ---

    table1 (grp_id, unit, score, mbr_cd)

    table2 (rl, cid,unit)


    The data in table1 is like this
    4 CA 600 1
    4 CA 600 2
    4 CA 600 3
    4 CA 600 4

    Table2 is like this
    AU8.7 56 AK
    AU8.7 125 AL
    AU8.7 27 WA
    AU8.7 32 OK


    I need to copy the rows in table1, update the unit from table2 and then
    insert all those rows back into table1.

    So I end up with something like this

    4 CA 600 1
    4 CA 600 2
    4 CA 600 3
    4 CA 600 4
    4 AL 600 1
    4 AL 600 2
    4 AL 600 3
    4 AL 600 4
    4 WA 600 1
    4 WA 600 2
    4 WA 600 3
    4 WA 600 4

    I've tried the cursor, but can't seem to use it in the insert
    statement..
    Any ideas?

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Fred Guest

  2. #2

    Default Re: Insert into same table pulling one column from another table

    fred,
    Try this
    Select * into Table3 from Table1
    Delete table 1
    select t3.* from table3 t3 join table2 t2
    on t3.state = t2.state
    where t2.state <> 'OK'
    drop table table3


    "Fred" <com> wrote in message
    news:phx.gbl... 


    HSalim Guest

  3. #3

    Default Re: Insert into same table pulling one column from another table



    You guys are Da Bomb!!
    Thanks for the help..


    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Fred Guest

Similar Threads

  1. Pulling table column names
    By Matthew in forum Coldfusion Database Access
    Replies: 1
    Last Post: June 30th, 07:57 PM
  2. How Do you insert a table in Previous Table
    By jaze18 in forum Dreamweaver AppDev
    Replies: 0
    Last Post: May 3rd, 07:46 PM
  3. Replies: 0
    Last Post: July 23rd, 11:30 PM
  4. Insert into <table w/ text column> select distinct ...
    By Adam Nester in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: June 30th, 04:38 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