473,698 Members | 2,392 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 51557
"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
8580
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
13927
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
33380
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
9468
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
8882
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
2462
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
13781
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
3802
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
12908
by: Joseph | last post by:
Can i drop a cloumn from a table in DB2 for Z/OS?
0
8678
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
8609
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
1
8899
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
7737
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
6525
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
5861
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();...
0
4621
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
2333
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2007
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.