Thread: Insert vs Update syntax
When inserting into a table and there are many columns to be inserted
it is hard to synchronize columns to values:
insert into my_table (
a,
b,
c,
...many more columns
)values(
@a,
@b,
@c,
... the corresponding values
)
Is there some reason for the insert syntax to be the way it is in
instead of the much easier to get it right Update syntax?:
update my_table
set a = @a,
b = @b,
c = @c,
...
Regards, Clodoaldo Pinto Neto
On Fri, Feb 29, 2008 at 01:17:20PM -0300, Clodoaldo wrote: > When inserting into a table and there are many columns to be inserted > it is hard to synchronize columns to values: <snip> > Is there some reason for the insert syntax to be the way it is in > instead of the much easier to get it right Update syntax?: Because it's what the SQL standard says. If you don't like it I suggest you take it up with them... But it's a little late to change now I think. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Those who make peaceful revolution impossible will make violent revolution inevitable. > -- John F Kennedy
Attachment
2008/2/29, Martijn van Oosterhout <kleptog@svana.org>: > On Fri, Feb 29, 2008 at 01:17:20PM -0300, Clodoaldo wrote: > > When inserting into a table and there are many columns to be inserted > > it is hard to synchronize columns to values: > > > <snip> > > > > Is there some reason for the insert syntax to be the way it is in > > instead of the much easier to get it right Update syntax?: > > > Because it's what the SQL standard says. If you don't like it I suggest > you take it up with them... But it's a little late to change now I > think. I know about the standards and I'm not blaming postgresql. I just want to know if it is worth to bring the the matter to some responsible group. Regards, Clodoaldo Pinto Neto
If you don't like the standard sql implementation, you could use plsql
or any language to make an abstraction layer/wrapper for this
functionality. Just pass everything as a key/value pair, in an array or
hashtable structure, to your abstraction layer/wrapper, and it can cycle
through the data structure to do the insert or update for you.
In very rough psuedo-code, something like
create my_data_structure {
table = employees,
first_name = sally,
last_name = smith,
date_of_birth = 2008-01-01
}
call function mywrapper (my_data_structure)
.....
function cycles through array, and creates an sql string
dynamically
This may have problems with constraints, foreign keys, etc.
Clodoaldo wrote:
> 2008/2/29, Martijn van Oosterhout <kleptog@svana.org>:
>
>> On Fri, Feb 29, 2008 at 01:17:20PM -0300, Clodoaldo wrote:
>> > When inserting into a table and there are many columns to be inserted
>> > it is hard to synchronize columns to values:
>>
>>
>> <snip>
>>
>>
>> > Is there some reason for the insert syntax to be the way it is in
>> > instead of the much easier to get it right Update syntax?:
>>
>>
>> Because it's what the SQL standard says. If you don't like it I suggest
>> you take it up with them... But it's a little late to change now I
>> think.
>>
>
> I know about the standards and I'm not blaming postgresql. I just want
> to know if it is worth to bring the the matter to some responsible
> group.
>
> Regards, Clodoaldo Pinto Neto
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
>
2008/2/29, Brent Friedman <bfriedman@scanonline.com>:
> If you don't like the standard sql implementation, you could use plsql
> or any language to make an abstraction layer/wrapper for this
> functionality. Just pass everything as a key/value pair, in an array or
> hashtable structure, to your abstraction layer/wrapper, and it can cycle
> through the data structure to do the insert or update for you.
Ok. I have been playing these tricks for the last 25 years. Please no
more tricks. If someone knows something about the reason for the
insert syntax in instead of the Update syntax then please elaborate on
it.
Regards, Clodoaldo Pinto Neto
> In very rough psuedo-code, something like
>
> create my_data_structure {
> table = employees,
> first_name = sally,
> last_name = smith,
> date_of_birth = 2008-01-01
> }
>
> call function mywrapper (my_data_structure)
>
> .....
> function cycles through array, and creates an sql string
> dynamically
>
> This may have problems with constraints, foreign keys, etc.
>
>
>
> Clodoaldo wrote:
> > 2008/2/29, Martijn van Oosterhout <kleptog@svana.org>:
> >
> >> On Fri, Feb 29, 2008 at 01:17:20PM -0300, Clodoaldo wrote:
> >> > When inserting into a table and there are many columns to be inserted
> >> > it is hard to synchronize columns to values:
> >>
> >>
> >> <snip>
> >>
> >>
> >> > Is there some reason for the insert syntax to be the way it is in
> >> > instead of the much easier to get it right Update syntax?:
> >>
> >>
> >> Because it's what the SQL standard says. If you don't like it I suggest
> >> you take it up with them... But it's a little late to change now I
> >> think.
> >>
> >
> > I know about the standards and I'm not blaming postgresql. I just want
> > to know if it is worth to bring the the matter to some responsible
> > group.
> >
> > Regards, Clodoaldo Pinto Neto
> >
>
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: explain analyze is your friend
> >
> >
>
>
Hi,<br /><br /> I've solved this problem for me (Perl). I have a module DBAPI and write a function InsertIntoTable($table_name,$hash_with_values, $data_base_handler).<br /> I send the parms to the function in the hash (key1=> value1, key2 => value2 ...) and in the function I compose the insert and execute it.<br /><br /> INSERT INTO$table_name (key1, key2, key3) VALUES (value1, value2, value3);<br /><br /> So I suggest you write such function andsolve most of your problems:-)<br /><br /> Regards, <br /> Kaloyan Iliev<br /> <br /> Clodoaldo wrote: <blockquotecite="mida595de7a0802290831y785c402ewcf9abdec00dac9e8@mail.gmail.com" type="cite"><pre wrap="">2008/2/29, Martijnvan Oosterhout <a class="moz-txt-link-rfc2396E" href="mailto:kleptog@svana.org"><kleptog@svana.org></a>: </pre><blockquotetype="cite"><pre wrap="">On Fri, Feb 29, 2008 at 01:17:20PM -0300, Clodoaldo wrote:> When inserting intoa table and there are many columns to be inserted> it is hard to synchronize columns to values: <snip> > Is there some reason for the insert syntax to be the way it is in> instead of the much easier to get it right Updatesyntax?: Because it's what the SQL standard says. If you don't like it I suggestyou take it up with them... But it's a little lateto change now Ithink. </pre></blockquote><pre wrap=""> I know about the standards and I'm not blaming postgresql. I just want to know if it is worth to bring the the matter to some responsible group. Regards, Clodoaldo Pinto Neto ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend </pre></blockquote><br />
Clodoaldo wrote: > 2008/2/29, Brent Friedman <bfriedman@scanonline.com>: >> If you don't like the standard sql implementation, you could use plsql >> or any language to make an abstraction layer/wrapper for this >> functionality. Just pass everything as a key/value pair, in an array or >> hashtable structure, to your abstraction layer/wrapper, and it can cycle >> through the data structure to do the insert or update for you. > > Ok. I have been playing these tricks for the last 25 years. Please no > more tricks. Tricks? > If someone knows something about the reason for the > insert syntax in instead of the Update syntax then please elaborate on > it. I believe it's because of the way it reads in English: "INSERT INTO <some columns> <these values>". One of the key benefits of SQL was supposed to be its ability for non-programmers to use it. As a result, they sacrificed consistency for the sake of ease-of-learning. Of course, once you start writing queries of any complexity, you lose the benefits of reading as English. If you come across a time machine, pop back to the first standards meeting and have a word with them, would you? -- Richard Huxton Archonet Ltd