CODEWITHSUNDEEP
mysqlsqlinsertwhere-clause
chronos
chronos

Reputation: 355

mysql insert with value, with selected data from another table

I have a MySQL problem I can not get to solve. I have a mysql to manage a virtual user dovecot installation, that uses two tables (one for the aliases, another for the domains).

The table aliases has these fields: domain_id(INT), source(VARCHAR), destination(VARCHAR), whereas table domains has only two fields: id (INT AUTO INC) and name (VARCHAR).

Although I'm able to select aliases that belong to a given domain by issuing:

SELECT valias.* FROM aliases AS valias
JOIN domains AS vdomains ON valias.domain_id=vdomains.id 
WHERE vdomains.name = "domain_name";

I can not get to work to insert a new alias, specifing the domain name. something like this:

INSERT INTO valias(domain_id, source, destination) 
    VALUES (id, 'canto', 'george') 
    SELECT id FROM aliases 
    JOIN domains AS vdomains ON aliases.domain_id=vdomains.id 
    WHERE  vdomains.name =     "domain_name";

Does somebody know how to solve this problem?

Upvotes: 1

Views: 3579

Answers (3)

RadhaKrishna
RadhaKrishna

Reputation: 312

Try this,

INSERT INTO valias(domain_id, source, destination) 
SELECT id,'canto', 'george' 
FROM aliases 
    JOIN domains AS vdomains 
        ON aliases.domain_id=vdomains.id 
WHERE  vdomains.name =     "domain_name";

Upvotes: 0

Rafi Goldfarb
Rafi Goldfarb

Reputation: 601

My experience is mainly in MS SQL Server, but I reckon it should go the same way in MySQL:

INSERT INTO valias(domain_id, source, destination)  
SELECT id, 'canto', 'george' FROM vdomains
WHERE  name = 'domain_name';

Upvotes: 2

Jarek.D
Jarek.D

Reputation: 1294

Either I'm missing something here or your query seems a bit to overingeenered. How about this:

INSERT INTO aliases(domain_id, source, destination) 
    VALUES (id, 'canto', 'george')
    JOIN domains ON domains.id = aliases.domain_id
    WHERE domains.name = 'domain name'

Upvotes: 0

Related Questions