Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
| Search | Today's Posts | Mark Forums Read
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old April 23rd, 2005, 08:16 AM
Authorized User
 
Join Date: Aug 2004
Location: Jasenie, , Slovakia.
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default Add an attribute to existing table

I am supposed to write an query which will add new attribute to my already existing table
So I written this

ALTER TABLE member
ADD e_mail CHAR(20)

which is fine, however when I try insert required data for each row I get stuck

INSERT INTO member(e_mail) VALUES('johnwhite@msn.com');

Can anyone help me please....

P.T.
__________________
P.T.
 
Old April 24th, 2005, 03:01 AM
Imar's Avatar
Wrox Author
Points: 70,322, Level: 100
Points: 70,322, Level: 100 Points: 70,322, Level: 100 Points: 70,322, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

What error do you get? Can you see the new column in the program you manage your tables with? (E.g. Enterprise Manager for Sql Server).

How do your other columns look like? Are you supposed to supply required information for those columns?

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
 
Old April 24th, 2005, 05:25 AM
Authorized User
 
Join Date: Aug 2004
Location: Jasenie, , Slovakia.
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Table before adding new attribute
Code:
SQL> SELECT * FROM member;

MEMBE NAME                 ADDRESS                        TELEPHONE_NO STATUS
----- -------------------- ------------------------------ ------------ --------------------
A2345 John White           81 George Street,              020-84561122 Staff
                           London

R3456 Julie Lee            63 Ashby Street,               0121-8221024 Researcher
                           Edinburgh

S4567 David Ford           2 Elm Place, London            020-75511003 Student
S7654 Mary Howe            28 Malvern Street,             01322-451399 Student
                           Dartford
then I call my script
Code:
ALTER TABLE member
ADD e_mail CHAR(20);
Table adjusted as you see bellow
Code:
SQL> SELECT * FROM member;

MEMBE NAME                 ADDRESS                        TELEPHONE_NO STATUS               E_MAIL
----- -------------------- ------------------------------ ------------ -------------------- --------------------
A2345 John White           81 George Street,              020-84561122 Staff
                           London

R3456 Julie Lee            63 Ashby Street,               0121-8221024 Researcher
                           Edinburgh

S4567 David Ford           2 Elm Place, London            020-75511003 Student
S7654 Mary Howe            28 Malvern Street,             01322-451399 Student
                           Dartford
but when I add next line to fill new attribute with data

Code:
ALTER TABLE member
ADD e_mail CHAR(20)

INSERT INTO member(e_mail) VALUES('johnwhite@msn.com');
I will get following error

Code:
SQL> @Q13.sql
Input truncated to 55 characters
INSERT INTO member(e_mail) VALUES('johnwhite@msn.com')
            *
ERROR at line 1:
ORA-01400: cannot insert NULL into ("W0411758"."MEMBER"."MEMBER_NO")
What it is trying to tell me???
w0411758 is my student ID, MEMBER is name of the table but why is there MEMBER_NO when I asked to insert new value into the E_MAIL????

P.T.
 
Old April 24th, 2005, 05:30 AM
Imar's Avatar
Wrox Author
Points: 70,322, Level: 100
Points: 70,322, Level: 100 Points: 70,322, Level: 100 Points: 70,322, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

Quote:
quote:but why is there MEMBER_NO when I asked to insert new value into the E_MAIL????
I don't know, really. Maybe there is an additional column called MEMBER_NO that you don't know off?

If that column is there, and it's a required column, then the error message makes sense. When you insert a new record using INSERT then you'll need to provide values for all required columns (unless they have a default value).....

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
 
Old April 24th, 2005, 05:37 AM
Authorized User
 
Join Date: Aug 2004
Location: Jasenie, , Slovakia.
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default

As you see MEMBER_NO is primary key
Code:
CREATE TABLE member (
member_no        CHAR(5)     NOT NULL,
name            CHAR(20)    NOT NULL,
address            CHAR(30)    NOT NULL,
telephone_no        VARCHAR2(12),
status            CHAR(20),
PRIMARY KEY (member_no));
and default how do I set up them?

I went for some tutorials but all what they show is add new attribute but they they do not provide example for adding data for this attribute...

P.T.
 
