Обсуждение: 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
On Tue, May 20, 2008 at 5:34 AM, Sam Mason <sam@samason.me.uk> wrote:
That'd be my guess too. OP's subquery is possibly returning more than one row, and hence an error.
--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com
EnterpriseDB http://www.enterprisedb.com
Mail sent from my BlackLaptop device
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)I'd guess the OP doesn't want the sub-query, if he posted the error
>
> Cast the output of those concatenations to date:
>
> update table set date=(select (year || '-' || month || '-' || day || '
> 01:00:00')::date as newdate from table)
message we'd know for sure. I'd guess something like:
update table set date=(year||'-'||month||'-'||day||'01:00:00')::date;
That'd be my guess too. OP's subquery is possibly returning more than one row, and hence an error.
If the "date" column really is of date type, then the final
"||'01:00:00'" is somewhat superfluous.
--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com
EnterpriseDB http://www.enterprisedb.com
Mail sent from my BlackLaptop device
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