SQL query problem

[soapdad]

Hello All,
I am trying to create an update query on MSSQL using query analyzer and seem to be having a problem using a left join. I am using the following query:

UPDATE Aorders LEFT JOIN orders ON Aorders.NewOrderID = orders.orderid SET orders.odate = Aorders.odate;

and I am getting the following error:

Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'JOIN'.

I have tried many different variations on this code without success. Can anyone see anything obviously wrong with this?

Thanks in advance!

 

[r937]

not sure that you really need a left join, since if there's no match, what would you update?



therefore, try this --

update orders
   set odate = Aorders.odate
  from orders t1
inner
  join Aorders t2
    on orders.orderid = Aorders.NewOrderID

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[r937]

oops, forgot the aliases --

update orders
   set odate = [b]t2[/b].odate
  from orders t1
inner
  join Aorders t2
    on [b]t1[/b].orderid = [b]t2[/b].NewOrderID

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[soapdad]

I was using a left join after reading in the Access documentation that doing so would add any unmatched records to the second file. I wasn't sure if that would work in straight SQL. My goal is to update any record in the second file that has a match and add the orders from the first if there wasn't a match. I had created a SQL query in Access that did that and I was trying to apply the same principle to a query in query analyzer. Any thoughts on that?
Thanks for taking the moment to help!

 

[r937]

yes, that's how left outer joins work -- rows from the left table that have no matching rows in the right table are included along with those that do

but this --

My goal is to update any record in the second file that has a match and add the orders from the first if there wasn't a match

is a different kettle of fish



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[soapdad]

Thanks for the attempt. I just went out and bought a Microsoft book on SQL programming. I haven't gone into it yet, but I guess I could also do this in two passes. I was attempting to do it this way because I sometimes use Access to create a model query and then modify it in query analyzer. That query was suggested in their help files and works fine with Access. Not in SQl, I guess.
Thanks again....

 

[r937]

yes, that's the nice thing about SQL standards -- there are so many to choose from!



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)