SQL Insert or Update

Question

SQL Insert or Update

Is there any function that would add values to the database if there are no values with a specific id, and update if the value exists. As far as I understand, we need to check each time for the existence of this line and then, if it exists, then we perform update, and if not, then insert. Or is there another option, without unnecessary requests?

1

php sql

Author: Alex, 2014-10-05

Source

2 answers

score 2 · Answer 1

Response from the comment

In MySQL, you can use the {[1] construct]}:

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;

Learn more http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html

score 0 · Answer 2

Specify the database. for postgres, upsert will look like this

UPDATE "SC_Wonder"."TMining_New"
SET "FH_09_Q" = 100,
    "FH_09_Fe" = 20,
    "FH_09_ZnO" = 0,
    "FH_09_S" =0
WHERE "FMineObj_ID" = 7
  AND "FDate" = '2015-01-28' ;
INSERT INTO "SC_Wonder"."TMining_New" ( "FDate",
                                        "FMineObj_ID",
                                        "FH_09_Q",
                                        "FH_09_Fe",
                                        "FH_09_ZnO",
                                        "FH_09_S" )
SELECT '2015-01-28',
       7,
       100,
       20,
       0,
       0
WHERE NOT EXISTS
    (SELECT 1
     FROM "SC_Wonder"."TMining_New"
     WHERE "FMineObj_ID" = 7
       AND "FDate" = '2015-01-28');