adding values to columns

buzmay · #1 (**permalink**) 01-17-12, 08:23

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

Wim · #2 (**permalink**) 01-17-12, 09:48

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.

buzmay · #3 (**permalink**) 01-17-12, 09:55

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?

Brett Kaiser · #4 (**permalink**) 01-17-12, 10:23

Yes, it's an Alias...and you NEED to do it that way to do a join update

buzmay · #5 (**permalink**) 01-17-12, 10:30

thanks

char 5

Wim · #6 (**permalink**) 01-17-12, 11:07

Quote:

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).

buzmay · #7 (**permalink**) 01-17-12, 11:33

Wim, million thanks I appreciate this a lot

Internet Services

Web Development

Digital Marketing

Consumer Tech