Re: quoting values magic

On 2009-05-22, Brandon Metcalf <brandon(at)geronimoalloys(dot)com> wrote:
> Assume I have an UPDATE statement that looks like
>
> UPDATE foo
> SET
> pattern = '$pattern',
> shape = '$shape',
> length = $length,
> comment = '$comment'
> WHERE foo_id = $foo_id
>
> and length is defined as NUMERIC. Is there any kind of magic that
> would allow me to use the SQL above as is even if $length is not
> defined?

no, but you can get the same effect in a different way.

> In other words, I'd like to avoid having to modify the SQL
> to include or not include "length = $length" based on whether or not
> $length is defined as it's acceptable for it to be NULL in foo.

> I can't say "length = '$length'" as '' is not valid input for NUMERIC.
>
> Hope that makes sense?

option 1: $length = "length"

If your language of choice (it appears to be similar to shell, PHP, or Perl)
allows you to store the string value "length" in your $length variable
then the existing values of length will be retained in the update.

option 2: case when '$length' = '' ...

you can use case like this:

UPDATE foo
SET
pattern = '$pattern',
shape = '$shape',
length = case when '$length'='' then length else '$length' end,
comment = '$comment'
WHERE foo_id = $foo_id

here you can substitute any value you choose for the empty string,
0 or NULL may (or may not) be more apropriate.