check if data exist while inserting data [duplicate]

Question

How to insert a record to a table if there is no data exist of same type

insert into  user_table (userid, active,contid) values ('AAA',1,1);
insert into  user_table (userid, active,contid) values ('ABA',1,2);
          
        INSERT INTO new_table(userid,isactive)
SELECT userid,1
  FROM user_table where contid=1
 WHERE NOT EXISTS (SELECT userid
                     FROM new_table
                    WHERE contid=1
                  )

i need to copy data from one table1 to table2 if table2 doesnt have the same data. if data exist just skip and dont make any insert. I am getting "SQL command not properly ended" error with the above query

Please do not edit answers into your question that substantially changes the question and invalidates (some) existing answers. If you have clarifications to the question then keep the existing code and add a new section below with the clarification; or, if you have a follow-up question then ask a new question. — MT0
– MT0, Commented May 9, 2023 at 7:47

MT0 · Accepted Answer · 2023-05-09 07:40:42Z

2

You can use a MERGE statement:

MERGE INTO new_table dst
USING ( SELECT userid, contid
        FROM   user_table
        WHERE  contid = 1) src
ON src.userid = dst.userid AND src.contid = dst.contid
WHEN NOT MATCHED THEN
  INSERT (userid, isactive) VALUES (src.userid, 1)

answered May 9, 2023 at 7:40

MT0

173k12 gold badges70 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

nbk · Accepted Answer · 2023-05-09 07:32:20Z

1

The exisrs clause should have another condition.

You need to check if a active userid already exists too

INSERT INTO new_table(userid,isactive)
SELECT userid,1
  FROM user_table WHERE contid=1
 AND NOT EXISTS (SELECT 1
                     FROM new_table
                    WHERE user_table.userid = new_table.userid AND contid=1
                  )

answered May 9, 2023 at 7:32

nbk

49.6k8 gold badges32 silver badges53 bronze badges

Comments

Littlefoot · Accepted Answer · 2023-05-09 07:21:06Z

1

Your insert query has two where keywords; the 2nd one should've been and:

INSERT INTO new_table (userid, isactive)
   SELECT userid, 1
     FROM user_table
    WHERE     contid = 1
          AND NOT EXISTS               --> AND instead of WHERE
                 (SELECT userid
                    FROM new_table
                   WHERE contid = 1);

Also, perhaps a minus set operator would do as well:

INSERT INTO new_table (userid, isactive)
   SELECT u.userid, 1
     FROM user_table u
   MINUS
   SELECT n.userid, 1
     FROM new_table n;

answered May 9, 2023 at 7:21

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

Comments

Onur T. · Accepted Answer · 2023-05-09 07:26:10Z

0

You used the where keyword twice. I'm sharing the code below.

INSERT INTO new_table(userid,isactive)
SELECT userid,1 
FROM user_table 
WHERE contid=1 
AND NOT EXISTS (SELECT userid FROM new_table WHERE contid=1 )

answered May 9, 2023 at 7:26

Onur T.

11 silver badge

1 Comment

MT0 Over a year ago

This fixes the syntax error but will only add the row when new_table contains no rows where contid=1 regardless of the userid; it does not correlate the EXISTS sub-query to compare the userid values.

SelVazi · Accepted Answer · 2023-05-09 07:48:53Z

0

You can use left join to get only users that are not exists on table new_table using the condition new_table.userid is null :

INSERT INTO new_table(userid, isactive)
SELECT u.userid, 1
FROM user_table u
LEFT JOIN new_table n on n.userid = u.userid
where u.contid=1 and u.active = 1 and n.userid is null

answered May 9, 2023 at 7:48

SelVazi

16.2k2 gold badges20 silver badges35 bronze badges

Collectives™ on Stack Overflow

check if data exist while inserting data [duplicate]

5 Answers 5

Comments

Comments

Comments

1 Comment

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

1 Comment

Comments

Linked

Related