473,883 Members | 1,643 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

How can I drop a Column from a table?

I tried this:

ALTER TABLE Dokumente
DROP COLUMN docPrioID

but I get this errormessage:
DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: COLUMN;ABLE
Dokumente
DROP;CONSTRAINT

docPrioID was a foreign key to another table but I dropped them (the FK
and the table)
--> Get name with SELECT constname FROM syscat.referenc es and DROP it
from Dokumente

Now syscat.referenc es doesn't show constraints for this table.

Does anybody know how I can DROP the column docPrioID?

kind regards

Markus

Jan 10 '06 #1
5 51742
"Markus" <Th*********@Fr eenet.de> wrote in message
news:11******** **************@ z14g2000cwz.goo glegroups.com.. .
I tried this:

ALTER TABLE Dokumente
DROP COLUMN docPrioID

but I get this errormessage:
DB2 SQL error: SQLCODE: -104, SQLSTATE: 42601, SQLERRMC: COLUMN;ABLE
Dokumente
DROP;CONSTRAINT

docPrioID was a foreign key to another table but I dropped them (the FK
and the table)
--> Get name with SELECT constname FROM syscat.referenc es and DROP it
from Dokumente

Now syscat.referenc es doesn't show constraints for this table.

Does anybody know how I can DROP the column docPrioID?

kind regards

Markus


There is no native SQL command to drop a column from a table in DB2. Please
consult the SQL Reference Vol 2 for more information on alter table.

You can use the Control Center to drop a column, because it will generate
commands to export the data, drop the table, recreate the table without the
dropped column, and re-load the data.
Jan 10 '06 #2
Unfortunately I access the DB with a third-party tool and have no
access to the server so I want to do a workaround since somebody can
drop this column.

For this reason I dropped the FK and the tables as mentioned before and
now I want to set a default-value for this column.

Can you explain, what I have to do (datatype of the column is integer).

I tried
ALTER TABLE dokumente ALTER COLUMN docPrioID SET DATA TYPE INTEGER WITH
DEFAULT -1
but it seems, that this statement doesn't work. :-)

kind regards

Markus

Jan 10 '06 #3
One second after my posting I thougt: What happens if I try ALTER
TABLE dokumente ALTER COLUMN docPrioID SET WITH DEFAULT -1?

--> I tried it and it works. :-)

Markus

Jan 10 '06 #4
Markus wrote:
One second after my posting I thougt: What happens if I try ALTER
TABLE dokumente ALTER COLUMN docPrioID SET WITH DEFAULT -1?

--> I tried it and it works. :-)

Praise to the inventor of the syntax diagram! :-)

--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab
Jan 10 '06 #5
FYI:

offline drop column works with Beta DB2 version 9. GA is expected this
year.

ALTER TABLE Dokumente
DROP COLUMN docPrioID;
REORG TABLE Dokumente;

-- Artur Wronski

Jan 10 '06 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
8621
by: Richard Galli | last post by:
I want viewers to compare state laws on a single subject. Imagine a three-column table with a drop-down box on the top. A viewer selects a state from the list, and that state's text fills the column below. The viewer can select states from the drop down lists above the other two columns as well. If the viewer selects only one, only one column fills. If the viewer selects two states, two columns fill. Etc. I could, if appropriate, have...
1
13933
by: Marion Jakob | last post by:
hi i'm using mssql server 2000 i want to remove a not null column constraint from the column answertext in table answertext. i tried the following line ALTER TABLE AnswerText ALTER COLUMN AnswerText DROP NOT NULL;
2
33387
by: James Knowlton | last post by:
Hello, I'm just returning to MS SQL Server after two years of dealing with Sybase ASE. I need to drop a column, using the alter table command. I keep getting an error indicating that a constraint is using the column. Here is the create script for the table create table mytable (col1 char(1) not null, col2 char(1) default 'A' not null )
2
9479
by: kmnotes04 | last post by:
Is it possible to link one drop-down box to another? For example, if a name is chosen from a drop-down list, can another drop-down list then automatically display the person's office as a result of the choice made in the first drop-down list? If so, how can that be done? I am working with 'Teach Yourself Microsoft Access' and I didn't see it discussed in that book (if it's even possible to do). Or would I just have to create linked tables...
1
8898
by: Igor Kryltsov | last post by:
Hi, I have table: # \d category; category_id | integer | not null default nextval('public.category_category_id_seq'::text) category_name | character varying(100) | not null Indexes: category_pkey primary key btree (category_id)
1
2471
by: Thomas Chille | last post by:
Hello! I have a Problem. A table with a ForeignKeyRef exits. The Name of the field with this FK-Constraint is 'id_employee' because it is referencing the field 'id_employee' in 'public.sd_employee'. I drop this column of the table with cascade in a script: ALTER TABLE public.sd_messaging_participant DROP COLUMN id_employee CASCADE;
5
13831
by: Romulo NF | last post by:
Greetings, I´m back here to show the new version of the drag & drop table columns (original script ). I´ve found some issues with the old script, specially when trying to use 2 tables with drag&drop on the same page (which was not possible). Now i´ve a new concept of the script, more object oriented. I´ve also commented the whole code so you guys can easier understand it engine. What exactly we need when trying to make a column drag &...
1
3809
by: Gladiator | last post by:
Hi Team , Can some one tell me why we dont we have a DROP column or rename a column command in DB2 ? I Have a very Huge table and need to rename a column . The only option i have to do to rename a column which i by mistake spelled it wrong was to drop the Whole Table ???? Thanks in Advance , Kamal.
4
12930
by: Joseph | last post by:
Can i drop a cloumn from a table in DB2 for Z/OS?
0
9940
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
11141
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10847
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10415
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9573
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7971
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7126
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
1
4611
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
3232
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.