Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

Is it possible to set a value for all fields in a column, e.g.:

ID Host    URI
1  //cyrus /images    
2  //cyrus /videos
3  //cyrus /text
4  //cyrus /misc
5  //cyrus /backup

I want be able to change all the Host column entries -which are all alike- at one place. This place can be a GUI like the Ocracle SQL Developer or Queries, I don't care as long as I have to change only one datafield that changes all Host fields.

share|improve this question

1 Answer 1

up vote 2 down vote accepted

It sounds like you could do something like

UPDATE your_table_name
   SET host = '//new_host'
 WHERE host = '//cyrus'

to update all the rows in the table. Storing the same data in multiple rows of a table, however, violates the basic principle of normalization. You would almost always want to modify the schema design so that there is a separate table for the HOST and a foreign key from your table to the host table. Something like

CREATE TABLE host (
  host_id   NUMBER PRIMARY KEY,
  host_name VARCHAR2(50)
);

CREATE TABLE your_table_name (
  your_id   NUMBER PRIMARY KEY,
  host_id   NUMBER REFERENCES host( host_id ),
  uri       VARCHAR2(100)
);

If you did that, you'd only need to update one row in the HOST table to update the name of the host for all the rows in your table.

share|improve this answer
    
Great, I tried a foreign key relation b4, but maybe got the constraints wrong the first time - Ok so far so good, after that I do INSERT INTO "myDB"."HOST" (HOST_ID, HOST_NAME) VALUES ('1', 'TILSCHWEIGER'), and now I should enter a "1" into all HOST_ID fields of "your_table_name" ? At the moment there's still (null) there. –  Achmed Durangi Jun 17 '11 at 14:09
    
@Stephan - Correct, you'd want to insert a 1 into the HOST_ID column in YOUR_TABLE_NAME. You'd want to insert the number 1 rather than the string '1' if you declared HOST_ID as a number-- you always want to insert strings into string columns, numbers into number columns, and dates into date columns. –  Justin Cave Jun 17 '11 at 14:38
    
You're correct once again, I used parentheses and inserted strings. Would you happen to know how to retreive a 'hostname' value in relation to 'host_id' from the 'your_table_name' table by using a DISTINCT statement? ... by the way, if one would want to avoid foreign keys, could I use joint table or is that considered bad practice? –  Achmed Durangi Jun 17 '11 at 17:07

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.