help with a special SELECT 1

[Hylsan]

Hi!
Ive got a table with these colums;
id, vanl_nr, vtax_kod

If I do a simple select-phrase like this;

SELECT vanl_nr, vtax_kod
FROM list 
where vtax_kod='vh6'

I'll get a couple of rows like this

vanl_nr   vtax_kod
2423421    vh6
2333244    vh6
3232333    vh6
1232344    vh6

Now the problem. Each vanl_nr can have several vtax_kod, so if I view the first one it might look like this;

vanl_nr   vtax_kod
2423421    vh6
2423421    cd1
2423421    cd2

So I would like to view all the vtax_kod on those vanl_nr that also has 'vh6' as vtax_kod. Hope atleast someone can understand what Im after
The result should look like this;

vanl_nr   vtax_kod
2423421    vh6
2423421    cd1
2333244    vh6
2333244    cd1
2333244    cd4
3232333    vh6
3232333    cd1
1232344    vh6
1232344    cd3
1232344    cd4

I'd also like to alter all the cd1's in the above to cd6, but not ALL the cd1.
Using sql 2000, and sql query analyser.
Can anyone help me out on this??
Thanks in advance!

/Hylsan

 

[PerlyGates]

I think this is what you want ... make sure it is before running!!


/*
SELECT ...
Interpretation:
Returning all the vanl_nr's if any one of them has a
vtax_kod value of 'vh6'
*/

SELECT a.*
FROM list a
INNER JOIN
list b
ON a.vanl_nr = b.vanl_nr
WHERE b.vtax_kod = 'vh6'
ORDER BY a.vanl_nr

/*
UPDATING ...
Interpretation:
Only updating the cd1's returned above
and NOT ALL the cd1's in the table
*/

UPDATE list
SET vtax_kod = 'cd6'
WHERE list.vtax_kod = 'cd1'
AND EXISTS ( SELECT *
FROM list L
WHERE list.vanl_nr = L.vanl_nr
AND L.vtax_kod = 'vh6' )

 

[Hylsan]

Thanks for your answer!

It looks like it could work.
Ive ran the SELECT and the values look alright, but I must admit I fail to completly understand the whole sequence.

The INNER JOIN, does it makes a copy of the table temporary?
Looks like it but Ive never used it before.

Thanks again!
/Hylsan