Old April 24th, 2005, 05:46 AM
Imar's Avatar
Wrox Author
Points: 70,322, Level: 100
Points: 70,322, Level: 100 Points: 70,322, Level: 100 Points: 70,322, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

In that case, the error message makes sense, don't you think?? You *have* to provide a value for columns that have NOT NULL because that makes them required.

So, instead of specifying the email address, specify all required columns:

INSERT INTO member (member_no, name, address, Other Columns) (VALUES YourValuesGoHere)

How you setup defaults depend on the database you're using. For Sql server you can set them in the Properties grid for a selected column, or through CREATE code. Look in the help for info. For a CHAR(5) that's also the primary key, a default won't do you any good (unless you call some function that generates the key). I think you'll need to somehow generate a valid and unique 5 character member_no for each record you try to insert.

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
While typing this post, I was listening to: Can't Stop Now by Keane (Track 8 from the album: Hopes and Fears) What's This?
 
Old April 24th, 2005, 05:52 AM
Authorized User
 
Join Date: Aug 2004
Location: Jasenie, , Slovakia.
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default

All is "funny", because this is only table which has all data in expect that silly e_mail which I am supposed to add. I was trying to find out how to fill this attribute without touching rest of table.
I know it I can overwrite whole table and I just try to be little bit more smart than rest of student.

But as you pointed out I have to provide data because is NULL, is this also necessary if this data already exist???
P.T.
 
Old April 24th, 2005, 06:00 AM
Imar's Avatar
Wrox Author
Points: 70,322, Level: 100
Points: 70,322, Level: 100 Points: 70,322, Level: 100 Points: 70,322, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

Aha, in that case, use UPDATE instead of INSERT. As it name implies, UPDATE will update existing records, whereas INSERT always creates a brand new record.

UPDATE Member SET Email = 'SomeEmailAddress'

Note: this will update ALL records in the Member table and change the email address. If you want to limit the update to one record, add a WHERE clause:

UPDATE Member SET Email = 'SomeEmailAddress' WHERE Member_No = 'ABCDE'


HtH,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
While typing this post, I was listening to: This Is The Last Time by Keane (Track 2 from the album: Hopes and Fears) What's This?
 
Old April 24th, 2005, 06:05 AM
Authorized User
 
Join Date: Aug 2004
Location: Jasenie, , Slovakia.
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default

That's make sense!!!!!!!!!
I did not try to use this because was badly explain by my tutor.
What I mean he just updated already existing for example change telephone number.

Thank you very much:D and have nice day

P.T.
 
Old April 24th, 2005, 01:35 PM
Authorized User
 
Join Date: Aug 2004
Location: Jasenie, , Slovakia.
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Yes on end of day it is working beautifully!!!!!!!
And for anybody with similar problem here is my code, I don't say is perfect but does what I need it to do with it
Code:
ALTER TABLE member
ADD e_mail CHAR(20);

UPDATE member SET e_mail='johnwhite@msn.com' WHERE member_no='A2345';
UPDATE member SET e_mail='leeJulie@aol.com' WHERE member_no='R3456';
UPDATE member SET e_mail='d_ford@yahoo.com' WHERE member_no='S4567';
UPDATE member SET e_mail='maryhowe@aol.co.uk' WHERE member_no='S7654';
UPDATE member SET e_mail='mrobinson@msn.com' WHERE member_no='A7412';
UPDATE member SET e_mail='ahmed_h@msn.co.uk' WHERE member_no='B6421';
UPDATE member SET e_mail='AnnMat@f2s.com' WHERE member_no='A1246';
UPDATE member SET e_mail='amohammady@box.com' WHERE member_no='R8916';
UPDATE member SET e_mail='fairyjack@aol.com' WHERE member_no='S1248';
P.T.




Similar Threads
Thread Thread Starter Forum Replies Last Post
dynamically add a new row to a existing GridView srkvellanki ASP.NET 2.0 Professional 1 September 21st, 2008 09:15 PM
Add a new record between two existing records hsncompany BOOK: Beginning Access 2003 VBA 0 April 13th, 2008 03:36 PM
How to add fields to an existing table using VBA donrafeal Access VBA 3 March 22nd, 2006 06:11 PM
Reference to existing attribute value shumba XML 1 March 15th, 2006 02:53 PM
javascript - add element to existing xml? willgotoo XML 4 October 8th, 2004 09:33 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.