Professional Web Applications Themes

UPDATE problem... - Microsoft SQL / MS SQL Server

Hi, My tables are like this... TABLE A TABLE B col1 col2 col1 col2 col3 1 m 1 m NULL(1) 1 n 1 m NULL 1 n NULL(1) 2 m 2 n NULL(1) 2 n 2 m NULL(1) 2 n NULL 3 p NULL I want to update TABLE B with col3 as '1' for the corresponding records found in TABLE A per only one record.(The result I wanted are mentioned in the bracket). Thanks...

  1. #1

    Default UPDATE problem...

    Hi,

    My tables are like this...

    TABLE A TABLE B
    col1 col2 col1 col2 col3
    1 m 1 m NULL(1)
    1 n 1 m NULL
    1 n NULL(1)
    2 m 2 n NULL(1)
    2 n 2 m NULL(1)
    2 n NULL
    3 p NULL

    I want to update TABLE B with col3 as '1' for the corresponding records
    found in TABLE A per only one record.(The result I wanted are mentioned in
    the bracket).

    Thanks







    Vgs Guest

  2. #2

    Default Re: UPDATE problem...

    TableB as posted doesn't have a primary key. Please post the proper DDL
    (CREATE TABLE statements) for the tables.

    Explain what differentiates these two rows in TableB:
    (1, m, NULL(1))
    (1, m, NULL)

    how do you define which one to UPDATE?

    Possibly the UPDATE statement will look something like this:

    UPDATE TableB
    SET col3 = 1
    WHERE EXISTS
    (SELECT *
    FROM TableA
    WHERE col1 = TableB.col1 AND col2 = TableB.col2)

    (untested)

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


    David Guest

  3. #3

    Default Re: UPDATE problem...

    Try:

    UPDATE B
    SET col3 = 1
    FROM B INNER JOIN A
    ON B.col1 = A.col1
    AND B.col2 = A.col2

    --OR

    UPDATE B
    SET col3 = 1
    WHERE EXISTS (
    SELECT 1 FROM A
    WHERE A.col1 = B.col1
    AND A.col2 = B.col2)

    HTH


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


    Amy Guest

Similar Threads

  1. update problem
    By xarrisx in forum ASP Database
    Replies: 2
    Last Post: October 14th, 05:13 AM
  2. Problem with ' in SQL Update
    By Drew in forum ASP Database
    Replies: 2
    Last Post: August 25th, 02:13 PM
  3. inker problem (an update to the 'general problem')
    By eRez in forum Macromedia Director 3D
    Replies: 0
    Last Post: December 29th, 12:26 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