Update with Select - getting error

[snufse1]

Have following query:

update #table1
set qty = gross
from
(
select cast(sum(gross) as decimal(15, 2))
from tp6000.dbo.loadcomp
where branded = @ProdVar and
(@DateFrom IS Null or date >= @DateFrom) and
(@DateTo IS Null or date <= dateadd(day, 1, @DateTo))

)
as l where l.branded = #table1.product_code

Giving me error:

Invalid column name 'branded'.

 

[markros]

What is your SQL Server version?

update #table1
set qty = gross
from #table1 INNER JOIN
(
select cast(sum(gross) as decimal(15, 2)) as Gross, Branded
from tp6000.dbo.loadcomp
where branded = @ProdVar and
(@DateFrom IS Null or date >= @DateFrom) and
(@DateTo IS Null or date <= dateadd(day, 1, @DateTo) group by Branded)

)
as l ON l.branded = #table1.product_code

 

[snufse1]

Using 2005.

Getting error:

Incorrect syntax near the keyword 'group'.

 

[snufse1]

Got it working, was missing a ")". Thank you very much.

update #Table1
set qty = gross
from #Table1 INNER JOIN
(
select branded as Branded,
cast(sum(gross) as decimal(15, 2)) as Gross
from tp6000.dbo.loadcomp
where branded = @ProdVar and
(@DateFrom IS Null or date >= @DateFrom) and
(@DateTo IS Null or date <= dateadd(day, 1, @DateTo))
group by branded, gross

)
as l ON l.branded = #table1.product_code

 

[bborissov]

Some brackets fixing of markros code:

[COLOR=blue]update[/color]       #table1
[COLOR=blue]set[/color]          qty = gross
[COLOR=blue]from[/color] #table1
[COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color] ([COLOR=blue]select[/color] [COLOR=#FF00FF]cast[/color](sum(gross) [COLOR=blue]as[/color] [COLOR=blue]decimal[/color](15, 2)) [COLOR=blue]as[/color] Gross, Branded
                   [COLOR=blue]from[/color]     tp6000.dbo.loadcomp
             [COLOR=blue]where[/color] branded = @ProdVar and
                   (@DateFrom [COLOR=blue]IS[/color] Null or [COLOR=blue]date[/color] >= @DateFrom) and
                   (@DateTo [COLOR=blue]IS[/color] Null or [COLOR=blue]date[/color] <= [COLOR=#FF00FF]dateadd[/color]([COLOR=#FF00FF]day[/color], 1, @DateTo))
             [COLOR=blue]group[/color] [COLOR=blue]by[/color] Branded) [COLOR=blue]as[/color] l 
        [COLOR=blue]ON[/color]    l.branded = #table1.product_code

But Not tested again

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[markros]

You're welcome. Are you sure you need to group by Gross too? Seems to me you only need to group by Branded

 

[snufse1]

Now, looks like gross is not being added up for the month

update #Table1
set qty = gross
select branded as product,
cast(sum(gross) as decimal(15, 2)) as quantity
from #Table1, tp6000.dbo.loadcomp
where (branded = @Product) and
(@DateFrom IS Null or date >= @DateFrom) and
(@DateTo IS Null or date <= dateadd(day, 1, @DateTo))



select * from #Table1

 

[snufse1]

markros,

You are totally right. Do not need group by "gross". I removed and now it is totaling correcly. May fault. Thank you.

 

[markros]

Hey Boris,

Nice coloring - I'm still too lazy to set it up.

 

[bborissov]

It's easy (when you have a mood for this)
Just copy and paste from SSMS to George's application and you'll get it.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.