Updating A Database's Tables

[nightfox1405241487 0]

Is there an "easy" way to update a database's tables? Like for instance, I have my own custom-coded member login system. Whenever I add a new feature that needs a database, I manually have to download, update and upload the database. Is there an easy way I can do this?THANKS!!![N]F

[yordan 10]

Usually, it's rather easy to update a table in a database.The syntax is something like "insert into MYTABLE values SOMETHING, SOMETHING_ELSE, ..."Was this your exact question ?

[Hercco 0]

What Yordan posted was inserting a record to the database and I don't think Nightfox is after that. You are looking to change the tables structure right? Updating in database world usually means updating a single record which is done with SQL command beginning with UPDATE keyword or command or whatever. You can do all possible operations to a database with queries. Sometimes certain features might be limited (for example dropping databases might not be allowed from PHP scripts and so on) but usually you should be able to change the structure. Well provided that the user has create rights.Basically how you do it is use ALTER TABLE query. For example you can add column with query like this "ALTER TABLE tablename ADD column type". Naturally the syntax is exactly the same as for creating tables so you can everything here you can do there. You can drop columns with "ALTER TABLE tablename DROP COLUMN columnname". It is also possible to rename and change datatypes for columns, but unless I'm completely mistaken the syntax varies from DBMS to DBMS. MySQL uses CHANGE "ALTER TABLE tablename CHANGE oldname newname type". Notice that you can change the type here. To just change the type keep the name same. I have a vague memory that Oracle uses RENAME... I could be wrong though.I hope this answers your question.

[yordan 10]

Nicely answered, Hercco.I must confess that i was confused by the topic title, I understood the " Updating A Database's Tables" by updating some rows in a table, which is different from insert but thas nothing to do with the ALTER TABLE notion.Sorry again Yordan

Edited June 1, 2006 by yordan (see edit history)

[nightfox1405241487 0]

What Yordan posted was inserting a record to the database and I don't think Nightfox is after that.
You are looking to change the tables structure right? Updating in database world usually means updating a single record which is done with SQL command beginning with UPDATE keyword or command or whatever.

Thanks!! I'll look into all of that. It does look like what I'm after. I use MySQL, not Oracle. But thanks again!!

[N]F

[Chesso 0]

I'm pretty sure most if not all database software accepts pretty much the exact same syntax or close to it. I would say UPDATE is what you are looking for, it basically let's you change a field in a table to my knowledge to say update a int value incrementaly and such.INSERT would allow you to insert a new field in the table or record either way, by id or if the table has an auto_incremental primary key then it would do it automatically.I don't know of ALTER though? it sounds like it does the same thing as UPDATE? (Either way updating or altering your'e changing the original so what would be the difference between the two? there shouldn't be....)

[miCRoSCoPiC^eaRthLinG 0]

Yep - the INSERT and UPDATE syntax is almost same in any SQL Compliant Database...

 

Chesso: UPDATE and ALTER have diametrically opposite functions - though both names suggest some sort of updating mechanism... UPDATE is used to modify the DATA stored inside a table, whereas ALTER is used to modify the STRUCTURE of the table itself.

 

Your usual UPDATE syntax (for MySQL) would be:

UPDATE tableName SET Field1 = 'newvalue1', Field2 = 'newvalue2', ...;

 

ALTER, as shown by Hercco allows you to modify the Column names as well as their data types in a particular table... Say in the above table, Field1 was VARCHAR and Field2 was DOUBLE... I want to swap the data types and change the name of Field2 to Field3

ALTER TABLE tableName CHANGE Field1 Field1 DOUBLE;
ALTER TABLE tableName CHANGE Field2 Field3 VARCHAR(x);

[Chesso 0]

Ahhh so one changes the data stored and the other just changes the type of data stored.

[yordan 10]

Yes, in all SQL RDBMS the syntax is very similar.CREATE TABLE to create a tableALTER TABLE to change the table structureINSERT INTO table to add rowsetc...Very few differences between Oracle, Ingres, Informix, Mysql, PostGre.Of course, things are easier if you use integrated scripts like the ones in cpanel, but sometimes you have to understand basics, or at least know the terminology.

Edited June 4, 2006 by yordan (see edit history)

[vhortex 1]

Thanks!! I'll look into all of that. It does look like what I'm after. I use MySQL, not Oracle. But thanks again!!
[N]F

Oracle's SQL is the same as mySQL with just some addons and optiomations..
on the other hand, for a small scale bussiness which need an Oracle functionality,
mySQL 5 is a nice thing to start with..

As long as it uses SQL and a compliant, the database controls are the same..

[Hercco 0]

My understanding is that all SQL databases accept ALTER TABLE syntax but Oracle (and possibly others) have additional RENAME command. And I doubt no one would ask about UPDATE queries in a forum like this... I reckon that is one of the first things you learn when you get into relational databases. Which the threads title in itself is okay, database people just understand in comepletely different way.

[surfermac 0]

Use triggers to update the recordsit works best for all the records and automatically updates on completing one recordso go for triggers