Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2006
    Posts
    66

    Unanswered: insert from one table into another

    i have


    insert into membersarchive
    select firstname,lastname from members


    how can i do this but I also want to add an additional field not in members called username and add this to the values being inserted?

  2. #2
    Join Date
    Aug 2005
    Posts
    30
    You have to specify from where you want to pick username value and fieldnames explicitly in Insert into query; it will look somewhat like this, where users is another table for example from where you have to pick username:

    insert into membersarchive (firstname,lastname,username)
    select firstname,lastname,username from members,users where members.userid=users.userid

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by esthera
    I also want to add an additional field not in members called username and add this to the values being inserted
    Where is this extra value held?
    Without knowing this information we can't help you!
    Here's a quick quess
    Code:
    INSERT INTO membersarchive (firstname,lastname,username)
    SELECT	 firstname
    	,lastname
    	,Left(surname,6) + Left(firstname,2)
    FROM	 members
    George
    Home | Blog

  4. #4
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Where are you getting the variable username from? Is it coming in from a script? Is it coming from another table? Do you want to insert a blank?

    Blank :
    Code:
    INSERT INTO 
      membersarchive(firstname,lastname,username)
    SELECT firstname,lastname,'' FROM members
    Variable from script (php) (foreach user)
    Code:
    INSERT INTO 
      membersarchive(firstname,lastname,username)
    SELECT firstname,lastname,'{$php_username}' FROM members WHERE <some clause>
    From another table
    Code:
    INSERT INTO
      membersarchive (firstname,lastname,username)
    SELECT firstname,lastname,username from members m
    JOIN users u ON m.u=u.userid

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Tiny amendment in Aschk's code (aliases)
    Code:
    INSERT INTO membersarchive(firstname,lastname,username)
    SELECT	m.firstname, m.lastname, u.username
    FROM	members m
    JOIN users u
    	ON m.u=u.userid
    George
    Home | Blog

Posting Permissions

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