Worked perfectly. Thanks Richard. On Tue, Jul 1, 2008 at 10:17 AM, Richard Huxton <[EMAIL PROTECTED]> wrote:
> Dhanushka Samarakoon wrote: > >> Thanks for the reply. >> But one problem I have is I need to loop through all the rows in the table >> and in each iteration I need to fetch the value of mydate in to a variable >> and split it to month and year and add two rows with *value, 91, month* (2 >> , >> 91, Augest) and *value, 86, year* (2 , 86 , 2009) >> >> So I need an idea on >> - how to loop through all the rows thats returned by a select statement. >> - for each statement how to get the value of mydate in to a variable, so >> that I can use SubString to split it in to date and year and use them in >> the >> insert statement. >> > > Ah, looking back I see "mydate" isn't actually a date. Note the space in > the pattern for substring() below: > > INSERT INTO metadata (value, field, mydate) > SELECT value,91, substring(mydate, '(.+) ') > FROM metadata > UNION ALL > SELECT value, 86, substring(mydate, ' (.+)') > FROM metadata; > > Does that do it for you? Try the SELECT clauses by themselves to check if > they're doing the right thing. > > -- > Richard Huxton > Archonet Ltd >
