Professional Web Applications Themes

alter table/tables - Microsoft SQL / MS SQL Server

UPDATE Cust SET CustID = (SELECT NewCustID FROM NewCust WHERE NewCust.CustID = Cust.CustID To generate a script that will update all the tables and create a log(with thanks to T.K. Dinesh): SELECT 'SELECT oc.CustID AS OldCustID, nc.NewCustID, ''' + c.table_name + ''' AS table_name FROM ]' + c.table_name + '] oc INNER JOIN NewCust nc ON oc.CustID = nc.Custid' + CHAR(13) + CHAR(13) + 'UPDATE [' + c.table_name + '] SET CustID = (SELECT NewCustID FROM NewCust WHERE NewCust.CustID = [' + c.table_name + '].CustID' + CHAR(13) FROM information_schema.columns c INNER JOIN information_schema.tables t ON c.table_name = t.table_name WHERE c.column_name ...

  1. #1

    Default Re: alter table/tables

    UPDATE Cust
    SET CustID = (SELECT NewCustID FROM NewCust WHERE NewCust.CustID =
    Cust.CustID

    To generate a script that will update all the tables and create a log(with
    thanks to T.K. Dinesh):

    SELECT
    'SELECT oc.CustID AS OldCustID, nc.NewCustID, ''' + c.table_name + ''' AS
    table_name FROM ]' + c.table_name + '] oc INNER JOIN NewCust nc ON oc.CustID
    = nc.Custid'
    + CHAR(13) + CHAR(13) +
    'UPDATE [' + c.table_name + '] SET CustID = (SELECT NewCustID FROM NewCust
    WHERE NewCust.CustID = [' + c.table_name + '].CustID' + CHAR(13)
    FROM information_schema.columns c
    INNER JOIN information_schema.tables t
    ON c.table_name = t.table_name
    WHERE c.column_name = 'custid'
    AND c.table_name <> 'NewCust'
    AND t.table_type = 'base table'

    Run this in Query yzer with 'results in Text'.

    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Helen Berg" <helen_bergethotmail.com> wrote in message
    news:01df01c34784$3f645120$a001280aphx.gbl...
    > Hi.
    >
    > I have two tables
    > Cust and NewCust
    > In Cust i have the CustID stored
    > In NewCust i have the CustID and NewCustID stored
    > I would like to uppdate the CustID in Cust with
    > the NewCustID in NewCust where Cust.CustID=NewCust.CustID
    >
    > I think my brain left for vaccation to early...just cant
    > get it to work.
    >
    > Question 2
    > Is there a way to perform this agains all Tables in the
    > database where CustID exists without running it once for
    > each table and get some kind of output log of where and
    > which CustID was updated?
    >
    > //Helen

    Jacco Schalkwijk Guest

  2. #2

    Default Re: alter table/tables

    There are two solutions to this:
    [A] You can use the following construct:
    UPDATE c
    SET c.CustID = nc.NewCustID
    FROM
    Cust c INNER JOIN NewCust nc ON
    c.CustID = nc.CustID
    This will work only if there is a 1-1 relation, otherwise only the first
    record from the set of records will be picked up.

    [B] You can also use the following construct
    UPDATE Cust
    SET CustID = (SELECT nc.NewCustID FROM NewCust nc WHERE nc.CustID =
    Cust.CustID)
    This will work only if there is a 1-1 relation, otherwise an error will be
    raised.

    For your other question, you can probably iterate thorugh the columns of all
    the tables and see if CustID is present and if so, execute a similar
    statement as above using dynamic SQL.
    --
    HTH,
    SriSamp
    Please reply to the whole group only!
    [url]http://www32.brinkster.com/srisamp[/url]

    "Helen Berg" <helen_bergethotmail.com> wrote in message
    news:01df01c34784$3f645120$a001280aphx.gbl...
    > Hi.
    >
    > I have two tables
    > Cust and NewCust
    > In Cust i have the CustID stored
    > In NewCust i have the CustID and NewCustID stored
    > I would like to uppdate the CustID in Cust with
    > the NewCustID in NewCust where Cust.CustID=NewCust.CustID
    >
    > I think my brain left for vaccation to early...just cant
    > get it to work.
    >
    > Question 2
    > Is there a way to perform this agains all Tables in the
    > database where CustID exists without running it once for
    > each table and get some kind of output log of where and
    > which CustID was updated?
    >
    > //Helen

    SriSamp Guest

Similar Threads

  1. Alter table query problem
    By Jenny Rice in forum MySQL
    Replies: 3
    Last Post: June 8th, 08:16 AM
  2. Alter Table / Changing field types in SQL
    By Fordian-Slip in forum Coldfusion Database Access
    Replies: 2
    Last Post: January 6th, 11:03 PM
  3. Replies: 1
    Last Post: January 10th, 01:57 PM
  4. alter a DMS table space
    By xixi in forum IBM DB2
    Replies: 0
    Last Post: August 4th, 09:18 PM
  5. alter table move into temporary tablespace.
    By Charles J. Fisher in forum Oracle Server
    Replies: 3
    Last Post: December 16th, 04:18 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