Member Avatar for peter_budo

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('');

Can anyone help me please....

  • 2 Contributors
  • 3 Replies
  • 1K Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by peter_budo

Dani AI

Generated

A short expert note tying the thread together and offering safer, more scalable options.

As discovered and pointed out, the common confusion here is between creating a column and changing values that already exist. INSERT adds new rows; existing rows are modified with UPDATE or by joining to a mapping source. A few practical, low-risk recommendations follow.

  • Schema: prefer VARCHAR (not a fixed CHAR) for addresses and pick a generous length (many use VARCHAR(255)). Use utf8mb4 charset if the database stores international text. Allow NULL or set a sensible default if some members have no email. Consider a UNIQUE index only if business rules require unique addresses, or keep emails in a separate contact table if multiple addresses per member are possible.

  • Bulk population patterns (avoid issuing many single-row UPDATEs): use a single UPDATE with CASE for a small list, or load a mapping table and run a single UPDATE ... JOIN for larger sets. Example (placeholders shown):

UPDATE member
SET email = CASE member_no
  WHEN 'M1001' THEN 'alice@example.com'
  WHEN 'M1002' THEN 'bob@example.com'
  ELSE email
END
WHERE member_no IN ('M1001','M1002');

Or using a mapping table:

CREATE TEMPORARY TABLE tmp_map(member_no VARCHAR(20) PRIMARY KEY, email VARCHAR(255));
-- populate tmp_map, then:
UPDATE member m
JOIN tmp_map t ON m.member_no = t.member_no
SET m.email = t.email;
  • Safeguards: run schema changes on a test copy first, back up production, wrap updates in transactions when using InnoDB, and perform large updates in small batches or during maintenance windows to avoid long locks. GUI tools (TOAD, MySQL Workbench, phpMyAdmin) help with small edits, but scripted updates are safer and repeatable.

Recommended Answers

All 3 Replies

Member Avatar for peter_budo

After all I found my lost way trough the darknest and here is my final 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';

Hope I will pass my module :lol:

Member Avatar for haria_kishore

Dear friend

The only way you can insert data (or in other words update ur table with new data) is by using UPDATE command followed by some condition.

And have u used TOAD( a software for viewing sql data in a neat format unlike SQL* plus).I think that will make ur life a bit easy as well for updating large volumes of data in near future

Member Avatar for peter_budo

Thanks for interest.
As you can see this was part of my coursework. I must admit that to learn SQL was interesting experience however the course hasn't got appropriate backup. What I mean, our tutor answers been often confusing, not to much in notes and tutorials did not provide enought experience, for example to insert data in new attribute. Only think close to this for us was update already existing data. In the proces of learning it logicly come to me to do it exactly as populating table with INSERT INTO.
Now I know, I was wrong.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.