Loading ...
Sorry, an error occurred while loading the content.

Please Solve This

Expand Messages
  • ravi santlani
    One of the columns is moved from one table to some other table in the same database. How can one find the name of the new table where the column has been
    Message 1 of 12 , Jul 10, 2009
    • 0 Attachment
      One of the columns is moved from one table to some other table in the same
      database. How can one find the name of the new table where the column has
      been moved?
      e. g.

      Table EmployeeMasterEmpID
      EmpFirstName
      EmpLastName
      EmpAddress

      Table EmployeeDetailsEmpID
      EmpPhoneNumber
      EmpEmail

      If "EmpAddress" is moved from EmployeeMaster to EmployeeDetails by one
      person, how other person will know the new table name where the column is
      moved?


      [Non-text portions of this message have been removed]
    • John Warner
      Ask and threaten death if they ever do this again without making the entire department aware of the plan. This can break more than just a single query and
      Message 2 of 12 , Jul 11, 2009
      • 0 Attachment
        Ask and threaten death if they ever do this again without making the
        entire department aware of the plan. This can break more than just a
        single query and everyone should be involved or at least aware of the
        pending change.

        To find the column (SQL Server)

        USE your_database

        SELECT TABLE_NAME
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE COLUMN_NAME = 'EmpAddress'

        Note this could return more than one row as column names do not have to be
        unique to a database, only to the table.

        John Warner


        > -----Original Message-----
        > From: SQLQueriesNoCode@yahoogroups.com
        > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of ravi santlani
        > Sent: Friday, July 10, 2009 11:38 AM
        > To: SQLQueriesNoCode@yahoogroups.com
        > Subject: [SQLQueriesNoCode] Please Solve This
        >
        > One of the columns is moved from one table to some other table in the
        same
        > database. How can one find the name of the new table where the column
        has
        > been moved?
        > e. g.
        >
        > Table EmployeeMasterEmpID
        > EmpFirstName
        > EmpLastName
        > EmpAddress
        >
        > Table EmployeeDetailsEmpID
        > EmpPhoneNumber
        > EmpEmail
        >
        > If "EmpAddress" is moved from EmployeeMaster to EmployeeDetails by one
        > person, how other person will know the new table name where the column
        is
        > moved?
        >
        >
        > [Non-text portions of this message have been removed]
        >
        >
        >
        > ------------------------------------
        >
        > Yahoo! Groups Links
        >
        >
        >
      • Hans Raj Sharma
        in oracle you can use *all_updatable_columns* table to see all the columns and their tables. On Fri, Jul 10, 2009 at 9:08 PM, ravi
        Message 3 of 12 , Jul 11, 2009
        • 0 Attachment
          in oracle you can use *all_updatable_columns* table to see all the columns
          and their tables.


          On Fri, Jul 10, 2009 at 9:08 PM, ravi santlani<ravi.capgemini@...>
          wrote:
          >
          >
          > One of the columns is moved from one table to some other table in the same
          > database. How can one find the name of the new table where the column has
          > been moved?
          > e. g.
          >
          > Table EmployeeMasterEmpID
          > EmpFirstName
          > EmpLastName
          > EmpAddress
          >
          > Table EmployeeDetailsEmpID
          > EmpPhoneNumber
          > EmpEmail
          >
          > If "EmpAddress" is moved from EmployeeMaster to EmployeeDetails by one
          > person, how other person will know the new table name where the column is
          > moved?
          >
          > [Non-text portions of this message have been removed]
          >


          [Non-text portions of this message have been removed]
        • John Warner
          So Oracle is not SQL-92 complaint yet with Information_Schema support ? John Warner ... columns ... same ... is
          Message 4 of 12 , Jul 12, 2009
          • 0 Attachment
            So Oracle is not SQL-92 complaint yet with Information_Schema support ?

            John Warner


            > -----Original Message-----
            > From: SQLQueriesNoCode@yahoogroups.com
            > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Hans Raj
            > Sharma
            > Sent: Saturday, July 11, 2009 4:58 PM
            > To: SQLQueriesNoCode@yahoogroups.com
            > Subject: Re: [SQLQueriesNoCode] Please Solve This
            >
            > in oracle you can use *all_updatable_columns* table to see all the
            columns
            > and their tables.
            >
            >
            > On Fri, Jul 10, 2009 at 9:08 PM, ravi santlani<ravi.capgemini@...>
            > wrote:
            > >
            > >
            > > One of the columns is moved from one table to some other table in the
            same
            > > database. How can one find the name of the new table where the column
            > has
            > > been moved?
            > > e. g.
            > >
            > > Table EmployeeMasterEmpID
            > > EmpFirstName
            > > EmpLastName
            > > EmpAddress
            > >
            > > Table EmployeeDetailsEmpID
            > > EmpPhoneNumber
            > > EmpEmail
            > >
            > > If "EmpAddress" is moved from EmployeeMaster to EmployeeDetails by one
            > > person, how other person will know the new table name where the column
            is
            > > moved?
            > >
            > > [Non-text portions of this message have been removed]
            > >
            >
            >
            > [Non-text portions of this message have been removed]
            >
            >
            >
            > ------------------------------------
            >
            > Yahoo! Groups Links
            >
            >
            >
          • John Warner
            I may be erroring on something else, but my question is does the UPDATE statement allow table aliasing? UPDATE myDataBase.dbo.myTable m INNER JOIN
            Message 5 of 12 , Jul 16, 2009
            • 0 Attachment
              I may be erroring on something else, but my question is does the UPDATE
              statement allow table aliasing?

              UPDATE myDataBase.dbo.myTable m
              INNER JOIN YourDatabase.dbo.yourTable y
              ON m.field = y.field
              SET m.cust = y.cust
              WHERE m.cust IS NULL


              John Warner
            • Tim Mitchell
              John, try this instead: UPDATE m SET m.cust = y.cust FROM myDataBase.dbo.myTable m INNER JOIN YourDatabase.dbo.yourTable y ON m.field = y.field WHERE m.cust IS
              Message 6 of 12 , Jul 16, 2009
              • 0 Attachment
                John, try this instead:



                UPDATE m
                SET m.cust = y.cust
                FROM myDataBase.dbo.myTable m

                INNER JOIN YourDatabase.dbo.yourTable y
                ON m.field = y.field
                WHERE m.cust IS NULL





                Tim



                From: SQLQueriesNoCode@yahoogroups.com
                [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner
                Sent: Thursday, July 16, 2009 2:35 PM
                To: SQLQueriesNoCode@yahoogroups.com
                Subject: [SQLQueriesNoCode] SQL Server 2005 UPDATE syntax ???





                I may be erroring on something else, but my question is does the UPDATE
                statement allow table aliasing?

                UPDATE myDataBase.dbo.myTable m
                INNER JOIN YourDatabase.dbo.yourTable y
                ON m.field = y.field
                SET m.cust = y.cust
                WHERE m.cust IS NULL

                John Warner





                [Non-text portions of this message have been removed]
              • John Warner
                Thanks! John Warner
                Message 7 of 12 , Jul 16, 2009
                • 0 Attachment
                  Thanks!

                  John Warner


                  > -----Original Message-----
                  > From: SQLQueriesNoCode@yahoogroups.com
                  > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Tim Mitchell
                  > Sent: Thursday, July 16, 2009 3:38 PM
                  > To: SQLQueriesNoCode@yahoogroups.com
                  > Subject: RE: [SQLQueriesNoCode] SQL Server 2005 UPDATE syntax ???
                  >
                  > John, try this instead:
                  >
                  >
                  >
                  > UPDATE m
                  > SET m.cust = y.cust
                  > FROM myDataBase.dbo.myTable m
                  >
                  > INNER JOIN YourDatabase.dbo.yourTable y
                  > ON m.field = y.field
                  > WHERE m.cust IS NULL
                  >
                  >
                  >
                  >
                  >
                  > Tim
                  >
                  >
                  >
                  > From: SQLQueriesNoCode@yahoogroups.com
                  > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of John Warner
                  > Sent: Thursday, July 16, 2009 2:35 PM
                  > To: SQLQueriesNoCode@yahoogroups.com
                  > Subject: [SQLQueriesNoCode] SQL Server 2005 UPDATE syntax ???
                  >
                  >
                  >
                  >
                  >
                  > I may be erroring on something else, but my question is does the UPDATE
                  > statement allow table aliasing?
                  >
                  > UPDATE myDataBase.dbo.myTable m
                  > INNER JOIN YourDatabase.dbo.yourTable y
                  > ON m.field = y.field
                  > SET m.cust = y.cust
                  > WHERE m.cust IS NULL
                  >
                  > John Warner
                  >
                  >
                  >
                  >
                  >
                  > [Non-text portions of this message have been removed]
                  >
                  >
                  >
                  > ------------------------------------
                  >
                  > Yahoo! Groups Links
                  >
                  >
                  >
                • peternilsson42
                  ... update mydatabase.dbo.mytable m set m.cust = (select y.cust from yourdatabase.dbo.yourtable y where y.field = m.field) where m.cust is null; This can fail
                  Message 8 of 12 , Jul 19, 2009
                  • 0 Attachment
                    "John Warner" <john@...> wrote:
                    >
                    > I may be erroring on something else, but my question is does
                    > the UPDATE statement allow table aliasing?
                    >
                    > UPDATE myDataBase.dbo.myTable m
                    > INNER JOIN YourDatabase.dbo.yourTable y
                    > ON m.field = y.field
                    > SET m.cust = y.cust
                    > WHERE m.cust IS NULL

                    update mydatabase.dbo.mytable m
                    set m.cust = (select y.cust
                    from yourdatabase.dbo.yourtable y
                    where y.field = m.field)
                    where m.cust is null;

                    This can fail if the select returns multiple rows. If it does,
                    then an update select will likely fail too.

                    --
                    Peter
                  • Babu A
                    Hi, Use like this, alias wont allow for the table, next to the update keyword, but you can give the alias in from clause Update myDataBase.dbo.myTableSet
                    Message 9 of 12 , Jul 19, 2009
                    • 0 Attachment
                      Hi,
                      Use like this,
                      alias wont allow for the table, next to the update keyword, but you can give the alias in from clause

                      Update myDataBase.dbo.myTableSet myDataBase.dbo.myTable.cust = y.custfrom YourDatabase.dbo.yourTable ywhere myDataBase.dbo.myTable.field = y.fieldand myDataBase.dbo.myTable.cust IS NULL

                      Hope it Helps,
                      Babu AMicrosoft Technology

                      --- On Mon, 20/7/09, peternilsson42 <peternilsson42@...> wrote:

                      From: peternilsson42 <peternilsson42@...>
                      Subject: [SQLQueriesNoCode] Re: SQL Server 2005 UPDATE syntax ???
                      To: SQLQueriesNoCode@yahoogroups.com
                      Date: Monday, 20 July, 2009, 7:33 AM













                       





                      "John Warner" <john@...> wrote:

                      >

                      > I may be erroring on something else, but my question is does

                      > the UPDATE statement allow table aliasing?

                      >

                      > UPDATE myDataBase.dbo. myTable m

                      > INNER JOIN YourDatabase. dbo.yourTable y

                      > ON m.field = y.field

                      > SET m.cust = y.cust

                      > WHERE m.cust IS NULL



                      update mydatabase.dbo. mytable m

                      set m.cust = (select y.cust

                      from yourdatabase. dbo.yourtable y

                      where y.field = m.field)

                      where m.cust is null;



                      This can fail if the select returns multiple rows. If it does,

                      then an update select will likely fail too.



                      --

                      Peter


































                      Love Cricket? Check out live scores, photos, video highlights and more. Click here http://cricket.yahoo.com

                      [Non-text portions of this message have been removed]
                    • Arnie Rowland
                      Actually, when using SQL Server, you can use the alias in the update AND the assignments. You only need to specify the table in the FROM or JOIN clause(s).
                      Message 10 of 12 , Jul 24, 2009
                      • 0 Attachment
                        Actually, when using SQL Server, you can use the alias in the update AND the assignments. You only need to specify the table in the FROM or JOIN clause(s).


                        UPDATE m
                        SET m.column2 = @Variable,
                        m.column3 = t.columnB
                        FROM MyDatabase.dbo.MyTable m
                        JOIN AnotherTable a
                        ON a.columnA = m.column1
                        WHERE m.Column4 = @Variable


                        Regards,

                        Arnie Rowland, MVP (SQL Server)

                        "Fortune favors the prepared mind." Louis Pasteur




                        -----Original Message-----
                        From: SQLQueriesNoCode@yahoogroups.com [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Babu A
                        Sent: Friday, July 24, 2009 12:20 PM
                        To: Arnie
                        Subject: Re: [SQLQueriesNoCode] Re: SQL Server 2005 UPDATE syntax ???

                        Hi,
                        Use like this,
                        alias wont allow for the table, next to the update keyword, but you can give the alias in from clause

                        Update myDataBase.dbo.myTableSet myDataBase.dbo.myTable.cust = y.custfrom YourDatabase.dbo.yourTable ywhere myDataBase.dbo.myTable.field = y.fieldand myDataBase.dbo.myTable.cust IS NULL

                        Hope it Helps,
                        Babu AMicrosoft Technology

                        --- On Mon, 20/7/09, peternilsson42 <peternilsson42@...> wrote:

                        From: peternilsson42 <peternilsson42@...>
                        Subject: [SQLQueriesNoCode] Re: SQL Server 2005 UPDATE syntax ???
                        To: SQLQueriesNoCode@yahoogroups.com
                        Date: Monday, 20 July, 2009, 7:33 AM



















                        "John Warner" <john@...> wrote:

                        >

                        > I may be erroring on something else, but my question is does

                        > the UPDATE statement allow table aliasing?

                        >

                        > UPDATE myDataBase.dbo. myTable m

                        > INNER JOIN YourDatabase. dbo.yourTable y

                        > ON m.field = y.field

                        > SET m.cust = y.cust

                        > WHERE m.cust IS NULL



                        update mydatabase.dbo. mytable m

                        set m.cust = (select y.cust

                        from yourdatabase. dbo.yourtable y

                        where y.field = m.field)

                        where m.cust is null;



                        This can fail if the select returns multiple rows. If it does,

                        then an update select will likely fail too.



                        --

                        Peter


































                        Love Cricket? Check out live scores, photos, video highlights and more. Click here http://cricket.yahoo.com

                        [Non-text portions of this message have been removed]



                        ------------------------------------

                        Yahoo! Groups Links







                        Disclaimer - July 24, 2009
                        This email and any files transmitted with it are confidential and intended solely for SQLQueriesNoCode@yahoogroups.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Westwood Consulting, Inc. Warning: Although Westwood Consulting, Inc has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments.
                        This disclaimer was added by Policy Patrol: http://www.policypatrol.com/
                      • John Warner
                        Actually T-SQL does support aliases in the UPDATE clause. Thanks for the assist though. John Warner ... give the ... more.
                        Message 11 of 12 , Jul 24, 2009
                        • 0 Attachment
                          Actually T-SQL does support aliases in the UPDATE clause. Thanks for the
                          assist though.

                          John Warner


                          > -----Original Message-----
                          > From: SQLQueriesNoCode@yahoogroups.com
                          > [mailto:SQLQueriesNoCode@yahoogroups.com] On Behalf Of Babu A
                          > Sent: Monday, July 20, 2009 1:07 AM
                          > To: SQLQueriesNoCode@yahoogroups.com
                          > Subject: Re: [SQLQueriesNoCode] Re: SQL Server 2005 UPDATE syntax ???
                          >
                          > Hi,
                          > Use like this,
                          > alias wont allow for the table, next to the update keyword, but you can
                          give the
                          > alias in from clause
                          >
                          > Update myDataBase.dbo.myTableSet myDataBase.dbo.myTable.cust =
                          > y.custfrom YourDatabase.dbo.yourTable ywhere
                          > myDataBase.dbo.myTable.field = y.fieldand myDataBase.dbo.myTable.cust IS
                          > NULL
                          >
                          > Hope it Helps,
                          > Babu AMicrosoft Technology
                          >
                          > --- On Mon, 20/7/09, peternilsson42 <peternilsson42@...> wrote:
                          >
                          > From: peternilsson42 <peternilsson42@...>
                          > Subject: [SQLQueriesNoCode] Re: SQL Server 2005 UPDATE syntax ???
                          > To: SQLQueriesNoCode@yahoogroups.com
                          > Date: Monday, 20 July, 2009, 7:33 AM
                          >
                          >
                          >
                          >
                          >
                          >
                          >
                          >
                          >
                          >
                          >
                          >
                          >
                          >
                          >
                          >
                          >
                          >
                          >
                          > "John Warner" <john@...> wrote:
                          >
                          > >
                          >
                          > > I may be erroring on something else, but my question is does
                          >
                          > > the UPDATE statement allow table aliasing?
                          >
                          > >
                          >
                          > > UPDATE myDataBase.dbo. myTable m
                          >
                          > > INNER JOIN YourDatabase. dbo.yourTable y
                          >
                          > > ON m.field = y.field
                          >
                          > > SET m.cust = y.cust
                          >
                          > > WHERE m.cust IS NULL
                          >
                          >
                          >
                          > update mydatabase.dbo. mytable m
                          >
                          > set m.cust = (select y.cust
                          >
                          > from yourdatabase. dbo.yourtable y
                          >
                          > where y.field = m.field)
                          >
                          > where m.cust is null;
                          >
                          >
                          >
                          > This can fail if the select returns multiple rows. If it does,
                          >
                          > then an update select will likely fail too.
                          >
                          >
                          >
                          > --
                          >
                          > Peter
                          >
                          >
                          >
                          >
                          >
                          >
                          >
                          >
                          >
                          >
                          >
                          >
                          >
                          >
                          >
                          >
                          >
                          >
                          >
                          >
                          >
                          >
                          >
                          >
                          >
                          >
                          >
                          >
                          >
                          >
                          >
                          >
                          >
                          >
                          > Love Cricket? Check out live scores, photos, video highlights and
                          more.
                          > Click here http://cricket.yahoo.com
                          >
                          > [Non-text portions of this message have been removed]
                          >
                          >
                          >
                          > ------------------------------------
                          >
                          > Yahoo! Groups Links
                          >
                          >
                          >
                        • Babu A
                          Good Suggestions Arnie Rowland RegardsBabu Ahttp://blog-mstechnology.blogspot.com/ ... From: Arnie Rowland Subject: RE: [SQLQueriesNoCode]
                          Message 12 of 12 , Jul 25, 2009
                          • 0 Attachment
                            Good Suggestions "Arnie Rowland"
                            RegardsBabu Ahttp://blog-mstechnology.blogspot.com/

                            --- On Sat, 25/7/09, Arnie Rowland <arnie@...> wrote:

                            From: Arnie Rowland <arnie@...>
                            Subject: RE: [SQLQueriesNoCode] Re: SQL Server 2005 UPDATE syntax ???
                            To: SQLQueriesNoCode@yahoogroups.com
                            Date: Saturday, 25 July, 2009, 12:56 AM













                             





                            Actually, when using SQL Server, you can use the alias in the update AND the assignments. You only need to specify the table in the FROM or JOIN clause(s).



                            UPDATE m

                            SET m.column2 = @Variable,

                            m.column3 = t.columnB

                            FROM MyDatabase.dbo. MyTable m

                            JOIN AnotherTable a

                            ON a.columnA = m.column1

                            WHERE m.Column4 = @Variable



                            Regards,



                            Arnie Rowland, MVP (SQL Server)



                            "Fortune favors the prepared mind." Louis Pasteur



                            -----Original Message-----

                            From: SQLQueriesNoCode@ yahoogroups. com [mailto:SQLQueriesNoCode@ yahoogroups. com] On Behalf Of Babu A

                            Sent: Friday, July 24, 2009 12:20 PM

                            To: Arnie

                            Subject: Re: [SQLQueriesNoCode] Re: SQL Server 2005 UPDATE syntax ???



                            Hi,

                            Use like this,

                            alias wont allow for the table, next to the update keyword, but you can give the alias in from clause



                            Update myDataBase.dbo. myTableSet myDataBase.dbo. myTable.cust = y.custfrom YourDatabase. dbo.yourTable ywhere myDataBase.dbo. myTable.field = y.fieldand myDataBase.dbo. myTable.cust IS NULL



                            Hope it Helps,

                            Babu AMicrosoft Technology



                            --- On Mon, 20/7/09, peternilsson42 <peternilsson42@ yahoo.com> wrote:



                            From: peternilsson42 <peternilsson42@ yahoo.com>

                            Subject: [SQLQueriesNoCode] Re: SQL Server 2005 UPDATE syntax ???

                            To: SQLQueriesNoCode@ yahoogroups. com

                            Date: Monday, 20 July, 2009, 7:33 AM



                            "John Warner" <john@...> wrote:



                            >



                            > I may be erroring on something else, but my question is does



                            > the UPDATE statement allow table aliasing?



                            >



                            > UPDATE myDataBase.dbo. myTable m



                            > INNER JOIN YourDatabase. dbo.yourTable y



                            > ON m.field = y.field



                            > SET m.cust = y.cust



                            > WHERE m.cust IS NULL



                            update mydatabase.dbo. mytable m



                            set m.cust = (select y.cust



                            from yourdatabase. dbo.yourtable y



                            where y.field = m.field)



                            where m.cust is null;



                            This can fail if the select returns multiple rows. If it does,



                            then an update select will likely fail too.



                            --



                            Peter































                            Love Cricket? Check out live scores, photos, video highlights and more. Click here http://cricket. yahoo.com



                            [Non-text portions of this message have been removed]



                            ------------ --------- --------- ------



                            Yahoo! Groups Links



                            Disclaimer - July 24, 2009

                            This email and any files transmitted with it are confidential and intended solely for SQLQueriesNoCode@ yahoogroups. com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Westwood Consulting, Inc. Warning: Although Westwood Consulting, Inc has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments.

                            This disclaimer was added by Policy Patrol: http://www.policypa trol.com/
































                            Love Cricket? Check out live scores, photos, video highlights and more. Click here http://cricket.yahoo.com

                            [Non-text portions of this message have been removed]
                          Your message has been successfully submitted and would be delivered to recipients shortly.