Update inner join question

Graham · June 18th, 2003, 07:30 AM

Hi all

I've recently been moving apps to SQL2k from Access 2k

In access I could do

update table1 inner join table2 on table1.key=table2.key set table1.column1=table2.column1

Which doesn't work in SQL. The help files make some reference to update and joins but I can't get this to play ball.

Any clues please?

Regards
Graham

nbryson · June 18th, 2003, 07:32 AM

Try this

update table1
set table1.column1=table2.column1
from table2 inner join table2 on table1.key=table2.key

Cheers

Nickie

Graham · June 18th, 2003, 08:05 AM

Thanks Nickie

That gives me an error about needing to use correllation to distinguish the columns.

The actual line is: -
update allocations
set allocations.driver_name=drivers.driver_name
from drivers inner join drivers on allocations.driver_record_number=drivers.record_nu mber

Jeff Mason · June 18th, 2003, 09:05 AM

Quote:

quote:Originally posted by Graham
Thanks Nickie

That gives me an error about needing to use correllation to distinguish the columns.

The actual line is: -
update allocations
set allocations.driver_name=drivers.driver_name
from drivers inner join drivers on allocations.driver_record_number=drivers.record_nu mber

Without knowing your table structure I'm guessing your JOIN ought to read:

Code:

FROM drivers INNER JOIN allocations ON
   allocations.driver_record_number=drivers.record_number

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Graham · June 18th, 2003, 10:02 AM

Sorry Jeff

That is what it does say, all down to satellite delay between brain and keyboard!

Regards
Graham

Jeff Mason · June 18th, 2003, 10:14 AM

So you say the offending code is:

Code:

update allocations 
set allocations.driver_name=drivers.driver_name
from drivers inner join allocations on allocations.driver_record_number=drivers.record_number

And this is generating an error?

This query looks fine to me. Post the table defs, because something else is going on here...

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

ramankumar_04 · January 22nd, 2011, 11:55 AM

id mgrid depno
1 null 10
2 null 20
3 1 20
4 2 10
5 3 10

explation: in above table id - "1" has depno 10 and id - "3" has depno 20 but "1" is manager of "3". so id - "3" should have depno 10.
similarly id - "4" should have depno 20.
similarly we need to check this for id - "5".

i want to do this with update query. please let me know what should be query like?