Try this
UPDATE table1 AS a
SET value1 = x.value1
FROM table2 AS x
WHERE x.code = a.something
Evan Carroll
2010-03-22 18:47:50
views:
58answers:
7
+1
A:
I get a "Incorrect syntax near the keyword 'as'." when I tried this...
Sailing Judo
2010-03-22 18:51:22
Actually I think that this one should work if both "as"s are removed. You can't alias a table that's being updated, and you don't need "as" to alias a table name that is in a "from" statement.
Jeffrey L Whitledge
2010-03-22 19:10:53
+1
A:
Sounds like it should work like this:
Update table1 Set value1 =
( Select value1
From table2
Where table2.code = table1.something
)
Peter Lang
2010-03-22 18:49:07
This is similar to my very first attempt. I get an "Incorrect syntax near 'A'." when I try this.
Sailing Judo
2010-03-22 18:54:59
@Sailing Judo: I have edited my answer, but now it's more or less the same as Larry Lustig's. In case `table1` is not your real table name, are you sure you replaced both in the first and in the last line?
Peter Lang
2010-03-22 19:00:57
+2
A:
UPDATE is notoriously different across different DBMSes. But try something along these lines:
UPDATE Table1 SET Value1 =
(SELECT Value1 FROM Table2 WHERE code = Table1.Something)
Larry Lustig
2010-03-22 18:49:20
I tried this one too. I get the message "The column prefix 'Table1' does not match with a table name or alias name used in the query." This is referring to the "Table1" in the select.
Sailing Judo
2010-03-22 18:56:51
This is probably the most portable solution — it even works in Firebird, which doesn't support FROM in UPDATE at all. I'm surprised it doesn't work in SQL Server 6.5; unfortunately, I don't have an installation handy to test against.
Larry Lustig
2010-03-22 19:15:33
A:
"I am using Microsoft SQL Server 6.5"?? Why? Why don't you get the FREE SQL 2008 Express edition or if you qualify, SQL 2008 Developers Edition for about USD50?
Johan van Zyl
2010-03-22 18:58:40
@Johan van Zyl: Welcome to StackOverflow! Since you did not provide an answer, you should have added this as comment to the question instead of answer.
Peter Lang
2010-03-22 19:03:05
Who knows. Maybe the database is embedded in some sailing hardware? One doesn't get far in this business if one's solution to a programming problem involves purchasing new hardward and/or software.
Larry Lustig
2010-03-22 19:18:09
This is an existing database server that works perfectly fine. I'm pretty sure the Express versions (which I use all the time) don't support hundreds of connections needed by the applications involved here.
Sailing Judo
2010-03-22 19:25:14
A:
UPDATE Table1, Table2
SET Table1.Value1 = Table2.Value1
WHERE Table2.Code = Table1.Something
Does this help?
shahkalpesh
2010-03-22 19:04:01
A:
Try this one:
update table1
set value1 = x.value1
from table2 x
where something = x.code
Jeffrey L Whitledge
2010-03-22 19:04:02
+1
A:
Have you tried something like this :-
UPDATE table1
SET x.Value1=y.Value1
FROM Table1 x INNER JOIN
Table2 y ON x.Code=y.Something
ydobonmai
2010-03-22 19:06:27
This is ultimately what worked. I had to move the "table1.something" outside the select, performing a join on the update part of the statment. I'll post the working pseudo-sql in my question.
Sailing Judo
2010-03-22 19:27:48
Actually, now that I look at it, no need to repost the pseudo-sql. It looks like what you typed.
Sailing Judo
2010-03-22 19:37:56