MySQL INSERT after JOIN

Question

I have two tables as shown below.

table1

site | Link type
-----+----------
 A   | pdf
 B   | html
 C   | NULL
 D   | NULL

Table2

site | link type
-----+----------
 C   | htm
 D   | doc

This is the result I want:

site | link type
-----+----------
 A   | pdf
 B   | html
 C   | htm
 D   | doc

I want an insert query to insert the values of link type from table 2 to table 1
where link type is null joined with the condition with of table1.site = table2.site.

I tried:

INSERT INTO table1(linktype)
SELECT linktype FROM table1 t1 
JOIN table2 t2 
ON t1.site=t2.site

I want a insert query. as update query is working and want to know how a insert can be done?

See: http://stackoverflow.com/questions/1382842/mysql-insert-joins — Ocaso Protal, Apr 29 '11 at 10:00

rzetterberg · Accepted Answer · 2011-04-29T11:48:35.603

0

Edit: Completely edited after clarification:

Creation of table1:

SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS `table1`;
CREATE TABLE `table1` (
  `site` varchar(250) DEFAULT NULL,
  `linktype` varchar(250) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


INSERT INTO `table1` VALUES ('A', 'pdf');
INSERT INTO `table1` VALUES ('B', 'html');
INSERT INTO `table1` VALUES ('C', null);
INSERT INTO `table1` VALUES ('D', null);

Creation of table2:

SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS `table2`;
CREATE TABLE `table2` (
  `site` varchar(250) DEFAULT NULL,
  `linktype` varchar(250) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `table2` VALUES ('C', 'htm');
INSERT INTO `table2` VALUES ('D', 'doc');

Insert query:

INSERT INTO 
    table1  
    (site, linktype)
    (
       SELECT 
           table2.site, 
           table2.linktype
       FROM 
           table2
       JOIN 
           table1
       ON 
           table1.site = table2.site
   )
;

Table1 after insert query:

Table1

edited Apr 29 '11 at 11:48

answered Apr 29 '11 at 10:00

rzetterberg

10,146
4
44
54

I am using mysql so tried the second solution. It is not working. The value , htm and doc is getting added as new rows with null value in the site column. Thanks for your help. – navzit Apr 29 '11 at 10:57
I can do it with update query: " update table1 a join table2 b on a.site=b.site set a.linktype=b.linktype where a.linktype is null" ANyone can tell if insert is possible or not ? – navzit Apr 29 '11 at 11:00
So all you want to do is to make a copy of all linktypes in table1 that exists in table2? – rzetterberg Apr 29 '11 at 11:13
See my edit. I have tested the code in my mysql-database and it gives the result as in your image. – rzetterberg Apr 29 '11 at 11:19
Sorry still the same result. You have not added the condition that site column should match. Thanks. – navzit Apr 29 '11 at 11:27
http://dl.dropbox.com/u/27669228/table2.sql http://dl.dropbox.com/u/27669228/table1.sql sql dump of two tables for testing – navzit Apr 29 '11 at 11:36
Alright, I'm giving up. You say you want to just move "htm" and "doc" from table2 to table1 BUT you also say they have to match when there aren't any matches since "htm" and "doc" doesn't exist in table1. If you can't give me good explanation of what you want to achieve then I can't help you. – rzetterberg Apr 29 '11 at 11:36
Sorry for confusing you. The match should be for column name "site". Thanks for your help – navzit Apr 29 '11 at 11:37
I am afraid , I can not accept it. I need http://i.imgur.com/jAklc.png You can try the update command I have written in my second comment. I appreciate your help.My question is whether the same can be done with "insert command" – navzit Apr 29 '11 at 12:00
Then the answer is no. Insert is only used for adding new rows. If you are going to get the same result you first need to delete to Ones that matches and then insert the new ones... – rzetterberg Apr 29 '11 at 12:26
Thanks for your answer , I made an error in thinking that insert can be used with where clause. I accept you solution. More information about my problem can be found in http://stackoverflow.com/questions/485039/mysql-insert-where-query Thanks again – navzit Apr 29 '11 at 13:45

MySQL INSERT after JOIN

1 Answers1