Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

help with a special SELECT 1

Status
Not open for further replies.

Hylsan

Programmer
Mar 20, 2002
43
SE
Hi!
Ive got a table with these colums;
id, vanl_nr, vtax_kod

If I do a simple select-phrase like this;
Code:
SELECT vanl_nr, vtax_kod
FROM list 
where vtax_kod='vh6'
I'll get a couple of rows like this
Code:
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;
Code:
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;
Code:
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
 
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' )

 
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

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top