pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSetting a default value for a select statement without results
5+ messages / 4 participants
[nested] [flat]
* Setting a default value for a select statement without results
@ 2013-02-06 00:23 JORGE MALDONADO <jorgemal1960@gmail.com>
0 siblings, 3 replies; 5+ messages in thread
From: JORGE MALDONADO @ 2013-02-06 00:23 UTC (permalink / raw)
To: pgsql-sql
I have an UPDATE query with the following general structure:
UPDATE table1 SET (SELECT field FROM table2 WHERE conditions ORDER BY
order_field LIMIT 1)
Is it possible to assign a default value in case no results are returned by
the SELECT statement?
Respectfully,
Jorge Maldonado
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Setting a default value for a select statement without results
@ 2013-02-06 00:35 Anton Gavazuk <antongavazuk@gmail.com>
parent: JORGE MALDONADO <jorgemal1960@gmail.com>
2 siblings, 0 replies; 5+ messages in thread
From: Anton Gavazuk @ 2013-02-06 00:35 UTC (permalink / raw)
To: JORGE MALDONADO <jorgemal1960@gmail.com>; +Cc: pgsql-sql
Hi Jorge,
Look on http://www.postgresql.org/docs/8.1/static/functions-conditional.html
Thanks,
Anton
On Feb 6, 2013, at 0:23, JORGE MALDONADO <jorgemal1960@gmail.com> wrote:
> I have an UPDATE query with the following general structure:
>
> UPDATE table1 SET (SELECT field FROM table2 WHERE conditions ORDER BY order_field LIMIT 1)
>
> Is it possible to assign a default value in case no results are returned by the SELECT statement?
>
> Respectfully,
> Jorge Maldonado
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Setting a default value for a select statement without results
@ 2013-02-06 00:48 Ian Lawrence Barwick <barwick@gmail.com>
parent: JORGE MALDONADO <jorgemal1960@gmail.com>
2 siblings, 0 replies; 5+ messages in thread
From: Ian Lawrence Barwick @ 2013-02-06 00:48 UTC (permalink / raw)
To: JORGE MALDONADO <jorgemal1960@gmail.com>; +Cc: pgsql-sql
2013/2/6 JORGE MALDONADO <jorgemal1960@gmail.com>:
> I have an UPDATE query with the following general structure:
>
> UPDATE table1 SET (SELECT field FROM table2 WHERE conditions ORDER BY
> order_field LIMIT 1)
>
> Is it possible to assign a default value in case no results are returned by
> the SELECT statement?
One option would be to do something with UNION along these lines:
UPDATE table1 SET field =
(SELECT field FROM table2 WHERE conditions
UNION
SELECT 'default_value' WHERE NOT EXISTS (SELECT field FROM table2
WHERE conditions )
ORDER BY order_field LIMIT 1
)
HTH
Ian Barwick
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Setting a default value for a select statement without results
@ 2013-02-06 04:07 Jasen Betts <jasen@xnet.co.nz>
parent: JORGE MALDONADO <jorgemal1960@gmail.com>
2 siblings, 1 reply; 5+ messages in thread
From: Jasen Betts @ 2013-02-06 04:07 UTC (permalink / raw)
To: pgsql-sql
On 2013-02-06, JORGE MALDONADO <jorgemal1960@gmail.com> wrote:
> --f46d0401fb2fcb805e04d50354b1
> Content-Type: text/plain; charset=ISO-8859-1
>
> I have an UPDATE query with the following general structure:
>
> UPDATE table1 SET (SELECT field FROM table2 WHERE conditions ORDER BY
> order_field LIMIT 1)
assuming you mean this, and you are happy with its performance.
UPDATE table1 SET field1 = (SELECT field FROM table2 WHERE conditions ORDER BY
order_field LIMIT 1)
> Is it possible to assign a default value in case no results are returned by
> the SELECT statement?
use coalesce.
UPDATE table1 SET field1 = coalesce( (SELECT field FROM table2 WHERE conditions ORDER BY
order_field LIMIT 1) , default_value )
--
⚂⚃ 100% natural
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Setting a default value for a select statement without results
@ 2013-02-06 20:04 JORGE MALDONADO <jorgemal1960@gmail.com>
parent: Jasen Betts <jasen@xnet.co.nz>
0 siblings, 0 replies; 5+ messages in thread
From: JORGE MALDONADO @ 2013-02-06 20:04 UTC (permalink / raw)
To: pgsql-sql
This solution gave me the result I need, but it seems the process takes
longer when COALESCE is added.
What do you mean with the comment of "and you are happy with its performance"
?
Does it have to do with performance?
Regards,
Jorge Maldonado
On Tue, Feb 5, 2013 at 10:07 PM, Jasen Betts <jasen@xnet.co.nz> wrote:
> On 2013-02-06, JORGE MALDONADO <jorgemal1960@gmail.com> wrote:
> > --f46d0401fb2fcb805e04d50354b1
> > Content-Type: text/plain; charset=ISO-8859-1
> >
> > I have an UPDATE query with the following general structure:
> >
> > UPDATE table1 SET (SELECT field FROM table2 WHERE conditions ORDER BY
> > order_field LIMIT 1)
>
> assuming you mean this, and you are happy with its performance.
>
> UPDATE table1 SET field1 = (SELECT field FROM table2 WHERE conditions
> ORDER BY
> order_field LIMIT 1)
>
>
> > Is it possible to assign a default value in case no results are returned
> by
> > the SELECT statement?
>
> use coalesce.
>
> UPDATE table1 SET field1 = coalesce( (SELECT field FROM table2 WHERE
> conditions ORDER BY
> order_field LIMIT 1) , default_value )
>
>
>
>
> --
> ⚂⚃ 100% natural
>
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2013-02-06 20:04 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-02-06 00:23 Setting a default value for a select statement without results JORGE MALDONADO <jorgemal1960@gmail.com>
2013-02-06 00:35 ` Anton Gavazuk <antongavazuk@gmail.com>
2013-02-06 00:48 ` Ian Lawrence Barwick <barwick@gmail.com>
2013-02-06 04:07 ` Jasen Betts <jasen@xnet.co.nz>
2013-02-06 20:04 ` JORGE MALDONADO <jorgemal1960@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox