Feedback:

← General

Enhance INSERT statement to handle UPDATE syntax, ie. INSERT INTO x SET y=z

This would save having to maintain two separate sets of SQL code for INSERT / UPDATE into one.
ie.
// INSERT or UPDATE
if (bInsert) then begin
SQL.Add('INSERT INTO x SET');
SQL.Add('field1=:prmField1,');
end else begin
SQL.Add('UPDATE x SET');
end;

// set field values
SQL.Add('field2=:prmField2');
ParamByName('prmField2').AsString:='abc';

// UPDATE requires keyfield
if (not bInsert) then
SQL.Add('WHERE field1=:prmField1');

// set keyfield
ParamByName('prmField1').AsString:=sUniqueCode;
ExecSQL;

3 votes
Vote 0 votes Vote Vote
Vote
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    Forgot password?
    Create a password
    I agree to the terms of service

    You'll receive a confirmation email with a link to create a password (optional).

    Signed in as (Sign out)
    Close
    Close
    You have left! (?) (thinking…)
    anonymousanonymous shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →
    declined

    2 comments

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      Forgot password?
      Create a password
      I agree to the terms of service

      You'll receive a confirmation email with a link to create a password (optional).

      Signed in as (Sign out)
      Close
      Close
      Submitting...
      • AzzaAzza69AzzaAzza69 commented  ·   ·  Flag as inappropriate

        Please re-instate this...as you say, it looks like it is partially there:
        MERGE x ON ( field1=:prmField1 )
        WHEN MATCHED THEN UPDATE SET field2=:prmField2
        WHEN NOT MATCHED THEN INSERT ( field1, field2 ) VALUES ( :prmField1, :prmField2 )

        But while this will solve half the problem (with being multiple queries needing to be sent to server to COUNT and then either INSERT or UPDATE)
        it doesn't fix the second part of the problem with having to code twice (using SET for an UPDATE and using list of fieldnames and list of values for an INSERT);

        So my original post would stand, in that handling SET field=value using INSERT would cut down coding, eg:

        procedure SQL_AddNonKeyFields;
        begin
        SQL.Add('field2=:prmField2');
        ParamByName('prmField2'.AsString:='abc');
        end;
        SQL.Clear;
        SQL.Add('MERGE x ON ( field1=:prmField1 )');
        SQL.Add('WHEN MATCHED THEN UPDATE SET');
        SQL_AddNonKeyFields;
        SQL.Add('WHEN NOT MATCHED THEN INSERT SET');
        SQL.Add('field1=:prmField1');
        ParamByName('prmField1'.AsString:=sUniqueCode;
        SQL_AddNonKeyFields;

      • jeremym1234jeremym1234 commented  ·   ·  Flag as inappropriate

        Have you seen the MERGE statement? I believe it does this (or at least provides a portion of what you are requesting).

      General

      Knowledge Base and Helpdesk

      (thinking…)
      Reset