Обсуждение: Setting a default value for a select statement without results
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
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
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 (SELECTfield FROM table2 WHERE conditions ) ORDER BY order_field LIMIT 1 ) HTH Ian Barwick
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 WHEREconditions ORDER BYorder_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 BYorder_field LIMIT 1) , default_value) -- ⚂⚃ 100% natural
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
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>assuming you mean this, and you are happy with its performance.
> 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)
UPDATE table1 SET field1 = (SELECT field FROM table2 WHERE conditions ORDER BYorder_field LIMIT 1)use coalesce.
> Is it possible to assign a default value in case no results are returned by
> the SELECT statement?
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