473,320 Members | 1,694 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Table variables

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
Feb 2 '06 #1
3 7471
UPDATE @workdates
SET conflict = 'X'
FROM @workdates w, @existing s
WHERE w.workdate = s.workdate

Feb 2 '06 #2
"Alexander Kuznetsov" <AK************@hotmail.COM> wrote in
news:11**********************@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
Feb 2 '06 #3
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

Feb 2 '06 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Daniel | last post by:
right now i have some sql server tables that i search for a value w/ a key each time a .aspx is hit. is there any way i can just have the first hit to the .aspx select all the entries from the...
4
by: gooday | last post by:
Table test2 has multiple amounts for each account, I would like to sum the amounts for the same account and use the result to update the variable 'tot_amount' in table test1. But SQL does not allow...
4
by: Tim.D | last post by:
People, I've ventured into the wonderful world of Stored Procedures. My first experience has been relatively successful however I am stuck on using host variables to specifiy actualy table or...
2
by: Remco Groot Beumer | last post by:
Hello, I created a program in which I use modules and classmodules for setting my variables. For example when I need to set the customerID in a variable I use something like: ...
10
by: Coleen | last post by:
Hi all :-) I have a weird formatting problem with an HTML table that I am populating using VB.Net and HTML. Here is the snippet of code for the cell I'm trying to format: Dim...
1
by: serge | last post by:
I am running SQL Server Best Practices on a SQL 2000 database and it is recommending me to change the temp tables inside SPs to table variables. I had read already in other places to use table...
3
by: penguin732901 | last post by:
What do experienced programmers find the most efficient way to handle user settings. Currently I have 4 tables which allow various clients to customize my program to work for them,...
4
by: hmiller | last post by:
Hey there folks, I was wondering if there was a way to store a list of variables in a table and then call them one at a time in some loop method. Here's what I've got: A table "Tab Names"...
2
by: Burbletrack | last post by:
Hi All, Hope someone can help me... Im trying to highlight the advantages of using table variables as apposed to temp tables within single scope. My manager seems to believe that table...
9
by: Kelii | last post by:
I've been trying to get this piece to work for a few hours, but have given up. I hope someone out there can help, I think the issue is relatively straightforward, but being a novice, I'm stumped....
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.