mysql合并两张表某一列 mysql合并两张表为新表

转载

mob64ca14137e4f 2023-11-03 23:25:01

文章标签 mysql合并两张表某一列 php mysql两个表合并 mysql Core 数据 文章分类 MySQL 数据库

使用php和mysql将两个表合并到一个新表中(Merge two tables to a new table using php and mysql)

您好，这是我第一次发帖，但希望我不会搞砸太多。

基本上我正在尝试将两个表复制到一个新表中，表2和表3中的数据是我使用两个csv文件更新的临时数据。它只是共享相同ID的基本数据，因此主键和我希望将它们组合成一个新表。这应该是每天处理大约2000行的一次。下面是对我正在寻找的更好的描述。

3个表，Core，temp_data1，temp_data2

temp_data1有id，name，product

temp_data2有id，description

id是唯一的，因为它是产品的product_nr

首先将数据从temp_data1复制到Core。如果产品不存在，请插入新行，如果确实存在，则应使用该信息更新行

接下来使用id = id的描述更新Core，如果id不存在则不插入(它不应该存在)

我正在寻找可以在一个按钮上完成的事情，首先我将csv文件上传到两个不同的数据库(两个不同的文件)，然后按下按钮将两个表合并到Core one。我知道你可以使用两个csv文件立即执行此操作并跳过两个表，但我觉得这是我的头脑，它甚至都不好笑。

我可以处理编程php这是所有的mysql东西，搞乱了我的脑袋。

希望你们能帮助我，作为回报，我会帮助其他任何地方。

提前致谢。

Hello this is my first time i post but hopefully i won't mess up to much.

Basically i'm trying to to copy two tables into a new table, the data in table 2 and 3 are temp data that i update with two csv files. It's just basic data that share the same ID so thats the Primary Key and i want these to be combined into a new table. This is supposed to be done just once a day handling about 2000 lines Below follows a better description of what i'm looking for.

3 tables, Core, temp_data1, temp_data2

temp_data1 has id, name, product

temp_data2 has id, description

id is a unique since it's the product_nr of the product

First copy the data from temp_data1 to Core. Insert new line if the product does not exist, if it do exist it should update the row with the information

Next update Core with the description where id=id and do not insert if id do not exist (it should not exist)

I'm looking for something that can be done in one push of a button, first i upload the csv file into the two different databases (two different files) next i push a button to merge the two tables to the Core one. I know you can do this right away with the two csv files and skip the two tables but i feel like that is so over my head it's not even funny.

I can handle programming php it's all the mysql stuff that's messing with my head.

Hopefully you guys can help me and in return i will help out any other place i can.

Thanks in advance.

最满意答案

我不确定我是否理解正确，但这可以使用sql脚本完成，使用INSERT INTO...SELECT...ON DUPLICATE KEY UPDATE... - 请参阅http://dev.mysql.com/doc /refman/5.6/en/insert-select.html

I'm not sure I understand it correctly, but this can be done using only sql script, using INSERT INTO...SELECT...ON DUPLICATE KEY UPDATE... - see http://dev.mysql.com/doc/refman/5.6/en/insert-select.html

2013-11-05

相关问答

您可以使用UNION的SQL查询： SELECT ID, Name

FROM LiveTable

UNION ALL

SELECT ID, Name

FROM ArchiveTable

注意： UNION ALL将保留重复项。如果要删除重复记录，请使用UNION 。 You can use an SQL query with UNION: SELECT ID, Name

FROM LiveTable

UNION ALL

SELECT ID

...

你需要做的是加入表格。最常见的JOIN类型： INNER JOIN - 用于匹配与ON()语句匹配的数据。如果ON()不匹配，则结果将被排除。 LEFT JOIN - 用于在ON()中匹配的数据不必在那里。它只是将数据追加到原始的FROM中，并且如果没有数据匹配，则使用NULL填充列。例 SELECT

ft.id,

ft.file_name,

ft.file_description,

ft.file_url,

af.id as access_id,

af.student_id,

...

加入几对就可以了。 SELECT u.id, u.hash, uf_f.value AS firstname, uf_l.value AS lastname

FROM user AS u

LEFT JOIN user_field AS uf_f ON uf_f.user_id = u.id AND uf_f.key = 'firstname'

LEFT JOIN user_field AS uf_l ON uf_l.user_id = u.id AND uf_l.key = 'lastname'

...

我不确定我是否理解正确，但这可以使用sql脚本完成，使用INSERT INTO...SELECT...ON DUPLICATE KEY UPDATE... - 请参阅http://dev.mysql.com/doc /refman/5.6/en/insert-select.html I'm not sure I understand it correctly, but this can be done using only sql script, using INSERT INTO...SELEC

...

这是一个从db或只有一些表格制作bakups的函数 function &backup_tables($host, $user, $pass, $name, $tables = '*'){

$data = "\n/*---------------------------------------------------------------".

"\n SQL DB BACKUP ".date("d.m.Y H:i")." ".

"\n HOST: {

...

将SKU改为主键。从那里你可以做一个INSERT ... SELECT ... ON DUPLICATE KEY UPDATE查询。 I thought I would post what I eventually used for this. It's probably not the "BEST" way, and dropping the column is completely optional. ALTER TABLE `temp_import`

ADD `deleteme` tiny

...

听起来你想要结合两个表的结果。如果是这样，您可以使用UNION ALL ： SELECT surname, firstname, email, location

FROM globalusers

UNION ALL

SELECT surname, firstname, email, 'local'

FROM localusers

更新了SQL小提琴 Sounds like you're looking to combine the results of the two tables. If s

...

首先使用php文件，你需要使用exec()和mysqldump as来转储多个数据库 exec("mysqldump --user root -p --databases test1 test2 > file.sql");

这会将多个数据库导出到一个文件中，现在您可以使用将数据库导入test3 exec("mysql -u username -p test3 < file.sql");

Using php file first of all you need to take dump of m