Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2012
    Posts
    5

    Unanswered: mysql query problem

    Hi everybody i am new to this forum ..i hope i would get a lot of knowledge from you people...i have two problems regarding mysql server databse

    1)
    i have dumped database file ..lets say it data1.sql
    it has following tables with colums in it................ post(POST_ID,THREAD_ID,USER,COMMENT)
    thread(THREAD_ID,THREAD_TOPIC)

    i want to import these colums data into corresponding data in new schema with ...

    post(POST_ID,THREAD_ID,USER_ID,COMMENT,RANK1,RANK2 )
    thread(THREAD_ID,THREAD_TOPIC)
    user(USER_ID,USER)

    please help me....

    2) whats problem with this query..i get error with i execute it

    update new_table n set n.THREAD_ID= (select THREAD_ID form post p where p.POST_ID=n.POST_ID)


    i hope u all guys would help me...

    thanx

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    and the error message is?
    have you verified that you have succesfuly imported the schema and the data?
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Sep 2012
    Posts
    5

    error message

    0 21:05:48 insert into new_table(THREAD_ID) select p.THREAD_ID from post p where p.POST_ID=20 Error Code: 1364. Field 'POST_ID' doesn't have a default value



    (i m using mysql workbench)

  4. #4
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    This means that you are running the INSERT statement but the table definition is saying that POST_ID is NOT NULL (most likely the primary key) and that it is expecting a value to be provided. This usually happens when you create the primary key without specifying AUTO_INCREMENT.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  5. #5
    Join Date
    Sep 2012
    Posts
    5
    there is red line under post p

  6. #6
    Join Date
    Sep 2012
    Posts
    5
    any idea for first problem?

  7. #7
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    If you are using mysqldump to generate the dump file as follows:

    Code:
    shell> mysqldump database > dump.sql
    By default this will not include a CREATE DATABASE statement in the dump file. To restore this just run

    Code:
    shell> mysql -D newdbname < dump.sql
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  8. #8
    Join Date
    Sep 2012
    Posts
    5
    i m using command prompt and secondly look both have difference in tables so we cant dump one file to other ..is it?

  9. #9
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    I am assuming that this is a one off load of the database? If it is something that needs to be automated then look at creating staging tables and load data into the staging table. A second phase is then needed to migrate the data into the live tables using INSERT ... SELECT statements.

    If this is a one time operation, you could load the tables and then using ALTER TABLE statement modify the structure. For example, you will need to do the following for the first table:

    ALTER TABLE post ADD USER_ID INT UNSIGNED, ADD RANK1 INT, ADD RANK2 INT;

    We then need to populate the USER_ID field with the value of the USER field.

    UPDATE post SET USER_ID = (SELECT USER_ID FROM users WHERE post.USER = users.USER);

    Finally we can remove the USER field from the table, with another ALTER TABLE ... DROP statement:

    ALTER TABLE post DROP USER;
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •