Results 1 to 7 of 7

Thread: SQL Question

  1. #1
    Join Date
    Feb 2004
    Location
    Vermont
    Posts
    53

    Question Unanswered: SQL Question

    I know how to select fields from two different tables in SQL, but can you copy data from one field to another using SQL??

    C

  2. #2
    Join Date
    Jan 2004
    Posts
    492

    Re: SQL Question

    Originally posted by carley465
    I know how to select fields from two different tables in SQL, but can you copy data from one field to another using SQL??

    C

    If you are trying to update a row you want to do it with subqueries :
    Code:
    update table_a
    set column_a = (select column_b from table_b where flag = 'Y')
    You can also add a subquery to the where statement as well.

    If you are inserting a new row, do something like this:
    Code:
    Insert into table_a (col1, col2, col3)
    select col1, col2, col3
    from table_b
    where flag = 'Y'

    where the select statement is the rows you want to copy from the other table.

    If you need further clarification let me know.

  3. #3
    Join Date
    Feb 2004
    Location
    Vermont
    Posts
    53
    Ok but if I need to select only records from one table to the other table where the two table are joined do I need a where statement to do this???

  4. #4
    Join Date
    Jan 2004
    Posts
    492
    Originally posted by carley465
    Ok but if I need to select only records from one table to the other table where the two table are joined do I need a where statement to do this???
    If you choose to select ALL rows, then no you do not need a where statement. If you only wish to copy rows from one table based on a certain criteria, then yes.

    Example:

    Table employee
    emp_id
    ename
    sal
    job

    Table developers
    emp_id
    ename
    sal
    job


    ok - Employee holds all your employee data for every job. Table developers only holds data for your developers.

    This is a bad example, but off the top of my head its good enough to get an idea.

    If you want to copy all of the employees with developer jobs from EMPLOYEE to DEVELOPERS you would do something like this:

    Code:
    insert into developers (emp_id, ename, sal, job)
    select emp_id, ename, sal, job from
    employee where job = 'DEVELOPER'
    This would effectively select all DEVELOPER records from EMPLOYEE and insert them into DEVELOPER. This is what I think you want to do correct?

    Also should add a note to make sure the datatypes are consistent - dont try copying a date column to a varchar column... it makes the database angry

  5. #5
    Join Date
    Feb 2004
    Location
    Vermont
    Posts
    53
    If you want to copy all of the employees with developer jobs from EMPLOYEE to DEVELOPERS you would do something like this:


    code:--------------------------------------------------------------------------------
    insert into developers (emp_id, ename, sal, job)
    select emp_id, ename, sal, job from
    employee where job = 'DEVELOPER'
    --------------------------------------------------------------------------------

    What I am trying to do is insert into CurrentMonth(Faxes that are Long distance)

    select Long distance fax from

    Long Distance Faxtbl where account='Current Month'


    Is this correct....I do realize the table names are not exact, but I am trying to make sure my thinking is correct.


    The join is the Account number. If the account number doesn't match I do not want to insert anything so in looking in the table under that column a zero would appear.

    C

  6. #6
    Join Date
    Jan 2004
    Posts
    492
    I have to make some assumptions here...because I cant see the data.


    If you were to do this:

    Insert into current_month (long_fax)
    select long_fax from
    Long_Distance_Faxtbl where account='Current Month'


    You would select all of the Long faxes from the Long_distance_faxtbl where the account = 'CURRENT MONTH'. It would then insert these long faxes figures into the current_month table in the long_fax category.

    You can join tables too...


    Insert into current_month (long_fax, col2, col3, col4)
    Select l.long_fax, b.test_col, b.test_col2, b.test_col3
    from long_distance l, fake_table b
    where l.fax_id = b.fax_id
    and l.account = 'CURRENT MONTH'

  7. #7
    Join Date
    Feb 2004
    Location
    Vermont
    Posts
    53
    Ok some how on all of this I am lost. I have tried to in access take two tables, and based on basically the account is what joins the two tables, so I thought that maybe in the SQL window I could write something that access was not allowing me to do, but I'm not sure I understand what has been explained to me to help me do what I am trying to do.


    In SQL I can query the two tables and from the current Month table I pull account and the Long distance record returns all blanks and in the same query I get from the Long distance table all the records with accounts and in the Long distance column I get 3.1.

    So an example of the data is as such

    Acct CMonth.Long Dist Fax L_D_F_Tbl.Acct L_D_F_Tbl.Long dist Fax
    3001 3001 3.1


    So all I want to do is transfer the data from L_D_F_Tbl.Long dist Fax to CMonth. Long Dist Fax.

    Can you help me?

Posting Permissions

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