CODEWITHSUNDEEP
phpmysqlsqlphpmyadmin
dan
dan

Reputation: 563

Adding new rows in sql

I have these two tables in my database :

table_A:                 table_B:                  

id    user   grade       id    user      date            
1     Mike   9.5         1     Mike      9:05    
2     Dan    absent      2     Dan       7:40    
3     Tom    9.4         3     Tom       3:20
4     Lina   7.4         4     Lina      7:40
                         5     Cynthia   5:39
                         6     Sam       4:50

And i'm using this SQL query, to detect which users in table_B do not exist in table_A, and select those users (not existing in table A) ids:

SELECT table_B.id FROM table_B WHERE table_B.id NOT IN (SELECT table_A.id FROM table_A);

And i'm using this php script to put the ids in a variable $not:

 $sql=" SELECT table_B.id FROM table_B WHERE table_B.id NOT IN (SELECT table_A.id FROM table_A)";
            $result = mysqli_query($con,$sql);
            if (!$result) {
                printf("Error: %s\n", mysqli_error($con));
                exit();
            }
            while($row = mysqli_fetch_array($result)){
                 $not=$row[0];
            }

The thing is, for those users not existing the query found, i want to extract their names and ids only (without date), and insert it in table_A and have an empty grade.. Any help?

Upvotes: 2

Views: 64

Answers (3)

alagu
alagu

Reputation: 779

Try this,

  Insert into table_A(id, user, '') SELECT b.id, b.user FROM table_B b WHERE b.id NOT IN (SELECT a.id FROM table_A a);

Upvotes: 0

Jorick Spitzen
Jorick Spitzen

Reputation: 1639

You can use INSERT INTO SELECT. https://dev.mysql.com/doc/refman/5.0/en/insert-select.html

Your query would look like:

INSERT INTO `table_A` (`id`, `user`)
SELECT `id`, `name` FROM `table_B` 
WHERE table_B.id NOT IN (SELECT table_A.id FROM table_A);

Upvotes: 0

Madhivanan
Madhivanan

Reputation: 13700

Insert into table_A(user,id,grade)
SELECT table_B.id,table_B.user,'' FROM table_B 
WHERE table_B.id NOT IN (SELECT table_A.id FROM table_A);

Upvotes: 1

Related Questions