update using joins and alias' - 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

SQL Server 2000 Forums

Transact-SQL (2000)

update using joins and alias'

Author

Topic

minette
Starting Member

2 Posts

Posted - 2006-03-09 : 06:32:04

I keep getting this error when trying to run the following query. "Cannot use the column prefix 'T'. This must match the object in the UPDATE clause 'tbl_timeline'."

Is anyone able to help me as I have no idea how to fix it.

begin transaction
update tbl_timeline
set T.intClientID = J.intClientID
where T.intJobID in (
select *
from tbl_timeline T
join tbl_jobs J on T.intJobID = J.intJobID
where T.intClientID = 53 and J.intClientID <> 53 and J.intClientID <> 0
and T.varRef <> 'ENG'
and T.varRef <> 'SUP'
and T.varRef <> 'ADM'
and T.varRef <> 'DOC'
and T.varRef <> 'SIC'
and T.varRef <> 'DHP'
and T.varRef <> 'HOL'
and T.varRef <> 'INT'
and T.varRef <> 'MET'
and T.varRef <> 'PUR'
and T.varRef <> 'QUO'
and T.varRef <> 'Y2K'
and T.varRef <> 'MOV'
and T.varRef <> 'TRN'
)

Thanks
Minette

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-03-09 : 07:24:23

You can't use prefixes for the fields you are updating..

the select * would fail as well..


begin transaction
update tbl_timeline
set intClientID = J.intClientID
where intJobID in (
select T.intJobID
from tbl_timeline T
join tbl_jobs J on T.intJobID = J.intJobID 
where T.intClientID = 53 and J.intClientID not in (53,0)
and T.varRef not in ('ENG','SUP','ADM','DOC','SIC',
'DHP','HOL','INT','MET','PUR','QUO','Y2K','MOV','TRN')
)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-03-09 : 07:30:11

you could change that big and T.varRef <> 'value' into T.varRef not in (values)


update	T
set	T.intClientID = J.intClientID
from	tbl_timeline T
	join tbl_jobs J on T.intJobID = J.intJobID 
where	T.intClientID = 53 and J.intClientID <> 53 and J.intClientID <> 0
	and T.varRef <> 'ENG'
	and T.varRef <> 'SUP'
	and T.varRef <> 'ADM'
	and T.varRef <> 'DOC'
	and T.varRef <> 'SIC'
	and T.varRef <> 'DHP'
	and T.varRef <> 'HOL'
	and T.varRef <> 'INT'
	and T.varRef <> 'MET'
	and T.varRef <> 'PUR'
	and T.varRef <> 'QUO'
	and T.varRef <> 'Y2K'
	and T.varRef <> 'MOV'
	and T.varRef <> 'TRN'

Go with the flow & have fun! Else fight the flow

minette
Starting Member

2 Posts

Posted - 2006-03-09 : 07:51:44

EXCELLENT - worked like a charm. Thanks to you both.

Minette

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources