CODEWITHSUNDEEP
mysqlsql
TAHA SULTAN TEMURI
TAHA SULTAN TEMURI

Reputation: 5191

Insert Record Else Update From Select Query MySql

How can I use update if not exists in this query?

INSERT INTO MyTbl(name, address, tele)
SELECT * FROM (SELECT 'Rupert', 'Somewhere', '022') AS tmp
WHERE NOT EXISTS (
SELECT name FROM MyTblWHERE name = 'Rupert'
) LIMIT 1;

I tried this but this throws error.

INSERT INTO MyTbl(name, address, tele)
SELECT * FROM (SELECT 'Rupert', 'Somewhere', '022') AS tmp
WHERE NOT EXISTS (
SELECT name FROM MyTblWHERE name = 'Rupert'
) LIMIT 1 ON DUPLICATE KEY UPDATE name = tmp.Name;

Upvotes: 0

Views: 82

Answers (3)

Raymond Nijland
Raymond Nijland

Reputation: 11602

As nobody knows your table structure or how your data is stored and real data requirements/constraints maybe the best way to simulate more or less the ANSI/ISO SQL standard MERGE (upsert) command with the querys below.. 

UPDATE
  MyTbl
INNER JOIN (
   SELECT
        MyTbl.id
   FROM MyTbl
   WHERE
     MyTbl.name = 'Rupert'
    AND
     MyTbl.address = 'Somewhere'
   AND
    MyTbl.tele = '022' 
) AS MyTbl_checked
USING(id) # assumes a PRIMARY AUTO_INCREMENT id
SET
    MyTbl.name = 'Rupert'
  , MyTbl.address = 'Somewhere'
  , MyTbl.tele = '022'
WHERE
   MyTbl.id = MyTbl_checked.id; 

INSERT INTO MyTbl(name, address, tele)
SELECT * FROM (SELECT 'Rupert', 'Somewhere', '022') AS tmp
WHERE NOT EXISTS (
  SELECT 1 FROM MyTblWHERE name = 'Rupert'
);

Note:
Untested but in theory it should do the trick just fine.
Also these queries will not correctly handle cases where people sharing the same name on address/tele combination, so keep that in mind.

Performance note
For performance a normal key can be created and need to atleast be KEY(name, address, tele) to boost both the INSERT and UPDATE.

As MySQL dialect implemention of MERGE (upsert) requires a unique key on name when using the query below.. 

INSERT INTO MyTbl (name, address, tele)
    SELECT name, address, tele
    FROM (SELECT 'Rupert' as name, 'Somewhere' as address, '022' as tele
         ) t
    ON DUPLICATE KEY UPDATE name = t.Name;

Meaning it will limit the usage of Rupert to be used only once..

Upvotes: 1

A.Jain
A.Jain

Reputation: 80

You can use Like this    

INSERT INTO table (id, name, age) VALUES(1, "A", 19) ON DUPLICATE KEY UPDATE    
    name="A", age=19

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269953

I think you need column names in the subquery:

INSERT INTO MyTbl (name, address, tele)
    SELECT name, address, tele
    FROM (SELECT 'Rupert' as name, 'Somewhere' as address, '022' as tele
         ) t
    ON DUPLICATE KEY UPDATE name = t.Name;

You don't need the EXISTS check -- presumably you have a unique constraint if you are using ON DUPLICATE KEY. If you don't have a unique key, then presumably you want:

create index unq_mytbl_name on mytbl(name);

Similarly, the LIMIT 1 is irrelevant: your data has only one row.

For a single row, you might find it more convenient to write this as:

INSERT INTO MyTbl (name, address, tele)
    VALUES ('Rupert', 'Somewhere', '022')
    ON DUPLICATE KEY UPDATE name = VALUES(Name);

Upvotes: 2

Related Questions