-2

I am using MySQL 5.6

The field "contract" is unique in all three tables.

Here is what I got so far

Database setup:

CREATE TABLE IF NOT EXISTS Active_Orders (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
contract VARCHAR(255) NOT NULL UNIQUE
)  ENGINE=INNODB;

INSERT INTO Active_Orders(name, contract)
VALUES ('steve', '3454'),
       ('tom', '6756'),
       ('becky', '9809');

CREATE TABLE IF NOT EXISTS Old_Orders (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
contract VARCHAR(255) NOT NULL UNIQUE
)  ENGINE=INNODB;

INSERT INTO Old_Orders(name,contract)
VALUES ('mark', '9896'),
       ('kelly', '0897'),
       ('paul', '1537'),
       ('will', '8254');

CREATE TABLE IF NOT EXISTS Move_Orders (
contract VARCHAR(255) NOT NULL UNIQUE
)  ENGINE=INNODB;

INSERT INTO Move_Orders(contract)
VALUES ('0897'),
       ('1537);

The Code I am using is:

INSERT INTO Active_Orders (name, contract)  
SELECT name, contract
FROM Old_Orders
WHERE Move_Orders.contract = Old_Orders.contract;

But I am getting

MySQL said: Documentation

#1054 - Unknown column 'Move_Orders.contract' in 'where clause'

What I want is:

SELECT * FROM Active_Orders;

id  name    contract
1   steve   3454
2   tom     6756
3   becky   9809
4   kelly   0897
5   paul    1537

Note:

 TableA is Active_Orders
 TableB is Old_Orders
 TableC is Move_Orders
  • 3
    The error message is pretty clear. MySQL doesn't know what is TableC. – Eric Jan 14 at 18:16
  • TableA, TableB, TableC is not a good name to use. Without you explaining the relationship between those tables, how do you expect us to help you??? If you have a meaningful name like Order, OrderDetail, Customer, then you can omit the explanation. – Eric Jan 14 at 18:18
  • 1
    MySQL doesn't know what is TableC. More precisely - TableC is not listed in FROM clause. – Akina Jan 14 at 18:39
  • 1
    The term you're looking for is "join". – mustaccio Jan 14 at 18:48
  • Please give your table structures as DDL (CREATE TABLE foo (...);) and your data as DML (INSERT INTO foo VALUES (...);) - or better yet, as a fiddle! Also, please always include your version of MySQL - it can be critical for answers as they've introduced a lot of important functionality in the last while. p.s. welcome to the forum! :-) – Vérace Jan 14 at 21:00
0

Probably the following could help:

INSERT INTO TableA (name, contract)  
SELECT name, contract
  FROM TableB
 WHERE contract IN (SELECT contract from TableC)

P.S. Your query is actually incorrect, TableC is not listed in WHERE clause and thus is unknown in the current context

|improve this answer|||||

Not the answer you're looking for? Browse other questions tagged or ask your own question.