You need to populate one table with data from another table in one database

Question

I am trying to populate one table from another table using mysql. The first table is the users, and the second table is the technique.

Users contain: userID, last name, first name, login, password, accesslevel.

Techniques contain: techID, tech_surname, tech_firstname, tech_loginid, tech_password, tech_accesslevel.

When I add a user, I want the tech table to populate if accesslevel = tech and users.loginid are not equal technicians.tech_loginid.

I tried several things, and the result is that no records are added, or all records in users where accesslevel = tech are added every time, giving me several duplicate records.

I tried this:

INSERT INTO technicians (techID, tech_surname, tech_firstname, tech_loginid, tech_passwrd, tech_accesslevel) SELECT firstname, surname, loginid, accesslevel, passwrd, tech_loginid FROM users, technicians WHERE accesslevel='tech' AND 'loginid!=tech_loginid' 

This will not work, and if I remove the AND operator from the WHERE clause, it will pop all the records each time a new user is added using accesslevel = tech.

What am I doing wrong? I searched for several hours for an answer.

Greetings

Answer 1

I think you are trying to insert those technicians that no longer exist in this table, so the query will be something like this

 INSERT INTO technicians (techID, tech_surname, tech_firstname, tech_loginid, tech_passwrd, tech_accesslevel) SELECT loginid, surname, firstname, tech_loginid, passwrd, accesslevel FROM users LEFT OUTER JOIN technicians ON loginid = tech_loginid WHERE accesslevel='tech' and tech_loginid IS null 

Answer 2

You can try

  INSERT INTO technicians (techID, tech_surname, tech_firstname, tech_loginid, tech_passwrd, tech_accesslevel) SELECT GetTechId(),surname, firstname, loginid , passwrd, accesslevel FROM users u WHERE NOT EXISTS (select 1 from technicians t where t.loginid=u.loginid) and u.accesslevel='tech'.