SQL Insert Command

To concatenate the values do this:

SELECT table1.name||table2.name
FROM table1,table2
WHERE ...;

or equivalently:

SELECT table1.name||table2.name
FROM table1
JOIN table2 ON ...;

I don't know how you intend to join the 2 tables, but presumably you do?

First you have to insure that the column length is sufficeint or use substr

Update Table1  A set NAME =
     NVL(  (select A.name || B.NAME from Table2 B where A.no = B.sid), A.name) ;

you can do like this..
   
If you are using  Oracle 9i.

    SELECT t1.name ||  t2.name 'new column name' FROM table t1 join  table t2 using (<common field name>);

Hope it will help u.

bhombal, your query will not work for 2 reasons:

1. Column names cannot be qualified with table name or table alias when using JOIN...USING.  Use JOIN...ON instead.
2. Only common field names between the two tables will be joined.  In this case, it would be only the "name" field.

It is not clear to me from your question what you want to do.  The title of your question indicates that you want to insert data somewhere, but your question talks about concatenating data from records that have already been inserted.  To use existing data from two different tables, you usually need to join the records based on matching values in fields from each table.  In your example, which fields can be used for the match, is it the "name" column?  Which values should be concatenated?  After concatenating, do you want the data inserted into one of these tables, or a third table?

Hi,

select table1.name||', '||table2.name    <==== Concatenate with a space or comma or whatever you want ||', '||
from table1, table2
where table1.id = table2.id;    <== Unless you have something joined between the two tables
                                                          you will have an exponential number of rows.    


Results:
||', '||                                         ===> John, Karen
||' '||                                          ===> John Karen
||' loves '||                                 ===> John loves Karen
||' needs the help of '||             ===> John needs the help of Karen

Another example is for DBA work:

Select 'GRANT UPDATE,DELETE,SELECT on TWF.SUBITEM to '|| USERNAME ||';'
from dba_users;

GRANT UPDATE,DELETE,SELECT on TWF.SUBITEM to TOML;
GRANT UPDATE,DELETE,SELECT on TWF.SUBITEM to FRANKB;
GRANT UPDATE,DELETE,SELECT on TWF.SUBITEM to SCOTT;
GRANT UPDATE,DELETE,SELECT on TWF.SUBITEM to PEPED;
GRANT UPDATE,DELETE,SELECT on TWF.SUBITEM to BCARLIS;


:) bcarlis

sir,

       i want  to insert upon concatenating of two fields from two tables;

example:

          table1:                                   table2:
                                                                     no name
                       no name                                 1   kumar
                         1  raj

my query is  i want to store raj & kumar in "name"  in table1

So both tables have a column called "no" to be joined, right?  That wasn't apparent in your first post.

UPDATE table1
SET name =
( SELECT table1.name||' '||table2.name
  FROM table2        
  WHERE table2.no = table1.no
)
WHERE table1.no IN
( SELECT table2.no FROM table2 );

The purpose of the WHERE clause is to ensure you only update table1 records that do have a matching table2 record.