I have two tables, one called "members" and another called "email_digest". They have different structures. I would like to have a query that:

1. Compares both tables
2. If a member from "members" is not in "email_digest", the query fetches the member ID from "members", adds it to "email_digest" and sets a value to its "frequency" column

How could I do that?

Example:

• TABLE members HAS COLUMNS member_id AND name
• DATA IN IT: 10 (member_id) AND John (name)
• TABLE email_digest HAS COLUMNS member_id AND frequency
• DATA IN IT: EMPTY
• Since "John" from members is not in email_digest, the query would add his member_id and the value "weekly" into frequency.

CodePudding user response：

Well, you can use NOT EXISTS to check if the value does not exist in your email_digest and then use it to insert data into your email_digest table.

Though I am not sure why weekly is the only chosen frequency, you can use following query:

INSERT INTO email_digest(member_id, `frequency`)
SELECT m.`member_id`,'WEEKLY' FROM members m WHERE NOT EXISTS (
SELECT * FROM `email_digest` e WHERE e.`member_id`=m.`member_id`
)

Here is a sample:

DROP TABLE IF EXISTS `email_digest`;

CREATE TABLE `email_digest` (
  `member_id` int(11) DEFAULT NULL,
  `frequency` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Data for the table `email_digest` */


/*Table structure for table `members` */

DROP TABLE IF EXISTS `members`;

CREATE TABLE `members` (
  `member_id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Data for the table `members` */

insert  into `members`(`member_id`,`name`) values (10,'John'),(9,'Asgar');

After using the query above, it will insert the data you need.