September 19, 2012 at 12:35 pm
What I'm trying to do right now is copy a column ("closed") from my Projects3 table into Projects. I only want to copy the values over where the row names match, however. I've been trying several different ways to do this, but keep getting errors. Any ideas or help would be greatly appreciated.
One Method:
UPDATE [Projects], [Projects3]
SET Projects3.Closed = Projects.Closed
WHERE Projects3.Name = Projects.Name
"Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ','."
Another Method:
SELECT Projects3.Closed
INTO Projects.Closed
WHERE Projects3.Name = Projects.Name
"Msg 4104, Level 16, State 1, Line 5
The multi-part identifier "Projects3.Name" could not be bound.
Msg 4104, Level 16, State 1, Line 5
The multi-part identifier "Projects.Name" could not be bound.
Msg 4104, Level 16, State 1, Line 5
The multi-part identifier "Projects3.Closed" could not be bound."
September 19, 2012 at 12:41 pm
Would you please post the schema of both "projects" and "projects3" tables?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.September 19, 2012 at 12:46 pm
there's two ways to do it;
one witht he proprietary SQL syntax, and another with a correlated query:
--SQL Server UPDATE FROM syntax
UPDATE [Projects]
SET Projects.Closed = Projects3.Closed
FROM [Projects3]
WHERE Projects3.Name = Projects.Name
--correlated subquery update
UPDATE [Projects]
SET Projects.Closed = (SELECT Projects3.Closed
FROM [Projects3]
WHERE Projects3.Name = Projects.Name)
Lowell
September 19, 2012 at 1:18 pm
Lowell (9/19/2012)
there's two ways to do it;one witht he proprietary SQL syntax, and another with a correlated query:
--SQL Server UPDATE FROM syntax
UPDATE [Projects]
SET Projects.Closed = Projects3.Closed
FROM [Projects3]
WHERE Projects3.Name = Projects.Name
--correlated subquery update
UPDATE [Projects]
SET Projects.Closed = (SELECT Projects3.Closed
FROM [Projects3]
WHERE Projects3.Name = Projects.Name)
The first one did exactly what I wanted, thanks. (Thanks to both of you).
September 19, 2012 at 8:40 pm
Lowell (9/19/2012)
there's two ways to do it;one witht he proprietary SQL syntax, and another with a correlated query:
--SQL Server UPDATE FROM syntax
UPDATE [Projects]
SET Projects.Closed = Projects3.Closed
FROM [Projects3]
WHERE Projects3.Name = Projects.Name
That's pretty cool - hadn't seen it done that way before. I would have done it like this (yours looks less verbose):
UPDATE p1
SET p1.Closed = p3.Closed
FROM Projects p1
INNER JOIN Projects3 p3
ON p3.Name = p1.Name
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply