需要用同一数据库中另一张表的数据填充一张表 [英] Need to populate one table with data from another table in same database

问题描述

我正在尝试使用 mysql 从另一个表中填充一个表.第一个表是用户，第二个表是技术人员.

I am trying to populate one table from another table with mysql. First table is users and the second table is technicians .

用户包含:userID, surname, firstname, loginid, password, accesslevel .

Users contains : userID, surname, firstname, loginid, password, accesslevel .

技术人员包含:techID、tech_surname、tech_firstname、tech_loginid、tech_password、tech_accesslevel.

Technicians contains : techID, tech_surname, tech_firstname, tech_loginid, tech_password, tech_accesslevel.

当我添加用户时，如果 accesslevel=tech 和 users.loginid 不等于 Technicals.tech_loginid，我希望填充技术人员表.

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

我尝试了几件事，结果要么没有添加记录，要么每次都添加了 accesslevel=tech 的用户中的所有记录，给了我几个重复的记录.

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

我试过了:

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'

它不起作用，如果我从 WHERE 子句中删除 AND 语句，它会在每次使用 accesslevel=tech 添加新用户时推送所有记录.

It doesn't work and if I remove the AND statement from the WHERE clause it pushes all the records each time a new user is added with accesslevel=tech.

我做错了什么?我已经搜索了几个小时的答案.

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

干杯

推荐答案

我认为您正在尝试插入那些已经不存在于该表中的技术人员，因此查询将是这样的

I think you're trying to insert those technicians that already do not exist in that table so a query would 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

这篇关于需要用同一数据库中另一张表的数据填充一张表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！