Whats wrong with my Update statement !!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Whats wrong with my Update statement !!

Author

Topic

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2006-05-02 : 07:47:58

Am getting this error message see below
"Server: Msg 170, Level 15, State 1, Line 11"
Line 11: Incorrect syntax near ')'.

Update dbo.tbl_Voy_RCATransfer
Set ProdGroup = productgroup,
ProdOrg = productorg,
ProdSubOrg = productsuborg,
ProdBusinessGrp = ProductBusinessGrp,
ProdSkillGroup = ProductSkillGroup
From (select rca.prodid, rca.countrycd3, rca.servicelegacySys, p.ccoacode, pc.productgroup, pc.productorg, pc.productsuborg, pc.ProductBusinessGrp, pc.ProductSkillGroup
from tbl_Voy_RCATransfer as rca join tlb_Voy_ProductsLU as p on rca.prodid=p.prodid
and rca.countrycd3 = p.countrycd3 and rca.servicelegacysys = p.serviceprovidername
join tbl_Voy_ProductCodeLU as pc on p.ccoacode = pc.ccoacode )

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-05-02 : 08:00:19

Update dbo.tbl_Voy_RCATransfer
Set ProdGroup = productgroup,
ProdOrg = productorg,
ProdSubOrg = productsuborg,
ProdBusinessGrp = ProductBusinessGrp,
ProdSkillGroup = ProductSkillGroup
From (select rca.prodid, rca.countrycd3, rca.servicelegacySys, p.ccoacode, pc.productgroup, pc.productorg, pc.productsuborg, pc.ProductBusinessGrp, pc.ProductSkillGroup
from tbl_Voy_RCATransfer as rca join tlb_Voy_ProductsLU as p on rca.prodid=p.prodid
and rca.countrycd3 = p.countrycd3 and rca.servicelegacysys = p.serviceprovidername
join tbl_Voy_ProductCodeLU as pc on p.ccoacode = pc.ccoacode ) as f

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

Kristen
Test

22859 Posts

Posted - 2006-05-02 : 08:36:00

Your UPDATE statement doesn't look right to me (apart from the syntax error).

If will update EVERY ROW in dbo.tbl_Voy_RCATransfer with the results from your nested Sub Select, and if that has multiple rows it will probably do it over-and-over again so you just wind up with whichever values happened to be the final row in the sub-select.

I doubt that's what you want! Let us have a description of what you are trying to do and then we can give you some advice.

Kristen

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2006-05-02 : 09:09:53

Hi Kristen,

Yes this is my problem like i let the query run for 30 mins thinking it was updating but it has not.

What i want to happen is for my sub select see below to join up with the other 2 product tables and then update the dbo.tbl_Voy_RCATransfer with the values it returns.
I built my sub select first and it returns the exact amount of rows that are in my dbo.tbl_Voy_RCATransfer...how can i solve this so with the result of my sub select goes into my final table dbo.tbl_Voy_RCATransfer.

select rca.prodid, rca.countrycd3, rca.servicelegacySys, p.ccoacode, pc.productgroup, pc.productorg, pc.productsuborg, pc.ProductBusinessGrp, pc.ProductSkillGroup
from tbl_Voy_RCATransfer as rca join tlb_Voy_ProductsLU as p on rca.prodid=p.prodid
and rca.countrycd3 = p.countrycd3 and rca.servicelegacysys = p.serviceprovidername
join tbl_Voy_ProductCodeLU as pc on p.ccoacode = pc.ccoacode

Kristen
Test

22859 Posts

Posted - 2006-05-02 : 09:31:30

Just replace the SELECT with the UPDATE + SET - e.g. I think this is what you want:


Update rca
Set	ProdGroup = pc.productgroup, 
	ProdOrg = pc.productorg, 
	ProdSubOrg = pc.productsuborg, 
	ProdBusinessGrp = pc.ProductBusinessGrp, 
	ProdSkillGroup = pc.ProductSkillGroup
select	rca.prodid, rca.countrycd3, rca.servicelegacySys, p.ccoacode, pc.productgroup, 
	pc.productorg, pc.productsuborg, pc.ProductBusinessGrp, pc.ProductSkillGroup
from	tbl_Voy_RCATransfer as rca 
	join tlb_Voy_ProductsLU as p 
		 on rca.prodid=p.prodid 
		and rca.countrycd3 = p.countrycd3 
		and rca.servicelegacysys = p.serviceprovidername 
	join tbl_Voy_ProductCodeLU as pc 
	on p.ccoacode = pc.ccoacode

Kristen

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2006-05-03 : 03:27:22

Thanks Kristen, this does the job i want it to do.

Update rca
Set ProdGroup = pc.productgroup,
ProdOrg = pc.productorg,
ProdSubOrg = pc.productsuborg,
ProdBusinessGrp = pc.ProductBusinessGrp,
ProdSkillGroup = pc.ProductSkillGroup
from tbl_Voy_RCATransfer as rca
join tlb_Voy_ProductsLU as p
on rca.prodid=p.prodid
and rca.countrycd3 = p.countrycd3
and rca.servicelegacysys = p.serviceprovidername
join tbl_Voy_ProductCodeLU as pc
on p.ccoacode = pc.ccoacode

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources