Delete Column

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • shieldsco
    New Member
    • Oct 2006
    • 6
    #1

    Delete Column

    I want to delete a colum in a table using the following sql statement:
    ALTER TABLE tblMarketing DROP COLUMN ThisYearMonthEn d10. In addition I only want to delete the column if it meets specific crietria. The crirtria is store in the table below. If the UdateMonth is not null then I want to delete.

    FinancilaPeriod Month UdateMonth
    ThisYearMonthEn d01 Jan
    ThisYearMonthEn d02 Feb
    ThisYearMonthEn d03 Mar
    ThisYearMonthEn d04 Apr
    ThisYearMonthEn d05 May
    ThisYearMonthEn d06 Jun
    ThisYearMonthEn d07 Jul
    ThisYearMonthEn d08 Aug
    ThisYearMonthEn d09 Sep
    ThisYearMonthEn d10 Oct Oct
    ThisYearMonthEn d11 Nov
    ThisYearMonthEn d12 Dec

    Can anyone help.

    Thanks
    Tags: None
    • shieldsco
      New Member
      • Oct 2006
      • 6
      #2
      Delete Column Using Specific Crietria

      I want to delete a colum in a table using the following sql statement:
      ALTER TABLE tblMarketing DROP COLUMN ThisYearMonthEn d10. In addition I only want to delete the column if it meets specific crietria. The crirtria is stored in the table below. If the UdateMonth is not null then I want to delete.

      FinancilaPeriod Month UdateMonth
      ThisYearMonthEn d01 Jan
      ThisYearMonthEn d02 Feb
      ThisYearMonthEn d03 Mar
      ThisYearMonthEn d04 Apr
      ThisYearMonthEn d05 May
      ThisYearMonthEn d06 Jun
      ThisYearMonthEn d07 Jul
      ThisYearMonthEn d08 Aug
      ThisYearMonthEn d09 Sep
      ThisYearMonthEn d10 Oct Oct
      ThisYearMonthEn d11 Nov
      ThisYearMonthEn d12 Dec

      Can anyone help.

      Thanks

        Comment

        • shieldsco
          New Member
          • Oct 2006
          • 6
          #3
          Delete Column Using Specific Crietria

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

          I want to delete a colum in a table using the following sql statement:
          ALTER TABLE tblMarketing DROP COLUMN ThisYearMonthEn d10. In addition I only want to delete the column if it meets specific crietria. The crirtria is stored in the table below. If the UdateMonth is not null then I want to delete.

          FinancilaPeriod Month UdateMonth
          ThisYearMonthEn d01 Jan
          ThisYearMonthEn d02 Feb
          ThisYearMonthEn d03 Mar
          ThisYearMonthEn d04 Apr
          ThisYearMonthEn d05 May
          ThisYearMonthEn d06 Jun
          ThisYearMonthEn d07 Jul
          ThisYearMonthEn d08 Aug
          ThisYearMonthEn d09 Sep
          ThisYearMonthEn d10 Oct Oct
          ThisYearMonthEn d11 Nov
          ThisYearMonthEn d12 Dec

          Can anyone help.

          Thanks

            Comment

            • shieldsco
              New Member
              • Oct 2006
              • 6
              #4
              Delete Column

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

              I want to delete a colum in a table using the following sql statement:
              ALTER TABLE tblMarketing DROP COLUMN ThisYearMonthEn d10. In addition I only want to delete the column if it meets specific crietria. The crirtria is stored in the table below. If the UdateMonth is not null then I want to delete.

              FinancilaPeriod Month UdateMonth
              ThisYearMonthEn d01 Jan
              ThisYearMonthEn d02 Feb
              ThisYearMonthEn d03 Mar
              ThisYearMonthEn d04 Apr
              ThisYearMonthEn d05 May
              ThisYearMonthEn d06 Jun
              ThisYearMonthEn d07 Jul
              ThisYearMonthEn d08 Aug
              ThisYearMonthEn d09 Sep
              ThisYearMonthEn d10 Oct Oct
              ThisYearMonthEn d11 Nov
              ThisYearMonthEn d12 Dec

              Can anyone help.

              Thanks

                Comment

                • ronverdonk
                  Recognized Expert Specialist
                  • Jul 2006
                  • 4259
                  #5
                  A table is nothing more then a collection of columns. If you want to remove a column you do it for the entire table. You cannot just drop a column for one specific row when it contains some data. When you drop a column, it removes that entire column from the table, data or not.

                  Ronald :cool:.

                    Comment

                    • Anu139
                      New Member
                      • Nov 2006
                      • 8
                      #6
                      Originally posted by shieldsco
                      --------------------------------------------------------------------------------

                      I want to delete a colum in a table using the following sql statement:
                      ALTER TABLE tblMarketing DROP COLUMN ThisYearMonthEn d10. In addition I only want to delete the column if it meets specific crietria. The crirtria is stored in the table below. If the UdateMonth is not null then I want to delete.

                      FinancilaPeriod Month UdateMonth
                      ThisYearMonthEn d01 Jan
                      ThisYearMonthEn d02 Feb
                      ThisYearMonthEn d03 Mar
                      ThisYearMonthEn d04 Apr
                      ThisYearMonthEn d05 May
                      ThisYearMonthEn d06 Jun
                      ThisYearMonthEn d07 Jul
                      ThisYearMonthEn d08 Aug
                      ThisYearMonthEn d09 Sep
                      ThisYearMonthEn d10 Oct Oct
                      ThisYearMonthEn d11 Nov
                      ThisYearMonthEn d12 Dec

                      Can anyone help.

                      Thanks
                      hi,can u please tell me, do u want to delete a column or a row? if u really want to delete a row whose FinancilaPeriod Month value is ThisYearMonthEn d10. u can use the following statement
                      delete from tblMarketing where FinancilaPeriod Month = 'ThisYearMonthE nd10'

                      I hope i understood ur problem correctly.

                        Comment

                        • shieldsco
                          New Member
                          • Oct 2006
                          • 6
                          #7
                          Originally posted by Anu139
                          hi,can u please tell me, do u want to delete a column or a row? if u really want to delete a row whose FinancilaPeriod Month value is ThisYearMonthEn d10. u can use the following statement
                          delete from tblMarketing where FinancilaPeriod Month = 'ThisYearMonthE nd10'

                          I hope i understood ur problem correctly.
                          I really want to delete a column based on the following example:
                          Table 1
                          FinancilaPeriod Month UdateMonth
                          ThisYearMonthEn d01 Jan
                          ThisYearMonthEn d02 Feb
                          ThisYearMonthEn d03 Mar
                          ThisYearMonthEn d04 Apr
                          ThisYearMonthEn d05 May
                          ThisYearMonthEn d06 Jun
                          ThisYearMonthEn d07 Jul
                          ThisYearMonthEn d08 Aug
                          ThisYearMonthEn d09 Sep
                          ThisYearMonthEn d10 Oct Oct
                          ThisYearMonthEn d11 Nov
                          ThisYearMonthEn d12 Dec



                          Table 2
                          ThisYearMonthEn d01 ThisYearMonthEn d02 ThisYearMonthEn d10
                          I want to delete the cloumn ThisYearMonthEn d10 in Table 2 based on the the UdateMonth in table 1.

                            Comment

                            • Banfa
                              Recognized Expert Expert
                              • Feb 2006
                              • 9067
                              #8
                              Hi shieldsco,

                              I hope you are getting the help you want. However I would like to request that in future you don't double (or in this case quadruple) post the same question to the same forum.

                              If you think you question/problem has been overlooked then please post a reply to it yourself so that it gets bumped to the top of the Forum list.

                              I have now merged your 4 threads on this subject into this single thread.

                              Regards
                              Banfa

                                Comment

                                • shieldsco
                                  New Member
                                  • Oct 2006
                                  • 6
                                  #9
                                  Originally posted by Banfa
                                  Hi shieldsco,

                                  I hope you are getting the help you want. However I would like to request that in future you don't double (or in this case quadruple) post the same question to the same forum.

                                  If you think you question/problem has been overlooked then please post a reply to it yourself so that it gets bumped to the top of the Forum list.

                                  I have now merged your 4 threads on this subject into this single thread.

                                  Regards
                                  Banfa
                                  Actually two of threads were futher explanation of the problem.

                                    Comment

                                    • athir13en
                                      New Member
                                      • Feb 2008
                                      • 2
                                      #10
                                      i think u can try to Select from Table1 1st then put it in a String, after that only you drop the column.

                                      sample table
                                      Table1
                                      FinancilaPeriod Month UdateMonth
                                      ...
                                      ...
                                      ThisYearMonthEn d09 Sep
                                      ThisYearMonthEn d10 Oct Oct
                                      ThisYearMonthEn d11 Nov
                                      ThisYearMonthEn d12 Dec

                                      Table2
                                      ThisYearMonthEn d01 ThisYearMonthEn d02 ThisYearMonthEn d10
                                      this is sample code using VB
                                      Code:
                                      Dim strSQL As String = ""
        Dim strColumnToDrop As String = ""

        strConnection = 'your connection string
        sqlConn = New SqlConnection(strConnection)

        'Select Data From Table1
        Dim command1 As New SqlCommand("Select FinancilaPeriodMonth From Table1 Where UdateMonth = 'Oct Oct'", sqlConn)
        sqlConn.Open()
        Dim reader As SqlDataReader = command1.ExecuteReader
        If reader.Read Then
            strColumnToDrop = Trim(reader("FinancilaPeriodMonth").ToString())
        End If
        sqlConn.Close()

        'drop column
        strSQL = "Alter Table Table2 Drop Column " & strColumnToDrop
        Dim command2 As New SqlCommand(strSQL, sqlConn)
        sqlConn.Open()
        command2.ExecuteNonQuery()
        sqlConn.Close()

                                        Comment

                                        Previous template Next
                                        Working...