Hello,

I am writing a function that uses two table variables. The structures of
both are shown here:

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

DECLARE @existing TABLE (
workdate SMALLDATETIME
)

I need to do an update on the first table:

UPDATE @workdates
SET conflict = 'X'
FROM @existing s
WHERE workdate = s.workdate

I am concerned that the unqualified 'workdate' in the WHERE clause will
give me an ambiguous column reference. Is this SQL statement valid?

Thanks,
Andrew

UPDATE @workdates
SET conflict = 'X'
FROM @workdates w, @existing s
WHERE w.workdate = s.workdate

"Alexander Kuznetsov" <AK_TIREDOFSPAM@hotmail.COM> wrote in
news:1138895559.862884.111780@g47g2000cwa.googlegr oups.com:

> UPDATE @workdates
> SET conflict = 'X'
> FROM @workdates w, @existing s
> WHERE w.workdate = s.workdate
>
>

But is the original statement valid?

UPDATE @workdates
SET conflict = 'X'
FROM @existing s
WHERE workdate = s.workdate

Andrew

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