Thread: Join three fields into one on same table

Hi, i need help for a query. I have three fields, year, month and day
into table and need join and update another field named date on same
table.

My problem is not that make update query.

With this query have the value of the update:
> select year || '-' || month || '-' || day || ' 01:00:00' as newdate from table

but this another query not have correct syntax...
> update table set date=(select year || '-' || month || '-' || day || ' 01:00:00' as newdate from table)

Regards,
Jordi

Is there a good reason to NOT store the year month and day as a date
instead of this way?

I can think of a lot of very good reasons to store it as a date, not a
lot of reasons to store them broken apart.

On Mon, May 19, 2008 at 4:51 AM, jrivero <godsea@gmail.com> wrote:
> Hi, i need help for a query. I have three fields, year, month and day
> into table and need join and update another field named date on same
> table.
>
> My problem is not that make update query.
>
> With this query have the value of the update:
>> select year || '-' || month || '-' || day || ' 01:00:00' as newdate from table
>
> but this another query not have correct syntax...
>> update table set date=(select year || '-' || month || '-' || day || ' 01:00:00' as newdate from table)
>
> Regards,
> Jordi
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

On Mon, May 19, 2008 at 4:51 AM, jrivero <godsea@gmail.com> wrote:
> Hi, i need help for a query. I have three fields, year, month and day
> into table and need join and update another field named date on same
> table.
>
> My problem is not that make update query.
>
> With this query have the value of the update:
>> select year || '-' || month || '-' || day || ' 01:00:00' as newdate from table
>
> but this another query not have correct syntax...
>> update table set date=(select year || '-' || month || '-' || day || ' 01:00:00' as newdate from table)

Cast the output of those concatenations to date:

update table set date=(select (year || '-' || month || '-' || day || '
01:00:00')::date as newdate from table)

Does that help?

On Mon, May 19, 2008 at 03:36:39PM -0600, Scott Marlowe wrote:
> On Mon, May 19, 2008 at 4:51 AM, jrivero <godsea@gmail.com> wrote:
> > My problem is not that make update query.
> >
> >> update table set date=(select year || '-' || month || '-' || day || ' 01:00:00' as newdate from table)
>
> Cast the output of those concatenations to date:
>
> update table set date=(select (year || '-' || month || '-' || day || '
> 01:00:00')::date as newdate from table)

I'd guess the OP doesn't want the sub-query, if he posted the error
message we'd know for sure.  I'd guess something like:

  update table set date=(year||'-'||month||'-'||day||'01:00:00')::date;

If the "date" column really is of date type, then the final
"||'01:00:00'" is somewhat superfluous.


  Sam

Very thanks Scott!! this is the solution was need :)

Jordi

On 19 mayo, 23:36, scott.marl...@gmail.com ("Scott Marlowe") wrote:
> On Mon, May 19, 2008 at 4:51 AM,jrivero<god...@gmail.com> wrote:
> > Hi, i need help for a query. I have three fields, year, month and day
> > into table and need join and update another field named date on same
> > table.
>
> > My problem is not that make update query.
>
> > With this query have the value of the update:
> >> select year || '-' || month || '-' || day || ' 01:00:00' as newdate from table
>
> > but this another query not have correct syntax...
> >> update table set date=(select year || '-' || month || '-' || day || ' 01:00:00' as newdate from table)
>
> Cast the output of those concatenations to date:
>
> update table set date=(select (year || '-' || month || '-' || day || '
> 01:00:00')::date as newdate from table)
>
> Does that help?
>
> --
> Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org)
> To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general