Posted by Greg on 02/02/06 21:52

Hi Andrew,

You could answer the question yourself by running the queries as a
script in QA.. E.G

--START ANDREWS SCRIPT

DECLARE @workdates TABLE (
conflict CHAR(1),
workdate SMALLDATETIME
)


DECLARE @existing TABLE (
workdate SMALLDATETIME
)

insert @workdates values ('a', '2006-02-03')
insert @existing values ('2006-02-03')
insert @workdates values ('a', '2006-02-02')

--I need to do an update on the first table:
UPDATE @workdates
SET conflict = 'X'
FROM @existing s
WHERE workdate = s.workdate

-- FINISH ANDREWS SCRIPT

You'll find this error message..

Server: Msg 209, Level 16, State 1, Line 17
Ambiguous column name 'workdate'.

That answers your original post. The following is a working example -
note the syntax differences..

-- START GREGS SCRIPT
DECLARE @workdates TABLE (
conflict CHAR(1),
workdate SMALLDATETIME
)


DECLARE @existing TABLE (
workdate SMALLDATETIME
)

insert @workdates values ('a', '2006-02-03')
insert @existing values ('2006-02-03')
insert @workdates values ('a', '2006-02-02')

--I need to do an update on the first table:
UPDATE w
SET conflict = 'X'
FROM @existing s JOIN @workdates w ON w.workdate = s.workdate

-- FINISH GREGS SCRIPT

• Next in forum: Performance and data structure question
• Prev in forum: Re: Monthly date range substitution
• Thread view: Re: Table variables

[Reply to this message]