MYSQL Create table with Unique values

Question

I am trying to UPDATE my table from another table. I would like to ignore duplicates and also delete any matches in that table.

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

For example, Table 2 will only have the unique entry after "Source Table" is compared to Table 1. I am using MYSQL/PHP.

Any direction is greatly appreciated.

if table 2 was empty, then you can't update. update cannot produce new records. you'd have to INSERT the bob record. — Marc B
– Marc B, Commented Feb 9, 2015 at 21:56
@ethrbunny Yes, Table 2 should have the Unique value. Table 2 is ultimately the table used in conjunction with a ticketing system (To avoid duplicate tickets) — cableguy
– cableguy, Commented Feb 9, 2015 at 22:02

Fabien TheSolution · Accepted Answer · 2015-02-09 22:08:01Z

1

SQL Fiddle

MySQL 5.5.32 Schema Setup:

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)

Query 1:

SELECT *
FROM Table2

Results:

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

answered Feb 9, 2015 at 22:08

Fabien TheSolution

5,0501 gold badge20 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

cableguy Over a year ago

Thanks so much. That makes perfect sense and was exactly what I was looking for.

Collectives™ on Stack Overflow

MYSQL Create table with Unique values

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related