Query Help - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Query Help

Author

Topic

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2013-06-05 : 13:12:41

Hi,
I am trying to update few columns in a table using CTE.
but Only the first update executes successfullt.
for the other update statements it throws error 'invalid object X'.
Please help me.
Below is the query

with x as
(select SUBSTRING(deptid, PATINDEX('%[^0 ]%', deptid + ' '), LEN(deptid)) as deptid,
description,
address,
city,
StateID,ZipCode
from MasterReference.dbo.Department where DeptID=080
)

update [s080ws\sqlexpress].POS.dbo.tbldepartmentinfo set description=(select description from x)
go

update [s080ws\sqlexpress].POS.dbo.tbldepartmentinfo set streetaddress=(select address from x);
go

update [s080ws\sqlexpress].POS.dbo.tbldepartmentinfo set city=(select city from x)
go
update [s080ws\sqlexpress].POS.dbo.tbldepartmentinfo set state=(select stateid from x)
go
update [s080ws\sqlexpress].POS.dbo.tbldepartmentinfo set zipcode=(select zipcode from x)
go

mohammad.javeed.ahmed@gmail.com

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-05 : 13:36:47

That is just the way CTE's work - i.e., the scope of the CTE is only for the insert/delete/update/select construct that follows it. The CTE combined with the construct following it is a SINGLE statement.

You will need to repeate the CTE construct before each update (assuming the result of the CTE is not affected by a prior update).

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-05 : 13:41:01

it should be


;with x as
(select SUBSTRING(deptid, PATINDEX('%[^0 ]%', deptid + ' '), LEN(deptid)) as deptid,
description,
address,
city,
StateID,ZipCode
from MasterReference.dbo.Department where DeptID=080
)

update s
set s.description = x.description,
s.streetaddress = x.address,
s.city = x.city,
s.state = x.state,
s.zipcode = x.zipcode
from [s080ws\sqlexpress].POS.dbo.tbldepartmentinfo s
cross join x

i assume x will always have a single record otherwise you need to use inner join based on linking column.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-06-05 : 13:45:21

That would be assuming you want to update every row in your tbldepartmentinfo table with the same values.
I suspect your original UPDATE statements were all missing WHERE clauses?

How many rows does your CTE resolve to?
Does tbldepartmentinfo have a column called [DeptID]?

Perhaps this:


update t set
       t.[description] = d.[description]
       ,t.[address] = d.[address]
       ,t.city = d.city
       ,t.statid = d.stateid
       ,t.zipcode = d.zipcode
from   MasterReference.dbo.Department d
inner join [s080ws\sqlexpress].POS.dbo.tbldepartmentinfo t
       on t.deptid = SUBSTRING(d.deptid, PATINDEX('%[^0 ]%', d.deptid + ' '), LEN(d.deptid))
where  d.deptid = '080'

Be One with the Optimizer
TG

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2013-06-05 : 14:56:12

Thanks for your replies.
The table tbldepartmentInfo will have only one record.

I have used the below query ad worked fine.

select SUBSTRING(deptid, PATINDEX('%[^0 ]%', deptid + ' '), LEN(deptid)) as deptid,
description,
address,
city,
StateID,ZipCode into #address
from MasterReference.dbo.Department where DeptID=030

update a set description=b.description,
streetaddress=b.Address,
city=b.City,
state=b.StateID,
zipcode=b.ZipCode from [s030ws\sqlexpress].POS.dbo.tbldepartmentinfo a
inner join #address b
on a.deptid=b.deptid

drop table #address

mohammad.javeed.ahmed@gmail.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources