November 14, 2011 at 4:59 am
Hi All,
I am trying to do an update statement:
begin tran
update dbo.Addresses
set addresses.Address1Original = addressescapscan.Address1Original,
addresses.Address2Original = addressescapscan.Address2Original,
addresses.Address3Original = addressescapscan.Address3Original,
addresses.TownOriginal = addressescapscan.TownOriginal,
addresses.CountyOriginal = addressescapscan.CountyOriginal,
addresses.PostcodeOriginal = addressescapscan.PostcodeOriginal,
addresses.CountryOriginal = addressescapscan.CountryOriginal
join dbo.Addresses on dbo.Addresses.dqid = dbo.addresscapscan.dqid
where addresses.A8Status <> 'N'
but am getting the multi part identifier could not be bound for the addresscapscan fields.
Any help much appreciated
November 14, 2011 at 5:07 am
Shouldn't it be like this?
FROM Addresses
INNER JOIN dbo.Addresses addressescapscan on dbo.Addresses.dqid = dbo.addresscapscan.dqid
November 14, 2011 at 5:07 am
You don't have the 2nd table in the FROM
UPDATE
A
SET
A.Address1Original = CAP.Address1Original
, A.Address2Original = CAP.Address2Original
, A.Address3Original = CAP.Address3Original
, A.TownOriginal = CAP.TownOriginal
, A.CountyOriginal = CAP.CountyOriginal
, A.PostcodeOriginal = CAP.PostcodeOriginal
, A.CountryOriginal = CAP.CountryOriginal
FROM
dbo.addresscapscan CAP
INNER join dbo.Addresses A
on A.dqid = CAP.dqid
WHERE
A.A8Status <> 'N'
November 14, 2011 at 5:17 am
You don't have the 2nd table in the FROM
In fact there is no FROM clause in OP's query... 😉
November 14, 2011 at 5:21 am
Dev (11/14/2011)
You don't have the 2nd table in the FROM
In fact there is no FROM clause in OP's query... 😉
Update dbo.table is an implied from if you really want to be anal about it. That's why the join was not throwing a syntax error.
November 14, 2011 at 5:23 am
martin.kerr 34088 (11/14/2011)
Hi All,I am trying to do an update statement:
begin tran
update dbo.Addresses
set addresses.Address1Original = addressescapscan.Address1Original,
addresses.Address2Original = addressescapscan.Address2Original,
addresses.Address3Original = addressescapscan.Address3Original,
addresses.TownOriginal = addressescapscan.TownOriginal,
addresses.CountyOriginal = addressescapscan.CountyOriginal,
addresses.PostcodeOriginal = addressescapscan.PostcodeOriginal,
addresses.CountryOriginal = addressescapscan.CountryOriginal
join dbo.Addresses on dbo.Addresses.dqid = dbo.addresscapscan.dqid
where addresses.A8Status <> 'N'
but am getting the multi part identifier could not be bound for the addresscapscan fields.
Any help much appreciated
You can't write a join after the set clause, to include the join you could use a from clause as suggested in eralier responses but that is not standard SQL, support for that construct may be removed in future (not any time soon, though)so it might be better to use a Merge statement to do this update.
MERGE dbo.Addresses addresses
USING addressescapscan
ON addresses.dqid = addressescapscan.dqid
WHEN MATCHED AND addresses.A8Status <> 'N'
SET addresses.Address1Original = addressescapscan.Address1Original,
addresses.Address2Original = addressescapscan.Address2Original,
addresses.Address3Original = addressescapscan.Address3Original,
addresses.TownOriginal = addressescapscan.TownOriginal,
addresses.CountyOriginal = addressescapscan.CountyOriginal,
addresses.PostcodeOriginal = addressescapscan.PostcodeOriginal,
addresses.CountryOriginal = addressescapscan.CountryOriginal;
- that's standard SQL, so unlikely to become unsupported.
Tom
November 14, 2011 at 5:31 am
That's why the join was not throwing a syntax error
So you are saying it's runtime / logical error...
November 14, 2011 at 5:34 am
Dev (11/14/2011)
That's why the join was not throwing a syntax error
So you are saying it's runtime / logical error...
No his problem is that he didn't reference BOTH tables in the query.
November 14, 2011 at 5:36 am
but that is not standard SQL, support for that construct may be removed in future (not any time soon, though)
I was unaware of this fact. Are you sure on it?
It still appears in UPDATE (Transact-SQL) syntax for SQL Server "Denali", without any side note / warning.
http://msdn.microsoft.com/en-us/library/ms177523(v=SQL.110).aspx
November 14, 2011 at 5:41 am
Dev (11/14/2011)
That's why the join was not throwing a syntax error
So you are saying it's runtime / logical error...
No, it's a binding error (if it passed syntax check but failed when run, which I doubt this piece of code would have done. The OP's code does throw a syntax error).
Edit: Clarified
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 14, 2011 at 5:42 am
Ninja's_RGR'us (11/14/2011)
Dev (11/14/2011)
That's why the join was not throwing a syntax error
So you are saying it's runtime / logical error...
No his problem is that he didn't reference BOTH tables in the query.
so it's syntax error...
November 14, 2011 at 5:44 am
GilaMonster (11/14/2011)
Dev (11/14/2011)
That's why the join was not throwing a syntax error
So you are saying it's runtime / logical error...
No, it's a binding error.
When in doubt, trust GAIL. 😀
November 14, 2011 at 5:52 am
GilaMonster (11/14/2011)
Dev (11/14/2011)
That's why the join was not throwing a syntax error
So you are saying it's runtime / logical error...
No, it's a binding error.
I heard it first time. I classify Errors as follows. Where will ‘Binding Error’ fit in?
Syntax Error
Definition : An error cause by violation of the programming language used.
Symptoms : Code fails to compile (error message from compiler)
Logical Error
Definition : An error caused by violation of logic (range, comparison, etc.). This error will NOT crash the program.
Symptoms : Unexpected output
Runtime Error/Execution Error
Definition : Any error, normally logical error that cause the program to crash.
Symptoms : Program crashes.
November 14, 2011 at 6:01 am
Ok, let's put it this way.
What kind of error is this?
SELECT Col1, Col2, Col3 FROM TableThatDoesNotExist
It's not a syntax error, that passes syntax check successfully.
It's not a run-time error, try-catch can't catch it.
p.s. A runtime error does not cause a crash unless the person who wrote the code was incompetent and didn't bother with any error handling.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 14, 2011 at 6:07 am
It's not a run-time error, try-catch can't catch it.
Well, it should.
p.s. A runtime error does not cause a crash unless the person who wrote the code was incompetent and didn't bother with any error handling.
Agree but the assumption is Developer didn't do anything extra except writing the logic for the application.
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply