mysql insert with value, with selected data from another table

Question

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?

If i understood properly then you have used INSERT INTO valias where "valias" is not any table. So I think you can't used that. Insert statement must have either tablename or viewname. — user2510115
– user2510115, Commented Jul 20, 2013 at 16:32

Rafi Goldfarb · Accepted Answer · 2013-07-20 16:46:05Z

2

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';

answered Jul 20, 2013 at 16:46

Rafi Goldfarb

6015 silver badges9 bronze badges

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

Comments

Jarek.D · Accepted Answer · 2013-07-20 16:41:16Z

0

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'

answered Jul 20, 2013 at 16:41

Jarek.D

1,2941 gold badge9 silver badges19 bronze badges

Comments

C B · Accepted Answer · 2018-02-02 20:35:01Z

0

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";

edited Feb 2, 2018 at 20:35

C B

1,7176 gold badges20 silver badges20 bronze badges

answered Jul 20, 2013 at 16:47

RadhaKrishna

3123 silver badges13 bronze badges

Collectives™ on Stack Overflow

mysql insert with value, with selected data from another table

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related