MYSQL创建具有唯一值的表 - MYSQL Create table with Unique values

MYSQL Create table with Unique values

本文关键字：唯一创建 MYSQL | 更新日期: 2023-09-27

我正在尝试从另一个表更新我的表。我想忽略重复项，同时删除该表中的所有匹配项。

Source Table          Table 1              Table 2
Customer | Address    Customer | Address   Customer | Address
__________________    __________________   __________________
Mike      123 Main    Mike      123 Main     Bob      999 1st 
Steve     456 Maple   Steve     456 Maple
John      789 Elm     John      789 Elm
Bob       999 1st

例如，在将"源表"与表1进行比较之后，表2将仅具有唯一条目。我使用的是MYSQL/PHP。

任何方向都将不胜感激。

SQL Fiddle

MySQL 5.5.32架构设置:

CREATE TABLE Source
    (`Customer` varchar(5), `Address` varchar(9))
;
INSERT INTO Source
    (`Customer`, `Address`)
VALUES
    ('Mike', '123 Main'),
    ('Steve', '456 Maple'),
    ('John', '789 Elm'),
    ('Bob', '999 1st'),
    ('Bob', '999 1st')
;
CREATE TABLE Table1
    (`Customer` varchar(5), `Address` varchar(9))
;
INSERT INTO Table1
    (`Customer`, `Address`)
VALUES
    ('Mike', '123 Main'),
    ('Steve', '456 Maple'),
    ('John', '789 Elm')
;
CREATE TABLE Table2
    (`Customer` varchar(5), `Address` varchar(9))
;
DELETE FROM Table2;
INSERT INTO Table2
    (`Customer`, `Address`)
SELECT DISTINCT Customer, Address
FROM Source
WHERE NOT EXISTS (SELECT 1 FROM Table1 WHERE Source.Customer = Table1.Customer)

查询1：

SELECT *
FROM Table2

结果：

| CUSTOMER | ADDRESS |
|----------|---------|
|      Bob | 999 1st |