Run a loop for each result in mysql

Question

I have the following query

SELECT @var:= meta_value, @no:=post_id FROM tbl WHERE `meta_key` ="_wpbdp[fields][10]"

now this gets around 500 results..
I want to run another INSERT query for each value of @var and @no variable.

Example::

 INSERT INTO tbl ('value','id') VALUES (@var,@no);

How do i get this in a LOOP??

Won't a INSERT...SELECT work?

shree.pat18
– shree.pat18

2014-07-21 09:15:43 +00:00
Commented Jul 21, 2014 at 9:15 — shree.pat18
– shree.pat18, Commented Jul 21, 2014 at 9:15

fancyPants · Accepted Answer · 2014-07-21 10:03:31Z

1

You don't need a loop, and btw, these variables will always hold just one value.

INSERT INTO tbl(`value`, `id`)
SELECT meta_value, post_id FROM tbl WHERE `meta_key` = "_wpbdp[fields][10]";

Also note, that this

INSERT INTO tbl ('value','id')

is wrong, because with ' you make value and id strings, not column names.

Read more about possible insert syntaxes here.

edited Jul 21, 2014 at 10:03

answered Jul 21, 2014 at 9:16

fancyPants

52.1k34 gold badges94 silver badges100 bronze badges

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

7 Comments

user1994082 Over a year ago

ok, i used the following code, but it says syntax error, pls suggest the error? SELECT meta_value, post_id FROM yr8tu_postmeta WHERE meta_key = "_wp_attached_file" INSERT INTO yr8tu_posts(ID, post_title, post_excerpt, post_status, comment_status, ping_status, post_password, post_name, post_parent, guid, menu_order, post_type, post_mime_type, comment_count) VALUES (,Replace(meta_value,".jpg",""),,"publish","open","open",,Replace(meta_value,".jpg",""),post_id,"","0","attachment","image/jpeg","0")

fancyPants Over a year ago

Please read my answer again, it says insert into tbl(col1, col2) select col1, col2 from tbl, not select col1, col2 from tbl insert into.... I added the official syntax in my answer.

fancyPants Over a year ago

Because you have no ; between your statements. So MySQL thinks that it's one statement, but a SELECT ... INSERT doesn't exist.

fancyPants Over a year ago

Also you have a comma too much here: VALUES (,

fancyPants Over a year ago

So does it work now? If yes, feel free to accept my answer by clicking the check to the left of my answer, so that others now that the issue is solved.

|

Collectives™ on Stack Overflow

Run a loop for each result in mysql

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related