Insert a copy of a row in a table and checking for duplicates

Question

I am looking to copy 4 existing rows in a database and need to change the value of one of the columns in the new rows. I need to do this and also make sure that no duplicates are created. What I have so far is

INSERT INTO table (col1, col2, col3)
SELECT 141, col2, col3,
FROM table
WHERE col1 = 99 AND NOT EXISTS
(SELECT * FROM table WHERE col1 = 141, col2, col3)

However, when I try run this, I have a syntax error at the comma in my (SELECT * FROM table WHERE col1 = 141, col2, col3) row.

Can anyone help?

EDIT: I have 4 rows with the following values in my database

col1	col 2	col 3
99	1	Enabled
99	3	A
99	4	B
99	5	C

and I want to create the following rows, but check for duplicates first

col1	col 2	col 3
141	1	Enabled
141	3	A
141	4	B
141	5	C

Commas are not accepted in `WHERE`. And your intention rather unclear. You specify that you want to copy *four* values, but the statement only has three columns. Overall, I can't make sense out of the question. — Gordon Linoff, Feb 22 '21 at 13:38
Which DBMS product are you using? "SQL" is just a query language used by all relational databases, not the name of a specific database product. Please add a [tag](https://stackoverflow.com/help/tagging) for the database product you are using. [Why should I tag my DBMS](https://meta.stackoverflow.com/questions/388759/) — , Feb 22 '21 at 13:39
Hi, I have updated my question so I hope this is clearer to understand :) — smose, Feb 22 '21 at 13:51

Kapitany · Answer 1 · 2021-02-22T14:34:25.863

0

I think you should use the EXCEPT keyword in your select (or MINUS if you use Oracle):

INSERT INTO table (col1, col2, col3)
SELECT 141, col2, col3,
FROM table
WHERE col1 = 99
EXCEPT
SELECT 141, col2, col3,
FROM table
WHERE col1 = 141;

edited Feb 22 '21 at 14:34

answered Feb 22 '21 at 13:44

Kapitany

1,319
1
6
10

Kazi Mohammad Ali Nur Romel · Answer 2 · 2021-02-22T14:34:29.030

0

Just removing col2 and col3 should solve the problem.

  INSERT INTO table (col1, col2, col3)
    SELECT 141, col2, col3
FROM table_name t1
WHERE col1 = 99 AND NOT EXISTS
(SELECT * FROM table_name t2 WHERE t2.col1 = 141 and t1.col2=t2.col2 and t1.col3=t2.col3)

edited Feb 22 '21 at 14:34

answered Feb 22 '21 at 14:06

Kazi Mohammad Ali Nur Romel

15,301
2
13
26

Thanks for your answer. I have tried this, but if I already have an existing row in my table with the value of 141, it doesn't add the other 3 it needs to copy – smose Feb 22 '21 at 14:16
@smose I have changed my answer to adopt this situation. It will now only insert the rows which don't have same col2 and col3 for 141 in col1. – Kazi Mohammad Ali Nur Romel Feb 22 '21 at 14:34

score 0 · Answer 3 · answered Feb 22 '21 at 15:57

0

I have solved this problem now with the following:

INSERT INTO table (col1, col2, col3)
SELECT 141, col2, col3,
FROM table t
WHERE col1 = 99 AND NOT EXISTS
(SELECT * FROM table nt
WHERE nt.col1 = 141 AND nt.col2 = t.col2 AND nt.col3 = t.col3)

answered Feb 22 '21 at 15:57

smose

145
1
11

Insert a copy of a row in a table and checking for duplicates

3 Answers3