Ad
Move Records With Unique Field Into 2nd Table
I have 2 tables. table_a
is current data, table_b
has updated data. The 1st thing I need to do is move all new records, i.e., move the records from table_a
to table_b
that have a value in a primary index field (primaryField
) not found in table_a
.
I've tried variations of the following:
INSERT INTO table_b (`col1`,`col2`,`col3`,etc...)
VALUES (`col1`,`col2`,`col3`,etc...)
FROM table_a
WHERE table_a.primaryField NOT IN (SELECT table_b.primaryField)
This approach doesn't work. How do you select only the rows in a table that have values for a specific field not found in the matching field of a 2nd table?
Ad
Answer
You can LEFT JOIN
table_a
to table_b
and then insert only those records in table_a
which do not match anything in table_b
.
INSERT INTO table_b (col1, col2, col3)
SELECT a.col1, a.col2, a.col3
FROM table_a a LEFT JOIN table_b b ON a.primaryField = b.primaryField
WHERE b.primaryField IS NULL
Ad
source: stackoverflow.com
Related Questions
- → I can't do a foreign key, constraint error
- → How to implement DbDongle::convertTimestamps as workaround of invalid timestamps with MySql strict
- → MySQL error "Foreign key constraint is incorrectly formed"
- → Eloquent Multitable query
- → "Laravel 5.1" add user and project with userId
- → Database backup with custom code in laravel 5 and get the data upto 10 rows from per table in database
- → Laravel 5.1 QueryException when trying to delete a project
- → Using Array in '->where()' for Laravel Query Building
- → Chaining "Count of Columns" of a Method to Single Query Builder
- → Laravel Eloquent Joining Strange query
- → convert time using mysql laravel 5
- → How to update a column after an expiration date in MySQL?
- → Foreign key constraint fails on existing key
Ad