I need help with a SQL Query

Hi Experts,
I need help with an update query between 2 tables without using a from/join clause.
How can I do this.

Thanks in advance,
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike EghtebasDatabase and Application DeveloperCommented:
You can do that by giving us table names, field names to be updated, etc. Any relevant information we need to built a workable example.

Also see: http://www.experts-exchange.com/articles/18499/Top-10-Ways-to-Ask-Better-Questions.html
HuaMin ChenProblem resolverCommented:
Here is one example
UPDATE Test1.dbo.Employee
SET DeptID = emp2.DeptID
FROM Test2.dbo.Employee as 'emp2'
   Test1.dbo.Employee.EmployeeID = emp2.EmployeeID

Open in new window

Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
If two tables are involved, you cannot go without a FROM clause. Anything using a different source than the  table to update requires it. With one exception: You can build an updatable view, which obfuscates the table the column is located in - but that has a lot of constraints, and I won't go into the details for that reason.

If you are trying to head towards something e.g. Oracle allows, which is
update (select ta.ca1, tb.cb2 from table1 ta join table2 tb on ta.id = tb.id /* ... */)
set ca1 = cb2

Open in new window

this cannot be used in MSSQL that way, and anyway it contains a FROM too :D
awking00Information Technology SpecialistCommented:
In sql server 2008, you can use merge -
merge into table1 t1
using table2 t2
on (t1.key = t2.key)
when matched
set t1.field = t2.field

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.