Update a table with values from another 1

[ice78991]

I have a table called bikes with the following columns
BikeID BikeName BikeSortOrder
1 Bike1 1
2 Bike2 2
3 Bike3 3

I wish to update the BikeSortOrder Column with values from another temporary table which is generated by a function createTable()

TempTable contains the columns
BikeID SortOrder
1 3
2 2
3 1

I am trying to come up with an update statement which updates the BikeSortOrder column in bikes with the SortOrder values generated within TempTable. IN the above example, the sort order of the bikes table is reversed.

I have tried using
UPDATE bikes
SET b.BikesSortOrder = tt.SortOrder
FROM bikes b
JOIN createTable() tt ON b.BikeID = (Select BikeID from tt)

but it is not correct
Any ideas?

 

[bborissov]

UPDATE bikes SET b.BikesSortOrder = tt.SortOrder
FROM bikes b
INNER JOIN dbo.createTable() tt ON b.BikeID = tt.BikeID

not tested

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
Microsoft MVP VFP

 

[ice78991]

Thanks. That works