Re: SQL*PLUS Syntax for Concatenation

Dave Schellenberg (dschellenberg_at_man.net) wrote:
: We are running Oracle 7 on VAX/VMS. I have two questions dealing with SQL*PLUS
: (and maybe PL/SQL) that I need help with:
 

: Question 1:
 

: I have two tables, call them table1 and table2. I want something like this:
 

: UPDATE TABLE1
: SET TABLE1.COL2 = concatenated CHARs from TABLE2.COL2
: WHERE TABLE2.ID = TABLE1.ID;
 
: In other words, I just want to concatenate all of the strings from
: table2.column2, separating each with a space, and then set a much bigger CHAR
: in table1 to that string. I have no idea!

I don't think you will be able to do this with a simple SQL statement. You will have to do it in a PL/SQL block. Process table2 with a cursor and then update table1 at the end.

: Question 2:
 

: I want to find the second-highest date in a column in a table. It is too
: easy to find the highest, using MAX( ), but I can't think of how to find the
: next highest.
 

: Anyone here ever done either of these?

You could do:

 SELECT MAX(c1) FROM t1
  WHERE c1 < (SELECT MAX(c1) FROM t1)

--
Karl Penney                           |~~~~~~|     _/^\_     |~~~~~~|
DMR Group, Halifax, NS                |      | _/\_\   /_/\_ |      |
aa651_at_cfn.cs.dal.ca                   |      | >___________< |      |
74203.673_at_compuserve.com              |______|       |       |______|