Home » SQL & PL/SQL » SQL & PL/SQL » SQL Query
SQL Query [message #215059] Fri, 19 January 2007 03:10 Go to next message
romi
Messages: 67
Registered: October 2006
Member


Hello all,

I have a table named details having 2 column name and age.
I have data like,'kid',24 and 'kid',25
then i want to insert
this value like 'kid',24,25 and if name r not same then i want
to insert name and age like 'john',30.

Is it possible with sql query.I need only sql query not pl-sql.


thanks
Re: SQL Query [message #215063 is a reply to message #215059] Fri, 19 January 2007 03:18 Go to previous messageGo to next message
ab_trivedi
Messages: 460
Registered: August 2006
Location: Pune, India
Senior Member
Hi,
But you have told that you have only two columns and you want to INSERT three values?
Bye
Ashu
Re: SQL Query [message #215064 is a reply to message #215059] Fri, 19 January 2007 03:18 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
use merge for that. It works like a when exists, then update, else insert.
Re: SQL Query [message #215065 is a reply to message #215064] Fri, 19 January 2007 03:20 Go to previous messageGo to next message
ab_trivedi
Messages: 460
Registered: August 2006
Location: Pune, India
Senior Member
Hi Frank,
but he want to insert the three values in same table ... where only two columns are there?
Ashu
Re: SQL Query [message #215068 is a reply to message #215065] Fri, 19 January 2007 03:25 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You and I don't know the datatype of the age column. It could be he wants a comma-separated string of ages (baaaad idea)
Re: SQL Query [message #215070 is a reply to message #215068] Fri, 19 January 2007 03:27 Go to previous messageGo to next message
ab_trivedi
Messages: 460
Registered: August 2006
Location: Pune, India
Senior Member
yes I agree, it is a baaaaaad IDEA ... hi hi hi, then he will ask how to seperate the age(remove commas)
Ashu

[Updated on: Fri, 19 January 2007 03:28]

Report message to a moderator

Re: SQL Query [message #215074 is a reply to message #215059] Fri, 19 January 2007 03:51 Go to previous messageGo to next message
romi
Messages: 67
Registered: October 2006
Member


Sorry, I want that in name column it should go 'kid' and in age
column both value should go 24,25 both.and if name are not
equal then it should go 'kid' in nam column and only 1 value 26
should go in age column.
Re: SQL Query [message #215087 is a reply to message #215074] Fri, 19 January 2007 04:33 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
What datatype is your age-column?
Remember that storing 24, 25 is TOTALLY different from storing two records for kid, one containing 24 and one with 25.
The first method makes it difficult to list eacht individual age.
Re: SQL Query [message #215208 is a reply to message #215059] Fri, 19 January 2007 13:20 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
actually the way to do this is with a child table. Your example is very simple and doesn't require a child table, but if it was an expanded example, it would be something like this.


CREATE TABLE NAMES (USER_NAME VARCHAR2(30) NOT NULL);

ALTER TABLE NAMES ADD CONSTRAINT NAMES_PK PRIMARY KEY (USER_NAME);

CREATE TABLE AGES (USER_NAME VARCHAR2(30) NOT NULL,
AGE NUMBER(3) NOT NULL);

CREATE UNIQUE INDEX AGES_U1 ON AGES(USER_NAME,AGE);

ALTER TABLE AGES ADD CONSTRAINT AGES_F1
FOREIGN KEY (USER_NAME)
REFERENCES NAMES (USER_NAME)
ON DELETE CASCADE
ENABLE;

You would insert the name into names only one and have as many ages as you want in the ages table for a particular name,

[Updated on: Fri, 19 January 2007 13:22]

Report message to a moderator

Re: SQL Query [message #215243 is a reply to message #215059] Sat, 20 January 2007 02:00 Go to previous message
romi
Messages: 67
Registered: October 2006
Member


thanks bill b,

but i am unable to do it.can u give me one insert example
with it, which u have given before.
Previous Topic: cursor
Next Topic: Query Help
Goto Forum:
  


Current Time: Mon Dec 05 23:51:13 CST 2016

Total time taken to generate the page: 0.08767 seconds