• Want to receive the latest contracting news and advice straight to your inbox? Sign up to the ContractorUK newsletter here. Every sign up will also be entered into a draw to WIN £100 Amazon vouchers!

sql server question

EternalOptimist

The beerded one
Joined
Jul 8, 2005
Messages
22,492
Likes received
999
Thanks received
191
Location
Castle Saburac
sql server 2005

I have two tables

table A has columns id, stepvalue, status
table B has many columns id, 'F' & stepvalue ,,,,,,,,,


I want to put A.status into B.F??


I tried this using two SP's , a wrapper with a cursor plus one that executed each stepvalue but it was way too slow


A has 6k rows
B has 100
 
I'm no SQL Server expert but can you try an inner join:

Code:
UPDATE b SET f=a.status FROM b INNER JOIN a ON b.id = a.id
 
This will only work if your IDs are not duplicated

Code:
update B 
inner join
(select A.status, A.id
from A inner join B
on A.id = B.id) derivedT
on derivedT.id = B.id
set F = derviedT.status
 
Last edited: 13:32
finally figured out what you meant...

you want the F column in table B to hold the concatenated statuses from the A table matched by ID and Step value
ie F = status1|status2|status3......

before i go into any SQL.... why the funk do you want to do this? it goes against every single RDBMS principle!
 
This will only work if your IDs are not duplicated

Code:
update B 
inner join
(select A.status, A.id
from A inner join B
on A.id = B.id) derivedT
on derivedT.id = B.id
set F = derviedT.status

the whole point is that I dont know f

f is determined by one of the columns in table A
 
the whole point is that I dont know f

f is determined by one of the columns in table A

:confused:

Yes... 'F' is determined by 'Status' in the other table, right? So you select all the statuses in A that also have an ID in table B and then match them up.
 
:confused:

Yes... 'F' is determined by 'Status' in the other table, right? So you select all the statuses in A that also have an ID in table B and then match them up.


no, f is the column name
 
Yes, I know it is.

You're not making much sense I'm afraid...
 
finally figured out what you meant...

you want the F column in table B to hold the concatenated statuses from the A table matched by ID and Step value
ie F = status1|status2|status3......

before i go into any SQL.... why the funk do you want to do this? it goes against every single RDBMS principle!

I know its all screwy. Its a legacy system that has a pivot table, where the end table (b) has column names that correspond to a step value in table a.column.stepvalue

eg

table A

ID stepvalue status

1, 1, y,
1, 3, y,
1 , 4 , y,
2, 1, n,
2, 3, y,


table b

ID F01 F02 F03 F04===>
1, y, y, , y,
2, n , , y, ,



the problem is, I cant guarantee that the columns are there
plus using a wrapper and a cursor is way too slow

I am working on pivoting A right now, to get the b@astards to line up....unless anyone has a slicker idea





:rolleyes:
 
which SQL Server version are you using?

run this if you're not sure what you've connected to:
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

If you're using 2005 then there are pivot functions that can help
 

Advertisers

Back
Top