In MySQL, Copy a row record from the same table only if that row doesn't exist?-CodePudding

I was trying to figure out how to copy a row record from the same table only if that row doesn't exist, but solutions I found was just a part of it. Like, 'copying a record', or 'insert if not exists'. So I tried to merge those answers to make mine, but... I think I only made some abomination code.

Let's see this

INSERT INTO LCL
SELECT * FROM LCL WHERE (UID = 0 AND NAME = 'S_TYPE')
WHERE NOT EXISTS (SELECT * FROM LCL WHERE UID = 11 AND NAME = 'S_TYPE' LIMIT 1);

There is a default format records with UID = 0. And if there is no record with NAME = S_TYPE, copy record with NAME = S_TYPE AND UID = 0, and change UID into 11. The record should copy the data of all columns. So I tried to use SELECT * but not sure if it is right.

I'm sure I screwed things up with two WHEREs...

CodePudding user response：

If you already have a WHERE then the follow up should be AND. I don't think you really need the parentheses on the first WHERE though so:

INSERT INTO LCL
SELECT * FROM LCL 
 WHERE UID = 0 AND NAME = 'S_TYPE'
   AND NOT EXISTS (SELECT * FROM LCL WHERE UID = 11 AND NAME = 'S_TYPE' LIMIT 1);