May 22, 2008 at 8:44 am
Hello,
Could someone please help me.
I'm trying the following:
update TABLE 1
set TABLE1.FIELD1 = TABLE1.FIELD1 + TABLE3.FIELD2A
from TABLE1,(select field2a,field2b from TABLE2) as TABLE3
where TABLE1.FIELD2 = TABLE3.FIELD2b
[**TABLE3 contains 3 rows** ]
** RESULT: TABLE1.FIELD1 = TABLE3.FIELD2A (row1)
** Wanted result: TABLE1.FIELD1 = TABLE3.FIELD2A (row1) + TABLE3.FIELD2A (row2) + TABLE3.FIELD2A (row3)
Note: The number of rows of table 3 is variable
Thank you very kind for your help.
May 22, 2008 at 9:39 am
UPDATE TABLE1
SET TABLE1.FIELD1 = TABLE1.FIELD1 + TABLE2.FIELD2A
FROM TABLE1
INNER JOIN TABLE2
ON TABLE1.FIELD2 = TABLE2.FIELD2b
- Zahran -
May 22, 2008 at 9:58 am
What is the data type in TABLE3.FIELD2A and TABLE1.FIELD1 ?
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
May 22, 2008 at 10:20 am
OK. I am assuming the data type is VARCHAR because if it was numeric a simple SUM and a subquery would work.
What you may want to do is this:
create a UDF ( I am assuming the LinkField is INT but you can change it to whatever data type it is)
CREATE FUNCTION dbo.fn_ConcatString(@LinkField INT)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @SumField VARCHAR(8000)
SELECT @SumField = ISNULL(@SumField,'') + FIELD2A
FROM TABLE3
WHERE TABLE3.FIELD2b = @LinkField
RETURN @SumField
END
Then run a simple update
UPDATE TABLE1
set TABLE1.FIELD1 = TABLE1.FIELD1 + dbo.fn_ConcatString(TABLE1.FIELD2)
If I got all the syntax right this should work.
Just be carefull. You can easily blow thru the VARCHAR(8000) if you have a lot of records in TABLE3
and this might be slow on large databases.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]
May 23, 2008 at 2:48 am
Hello JacekO,
Your assumption about the varchar was correct. Your sollution works prefectly, than you very kind.
Kind regards,
Wouter
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply