Home » SQL & PL/SQL » SQL & PL/SQL » Clarification in 'Insert into' statement (merged)
Clarification in 'Insert into' statement (merged) [message #400734] Wed, 29 April 2009 07:01 Go to next message
ramsk0408
Messages: 9
Registered: April 2009
Junior Member
Hi,

Please clarify my below doubt in Insert into statement in SQL,

Is tat possible to use the column 1 value as the last column value in the same insert statment?

Is the following scenario is a valid one?

Third column in the insert query should have the first character from the first column value being inserted and the first 5 character from the second column value which is also being inserted with the third column.

Please lemme know whether the scenario is valid and if it is so, how can this be achieved?

Thanks in Advance. Smile


Regards,
Ram
Re: Query in 'insert into ' statement [message #400738 is a reply to message #400734] Wed, 29 April 2009 07:05 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If I'm not wrong, SUBSTR might help.
Re: Query in 'insert into ' statement [message #400749 is a reply to message #400734] Wed, 29 April 2009 07:28 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
that is possible.
Use concatination with SUBSTR

Thanks,
Clarification in 'Insert into' statement [message #401016 is a reply to message #400734] Fri, 01 May 2009 02:24 Go to previous messageGo to next message
ramsk0408
Messages: 9
Registered: April 2009
Junior Member
Hi,

Can you please help me out in the following scenario,

While inserting a value to the third column, it should be the value of concatenated string of first 2 charcters from first value and 5 characters from the second value from the insert command.

Thanks in advance. Smile

Regards,
Ram
Re: Clarification in 'Insert into' statement [message #401017 is a reply to message #401016] Fri, 01 May 2009 02:28 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
This is really basic stuff. It's been a couple of days since this was first posted - did you try to work it out for yourself?
INSERT INTO <table> (col_1,col_2,col_3)
values
(val_1,val_2, substr(val_1,1,2)||substr(val_2,1,5))
Re: Clarification in 'Insert into' statement [message #401019 is a reply to message #401016] Fri, 01 May 2009 02:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
While inserting a value to the third column, it should be the value of concatenated string of first 2 charcters from first value and 5 characters from the second value from the insert command.

In addition to JRowbottom's answer, this should never happen.
Please read Normalization Wiki page.

Regards
Michel

[Updated on: Fri, 01 May 2009 02:33]

Report message to a moderator

Re: Clarification in 'Insert into' statement [message #401020 is a reply to message #401017] Fri, 01 May 2009 02:36 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
dear JRowbottom,

No doubt, it is very simple one. As you have mentioned, the values need to be duplicated one at regular and another time within substr. is it not? will it be better if we use trigger using new, old and if the requirement is a regular business rule?
or am i side tracking?
yours
dr.s.raghunathan
Re: Query in 'insert into ' statement [message #401021 is a reply to message #400734] Fri, 01 May 2009 02:38 Go to previous messageGo to next message
ramsk0408
Messages: 9
Registered: April 2009
Junior Member
Can you please tell me how to refer to the column values in the substr function. I feel quite difficult to specify the column value which is going to be inserted in the insert command which is inserted now........

What i'm coming to say is

insert into table1 values('East','Texas',substr(?,1,1)+substr(?,1,5));

If i'm not wrong the syntax of the substr is right Wink

The thrid column value should be like 'ETexas'

Now i need to know what should i replace the ? in the code...
Re: Query in 'insert into ' statement [message #401024 is a reply to message #401021] Fri, 01 May 2009 02:46 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
In your example, the only thing you can replace the ? with is this:
 
insert into table1 values('East','Texas',substr('East',1,1)+substr('Texas',1,5));

If the 3rd value will always be populated like this, then you could create a trigger on the table that fired each time a row was inserted - this would let you reference the newly inserted values of the first two columns dynamicaly:
CREATE OR REPLACE TRIGGER table1_bri
BEFORE INSERT ON table 1
FOR EACH ROW
BEGIN
  :new.col_3 := substr(:new.col_1,1,1)+substr(:new.col_2,1,5
END;
/)
Re: Clarification in 'Insert into' statement [message #401025 is a reply to message #401020] Fri, 01 May 2009 02:47 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
By a million to one chance, I've just posted a trigger based solution in the OP's other, identical, thread.

Great minds think alike......
Re: Query in 'insert into ' statement [message #401033 is a reply to message #401021] Fri, 01 May 2009 04:13 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do NOT multipost your question.

By the way, + is NOT the concatenation operator.

Regards
Michel
Re: Query in 'insert into ' statement [message #401035 is a reply to message #401024] Fri, 01 May 2009 04:19 Go to previous messageGo to next message
ramsk0408
Messages: 9
Registered: April 2009
Junior Member
Thanks for your response JRowbottom..... But this can't be achieved with out using trigger.....?
Re: Query in 'insert into ' statement [message #401036 is a reply to message #401033] Fri, 01 May 2009 04:21 Go to previous messageGo to next message
ramsk0408
Messages: 9
Registered: April 2009
Junior Member
So sorry i thought that the previous post was not submitted as my system rebooted before i give submit....

Thanks,
Ram.
Re: Query in 'insert into ' statement [message #401038 is a reply to message #401036] Fri, 01 May 2009 04:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
i thought that the previous post was not submitted as my system rebooted before i give submit....

How could this be possible? The first topic was created on "Wed, 29 April 2009 14:01" (France time) and the second one on "Fri, 01 May 2009 09:24" and you got 2 answers on the first topic 29 April before reposting.

Regards
Michel
Re: Query in 'insert into ' statement [message #401045 is a reply to message #401038] Fri, 01 May 2009 05:09 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
So? It just means that his computer takes 2 days to reboot.
Re: Query in 'insert into ' statement [message #401049 is a reply to message #401045] Fri, 01 May 2009 05:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Laughing

Re: Query in 'insert into ' statement [message #401051 is a reply to message #401045] Fri, 01 May 2009 05:41 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Cool
Re: Query in 'insert into ' statement [message #401052 is a reply to message #401033] Fri, 01 May 2009 05:42 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
By the way, + is NOT the concatenation opera


No, it's not. I wonder what happened there.

must be losing my marbles, I guess....
Previous Topic: Deleteing records
Next Topic: SQL Select Query
Goto Forum:
  


Current Time: Thu Dec 08 23:56:28 CST 2016

Total time taken to generate the page: 0.09931 seconds