Table and Field update

[slimjim]

Have a question

I have a table and it has fields in it i dont want to lose the data in them.
Now i want to add some more fields to that table and also insert info into those new fields.

And do that all without losing old data.

How would i make that table and inserts.
below is new table. Now old one didn't have last 5 fields.
But in those last 5 fields i need to add 0.000 in them too

create table members_credits(
            id int(11) not null auto_increment,
            user_id integer,
            credits_num float(14,3),
            cred_lv_1 float(14,3) default NULL,
            cred_lv_2 float(14,3) default NULL,
            cred_lv_3 float(14,3) default NULL,
            cred_lv_4 float(14,3) default NULL,
            cred_lv_5 float(14,3) default NULL,
			primary key(id)
);

I also have me another problem.
I have made a table called referrals which is below. From another table in the db i need to insert each id,userid from that table into this new table and then in field referrer i need to add a 0 in each.
How ?

CREATE TABLE referrals (
            id int(11) NOT NULL auto_increment,
            user int(11) default NULL,
            referrer int(11) default NULL,
            PRIMARY KEY  (id)
);

[cosminb]

i think you'll find more help on that matter at
www.mysql.com

[jimson]

to add more fields to already builded table, use
ALTER TABLE syntax

6.5.4 ALTER TABLE Syntax

ALTER [IGNORE] TABLE tbl_name alter_spec [, alter_spec ...]

alter_specification:
ADD [COLUMN] create_definition [FIRST | AFTER column_name ]
or ADD [COLUMN] (create_definition, create_definition,...)
or ADD INDEX [index_name] (index_col_name,...)
or ADD PRIMARY KEY (index_col_name,...)
or ADD UNIQUE [index_name] (index_col_name,...)
or ADD FULLTEXT [index_name] (index_col_name,...)
or ADD [CONSTRAINT symbol] FOREIGN KEY [index_name] (index_col_name,...)
[reference_definition]
or ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
or CHANGE [COLUMN] old_col_name create_definition
[FIRST | AFTER column_name]
or MODIFY [COLUMN] create_definition [FIRST | AFTER column_name]
or DROP [COLUMN] col_name
or DROP PRIMARY KEY
or DROP INDEX index_name
or DISABLE KEYS
or ENABLE KEYS
or RENAME [TO] new_tbl_name
or ORDER BY col
or table_options

u never read mysql manual... right!

[spstieng]

For the first part use Alter table as Jimson says.
For the second part, you have to use concatination (if I have understood you correctly).

Not sure for MySQL but for Oracle, you concatinate using || (double lines) syntax.

So in pseudocode it would be like this:

insert into {table} values (
Select {whatever}
from {table}
where {some condition} (if u need a condition)
concatinate || "0"
);

um... it's been a while since I did any SQL 🙂