Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2012
    Posts
    52

    Unanswered: adding values to columns

    Hi guys,

    I have a table with 6 columns. which we can call a, b, c, d, e, f
    what I want to achieve is to put data in column d and e and then split this result in column f.

    The data I want to put in column d and e is already exported and executed from a table called exp_data, which is from a period of november.


    So this is what i have so far but is not working:

    update split_table set d =
    select amount from exp_data
    where period = '1111'
    and exp_data.account = split_table.b
    and exp_data.company = split_table.a

    the error I get is incorrect syntax near select any ideas?

    Thank in advance.

    Fixed the issue by adding a parenthesis before the select until the end, thanks anyhow
    Last edited by buzmay; 01-17-12 at 09:29. Reason: fixed

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Try this:
    Code:
    update U
    set d = exp_data.amount
    FROM split_table AS U
    	INNER JOIN exp_data ON
    		U.b = exp_data.account
    		and U.a = exp_data.company
    where period = '1111'
    Can you put your code between [CODE] [/CODE ] tags? It makes things easier to read for us.
    Last edited by Wim; 01-17-12 at 11:27.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Jan 2012
    Posts
    52
    Sorry I didnt know but I will do that from now on.

    What do you mean by Update u??

    Is it update the table name?

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Yes, it's an Alias...and you NEED to do it that way to do a join update
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jan 2012
    Posts
    52
    thanks

    char 5

  6. #6
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    What do you mean by Update u??
    (I have corrected an error in my previous post. )

    A basic UPDATE statement is something like this:
    Code:
    UPDATE TableName
    SET AColumn = 'A New Value'
    WHERE SomeCondition = 'True'
    In your case, one first needs to do a JOIN to retrieve the new values of the column that you want to update. If we would write the JOIN with a SELECT statement, we get: (*)
    Code:
    SELECT exp_data.amount
    FROM split_table 
    	INNER JOIN exp_data ON
    		split_table.b = exp_data.account
    		and split_table.a = exp_data.company
    where period = '1111'
    When working with tables in SQL, we can give them an "alias" name. In some occasions it is done to make the table names shorter or more meaningful. Assigning alias names is optional here.
    In some cases we need to JOIN a table with itself (e.g. if the table Employee would also have a column "Boss_ID", that references the Employee who is someone's boss), in that case we would have to make a distinction somehow to tell the Employee that we consider as a Boss apart from the Employee that we consider as the Minion. Here we MUST use aliases: e.g. Minion and Boss.
    Code:
    SELECT Minion.Employee_ID, Minion.Name, 
          Boss.Employee_ID, Boss.Name
    FROM Employee AS Minion 
       INNER JOIN Employee AS Boss ON 
          Minion.Boss_ID = Boss.Employee_ID
    We could rewrite the above code (*) as:
    Code:
    SELECT exp_data.amount
    FROM split_table as U 
    	INNER JOIN exp_data ON
    		U.b = exp_data.account
    		and U.a = exp_data.company
    where period = '1111'
    without changing anything of how the query performs.

    We can now rewrite this as an UPDATE statement:
    Code:
    UPDATE U
    SET U.d = exp_data.amount
    FROM split_table AS U
    	INNER JOIN exp_data ON
    		U.b = exp_data.account
    		and U.a = exp_data.company
    where period = '1111'
    It is best practice to use an alias for the table that has to be updated and name it "U". So it is very clear for you and all others who will ever see that code, what table will be updated and how it is JOINED with the other table(s).
    Last edited by Wim; 01-17-12 at 12:10. Reason: Sorry, didn't knew Brett had already anserwed it.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  7. #7
    Join Date
    Jan 2012
    Posts
    52
    Wim, million thanks I appreciate this a lot

Posting Permissions

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