Procedure to update one table with value from another table

I want to run a stored procedure that selects a value from one column
in a table and updates a column in another table with the value
selected from the first table.
CREATE PROCEDURE dbo.UpdateField
@.ID NVARCHAR(10)
AS
BEGIN
SET NOCOUNT ON
SELECT ColumnX AS "ColumnX_Value"
FROM Table1
WHERE Table1.ID = @.ID
UPDATE Table2
SET ColumnY = ColumnX_Value
WHERE Table2.ID = @.ID
END
GO
The procedure is called from an .asp page using:
EXEC dbo.UpdateField @.ID = 'SomeValue'"
SQL Server Query Analyzer gives me an "Invalid column name
'ColumnX_Value'" error when I run the above procedure.
What have I done wrong?Andyza wrote on 21 Jun 2005 03:46:21 -0700:
> I want to run a stored procedure that selects a value from one column
> in a table and updates a column in another table with the value
> selected from the first table.
> CREATE PROCEDURE dbo.UpdateField
> @.ID NVARCHAR(10)
> AS
> BEGIN
> SET NOCOUNT ON
> SELECT ColumnX AS "ColumnX_Value"
> FROM Table1
> WHERE Table1.ID = @.ID
> UPDATE Table2
> SET ColumnY = ColumnX_Value
> WHERE Table2.ID = @.ID
> END
> GO
> The procedure is called from an .asp page using:
> EXEC dbo.UpdateField @.ID = 'SomeValue'"
> SQL Server Query Analyzer gives me an "Invalid column name
> 'ColumnX_Value'" error when I run the above procedure.
> What have I done wrong?
You can't do it like that - you're just selecting rows, and then doing an
unrelated updated. Easiest way will be to use a join
UPDATE Table2
SET ColumnY = Table1.ColumnX
FROM Table1 INNER JOIN Table2 ON Table1.ID = Table2.ID
Dan|||Andyza
Why you are refering to the alias?
In the below example you will have to ensure that SELECT statement returns
only one row ,otherwise you could get a wrong value
I'd also add UPDLOCK hint to the SELECT statement to ensure that the data
has not changed since i last read id ( without blocking others readers). For
more details please refer the BOL
(Untested)
CREATE PROCEDURE dbo.UpdateField
@.ID NVARCHAR(10)
AS
DECLARE @.var INT
BEGIN
SET NOCOUNT ON
BEGIN TRAN
SELECT @.var=ColumnX AS "ColumnX_Value" (UPDLOCK)
FROM Table1
WHERE Table1.ID = @.ID
UPDATE Table2
SET ColumnY = @.var
WHERE Table2.ID = @.ID
COMMIT TRAN
END
GO
"Andyza" <andyza@.webmail.co.za> wrote in message
news:1119350781.771565.170710@.g14g2000cwa.googlegroups.com...
> I want to run a stored procedure that selects a value from one column
> in a table and updates a column in another table with the value
> selected from the first table.
> CREATE PROCEDURE dbo.UpdateField
> @.ID NVARCHAR(10)
> AS
> BEGIN
> SET NOCOUNT ON
> SELECT ColumnX AS "ColumnX_Value"
> FROM Table1
> WHERE Table1.ID = @.ID
> UPDATE Table2
> SET ColumnY = ColumnX_Value
> WHERE Table2.ID = @.ID
> END
> GO
> The procedure is called from an .asp page using:
> EXEC dbo.UpdateField @.ID = 'SomeValue'"
> SQL Server Query Analyzer gives me an "Invalid column name
> 'ColumnX_Value'" error when I run the above procedure.
> What have I done wrong?
>|||Daniel
You may want to avoid writing such techniques for UPDATETING because you
can get a wrong output
See , David Portas's example
CREATE TABLE Countries
(countryname VARCHAR(20) NOT NULL PRIMARY KEY,
capitalcity VARCHAR(20));
CREATE TABLE Cities
(cityname VARCHAR(20) NOT NULL,
countryname VARCHAR(20) NOT NULL
REFERENCES Countries (countryname),
CONSTRAINT PK_Cities
PRIMARY KEY (cityname, countryname));
INSERT INTO Countries (countryname, capitalcity) VALUES ('USA', NULL);
INSERT INTO Countries (countryname, capitalcity) VALUES ('UK', NULL);
INSERT INTO Cities VALUES ('Washington', 'USA');
INSERT INTO Cities VALUES ('London', 'UK');
INSERT INTO Cities VALUES ('Manchester', 'UK');
The MS-syntax makes it all too easy for the developer to slip-up by
writing ambiguous UPDATE...FROM statements where the JOIN criteria is
not unique on the right side of the join.
Try these two identical UPDATE statements with a small change to the
primary key in between.
UPDATE Countries
SET capitalcity = cityname
FROM Countries JOIN Cities /* evil UPDATE... FROM syntax */
ON Countries.countryname = Cities.countryname;
SELECT * FROM Countries;
ALTER TABLE Cities DROP CONSTRAINT PK_Cities;
ALTER TABLE Cities ADD CONSTRAINT PK_Cities PRIMARY KEY (countryname,
cityname);
UPDATE Countries
SET capitalcity = cityname
FROM Countries JOIN Cities /* don't do this! */
ON Countries.countryname = Cities.countryname;
SELECT * FROM Countries;
You get this from the first SELECT statement:
countryname capitalcity
-- --
UK London
USA Washington
and this from the second:
countryname capitalcity
-- --
UK Manchester
USA Washington
(though these results aren't guaranteed - that's part of the problem).
Why did the result change? The physical implementation has affected the
meaning of the code, with serious, potentially disastrous consequences.
How can you even test your code if its results are subject to change
due to the vagaries of storage, indexing and cacheing?
With the ANSI syntax there is no ambiguity. The UPDATE statement
compels the programmer to design an unambiguous assignment subquery
that returns no more than a single value.
UPDATE Countries
SET capitalcity = (SELECT MIN(cityname)
FROM Cities
WHERE Countries.countryname = Cities.countryname);
At the very least this forces the developer to reconsider whether the
UPDATE statement makes logical sense. You might want to make an effort
to learn Standard SQL instead of a dialect that can change at any time,
which will not port, cannot be understood by other programmers, etc.
"Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
news:eyB8e%23kdFHA.2288@.TK2MSFTNGP14.phx.gbl...
> Andyza wrote on 21 Jun 2005 03:46:21 -0700:
> >
> > I want to run a stored procedure that selects a value from one column
> > in a table and updates a column in another table with the value
> > selected from the first table.
> >
> > CREATE PROCEDURE dbo.UpdateField
> > @.ID NVARCHAR(10)
> > AS
> > BEGIN
> > SET NOCOUNT ON
> > SELECT ColumnX AS "ColumnX_Value"
> > FROM Table1
> > WHERE Table1.ID = @.ID
> >
> > UPDATE Table2
> > SET ColumnY = ColumnX_Value
> > WHERE Table2.ID = @.ID
> > END
> > GO
> >
> > The procedure is called from an .asp page using:
> >
> > EXEC dbo.UpdateField @.ID = 'SomeValue'"
> >
> > SQL Server Query Analyzer gives me an "Invalid column name
> > 'ColumnX_Value'" error when I run the above procedure.
> > What have I done wrong?
> You can't do it like that - you're just selecting rows, and then doing an
> unrelated updated. Easiest way will be to use a join
> UPDATE Table2
> SET ColumnY = Table1.ColumnX
> FROM Table1 INNER JOIN Table2 ON Table1.ID = Table2.ID
>
> Dan
>|||Uri wrote on Tue, 21 Jun 2005 13:59:04 +0300:
> Daniel
> You may want to avoid writing such techniques for UPDATETING because you
> can get a wrong output
> See , David Portas's example
Yeah, I can see that, thanks for the post. I assumed (perhaps incorrectly)
that the OP's UPDATE was being done joining on keys (as the column name was
ID which tends to be used for a PK/FK column), and therefore would always be
a single matching row in Table2.
Dan