logo

$5
How to use WHERE clause with mysqldump

I've tried this:

mysqldump --user=xxx --password=xxx databaseName --tables user_answer final_import_course_evaluation --where "user_answer.user_answer_id=final_import_user_answer.user_answer_id

and user_answer.user_id=final_import_user_answer.user_id

and user_answer.question_id=final_import_user_answer.question_id

and user_answer.class_id=final_import_user_answer.class_id

and user_answer.course_id=final_import_user_answer.course_id

and user_answer.answer=final_import_user_answer.answer

and user_answer.correct=final_import_user_answer.correct

and user_answer.quiz_order=final_import_user_answer.quiz_order

and user_answer.exam_order=final_import_user_answer.exam_order"   >  /home/dev/final_import_user_answer.sql


and I get this error:

mysqldump: Couldn't execute 'SELECT /*!40001 SQL_NO_CACHE */ * FROM `user_answer` WHERE user_answer.user_answer_id=final_import_user_answer.user_answer_id

and user_answer.user_id=final_import_user_answer.user_id

and user_answer.question_id=final_import_user_answer.question_id

and user_answer.class_id=final_import_user_answer.class_id

and user_answer.course_id=final_import_user_answer.course_id

and user_answer.answer=final_import_user_answer.answer

and user_answer.correct=final_import_user_answer.correct

and user_answer.quiz_order=final_import_user_answer.quiz_order

and user_answer.exam_order=final_import_user_answer.exam_order': Unknown column 'final_import_user_answer.user_answer_id' in 'where clause' (1054)


Am I only able to select from one table when using mysqldump? If so, this is pretty useless.



Lawrence Krubner | 07/20/10 at 11:10am | Edit

mysqldump

(2) Possible Answers Submitted...

  • avatar
    Last edited:
    07/20/10
    1:35pm
    Steven Siebert says:

    Hi Lawrence,

    No, this is not possible with mysqldump.

    You have two options:
    1 - use SELECT INTO OUTFILE and generate a CSV file which you can import using a LOAD DATA INFILE (http://dev.mysql.com/doc/refman/5.1/en/load-data.html)
    2 - first generate a temp table using SELECT INTO (http://dev.mysql.com/doc/refman/5.1/en/select.html) with your join. Then, mysqldump that file. Finally, drop the temp table. You can actually do all these actions from the command line and even create a script to do this for you on a regular basis, if necessary.

    If you're looking for INSERT statements, rather than a CSV file, like you said in your last post....the second way is to go.

    Regards,

    Steve

  • avatar
    Last edited:
    07/20/10
    11:30am
    Bill Hunt says:

    Yeah, you can't do joins in a mysqldump. You might have a look at the SELECT INTO OUTFILE or CREATE from SELECT syntax to do what you want.

This question has expired.





Current status of this question: Completed