How can I SELECT+INSERT in the same mysql query?

Question

I have a php script that process keywords from a mysql table "keywords" (columns: id - keyword) and then it saves data into another table "data" (column: id[foreign key keywords.id] - dataname - datavalue).

My problem is that when the script is ready to save the data I only have the keyword and not the id.

So is there a way I can get the keyword id and save the data in one mysql query? (i mean without having to do something like SELECT id from keywords where keyword = keyword, and then run another query for the INSERT.

zerkms · Accepted Answer · 2010-09-06 03:36:28Z

9

If selecting and modification tables are different - you can use simple nested query:

INSERT INTO `data` (`id`, `dataname`) VALUES
(
    (SELECT `id` FROM `keywords` WHERE `keyword` = 'keyworrrrrd'),
    'blabla'
)

or

INSERT INTO `data` (`id`, `dataname`)
SELECT `id`, 'blabla' FROM `keywords` WHERE `keyword` = 'keyworrrrrd'

answered Sep 6, 2010 at 3:36

zerkms

256k73 gold badges447 silver badges551 bronze badges

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

2 Comments

jarkam Over a year ago

Thanks i think that will do the job.

zerkms Over a year ago

@Frankie: i've written in that way to differ keyword as field and specific keyword in table ;-)

Collectives™ on Stack Overflow

How can I SELECT+INSERT in the same mysql query?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related