mysql insert  if not in another table

Posted on 2011-05-02
Last Modified: 2012-05-11
I have table A and table B

If an employee number exists in table B, but does not exist in table A, I want to insert  the employee number, the date and .01 into fields  'emp', 'date', and 'sales' of table A

What is the best way to do this with mysql in a PHP script?
Question by:birwin
    LVL 37

    Accepted Solution

    One SQL query:
    Insert into a (eid,date,sales)
    Select eid, now(), '.01' from b where eid not in (select eid from a)
    LVL 9

    Expert Comment

    i would use a syntax like below ;

    SELECT B.emp_id FROM tableA as B, tableB as A WHERE B.emp_id <>A.emp_id AND B.emp_id='1234'

    or better ;

    SELECT emp_id FROM tableB WHERE emp_id  NOT IN ( SELECT emp_id FROM tableA )
    LVL 6

    Author Closing Comment

    Worked perfectly, first try. Genius!

    Thank you for that great code.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
    Read about achieving the basic levels of HRIS security in the workplace.
    The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
    The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now