msql insert data into table based on values in two other tables

Question

I am using MySQL and have a table (documentShips) that I want to store connections / links between documents and users.

The users table has columns including id, first_name and last_name etc...
The documents table has columns including id and users, where the users column contains a comma separated value
- E.g. "Joe Bloggs, Fred Nerk, Simon McCool" etc...

I want to match users between the tables (documents and users) using a like statement, e.g.:

where documents.authors like '% users.last_name %'

and insert them into the documentShips table, e.g.:

insert into documentShips (user_id, document_id) ... values () ... where ...

I am struggling to create a valid (mysql) insert statement to do this.

Any help would be greatly appreciated !!!

Thanks, Jon.

syntex is Insert Into documentShips (id,..) select @id.. where condition. if you are using values i dont think you can use where clause. — AJP, Oct 23 '13 at 14:57

score 0 · Answer 1 · answered Oct 23 '13 at 15:15

0

Use INSERT...SELECT syntax as shown in the MySQL documentation. The documentation also has some examples.

answered Oct 23 '13 at 15:15

trf

1,279
1
13
33

score 0 · Accepted Answer · answered Oct 23 '13 at 15:21

If I understand correctly you can use FIND_IN_SET() like this

INSERT INTO documentShips (user_id, document_id)
SELECT u.id, d.id 
  FROM documents d JOIN users u
    ON FIND_IN_SET(CONCAT(u.first_name, ' ', u.last_name), d.authors) > 0
 ORDER  BY d.id, u.id

Here is SQLFiddle demo

In order for it to work correctly you have to make sure that comma separated values in document.authors have no spaces before or after commas. If in fact you have spaces then eliminate them first with a query like this

UPDATE documents
   SET authors = REPLACE(REPLACE(authors, ' ,', ','), ', ', ',');

Here is SQLFiddle demo

Now consider to normalize your documents table.

msql insert data into table based on values in two other tables

2 Answers2