SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Wizard
    Join Date
    May 2002
    Posts
    1,370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    statement to migrate table values

    Re-organizing the tables of a site, I need to copy existing data over from 'JobPost' table to 'JobRun' table

    Existing column values I need to copy over are:

    'JobPost' | 'JobRun'

    JobID ------> FK_JobID
    FK_EmpID ---> FK_EmpID
    JobPosted --> JobPosted
    JobStartRun-> JobStartRun
    JobEndRun --> JobEndRun
    JobRuntime -> JobRuntime

    Would like to do so through phpmyadmin at the sql line.
    How can I order this statement?

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Code:
    insert into jobrun
    (fk_jobid, jobposted, ...)
    select jobid
         , fk_empid
         , ...
      from jobpost
    fill in the ...

  3. #3
    SitePoint Wizard
    Join Date
    May 2002
    Posts
    1,370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks longneck,

    Since this inserts, starting from the last row of JobPost, (JobID /the rowid is being inserted with an order that varies into the new JobRun table)

    Tried attaching an ORDER BY to JobPost at the end of the statement without success. How can this go so the JobPost.JobID order is maintained?

    insert into JobRun
    (FK_JobID, FK_EmpID, JobPosted, JobStartRun, JobEndRun, JobRuntime)
    select JobID
    ,FK_EmpID
    ,JobPosted
    ,JobStartRun
    ,JobEndRun
    ,JobRuntime
    from JobPost ORDER BY JobID

  4. #4
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    697
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    The order in which records are inserted does not matter. It is only when doing a select you can affect the order by specifying an order by clause.

  5. #5
    SitePoint Wizard
    Join Date
    May 2002
    Posts
    1,370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    My table rows that hav auto-increment always appear in order, when I have to go in manually to administer. Just trying to keep this nice and tidy, swampBoogie

  6. #6
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    you might be able to do it like this, but i advise against it as depending on records being maintained in a certain physical order is a bad idea and a bad habit to get in to, and breaking bad habits is difficult:
    Code:
    insert into JobRun
    (FK_JobID, FK_EmpID, JobPosted, JobStartRun, JobEndRun, JobRuntime)
    select *
      from (select JobID
                 , FK_EmpID
                 , JobPosted
                 , JobStartRun
                 , JobEndRun
                 , JobRuntime
              from JobPost
            ORDER
                BY JobID) dt

  7. #7
    SitePoint Wizard
    Join Date
    May 2002
    Posts
    1,370
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What is dt?

  8. #8
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    whenever you use a derived table (i.e., a subquery that generates rows for a from or join clause) you must give it an alias. dt = derived table


Bookmarks

Posting Permissions

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