Strange problem in sql query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Strange problem in sql query

Author

Topic

SHIVPREET2K1
Starting Member

32 Posts

Posted - 2010-05-26 : 06:15:36

Dear Friends I have one table in which i have this data


A_R5
Item_Code  sub_item_code1 Sub_item_code2 Sub_item_code3
A               B               C 
X               Y               C


I execute this query

insert into A_R5
(Sub_item_code2,Sub_item_code3)
 
select  item_code,rm_code from 
rm_mc_polish_Cost_of_items 

where item_code in 

(Select sub_item_code2 from A_R5)


I got following output


A_R5
Item_Code  sub_item_code1 Sub_item_code2 Sub_item_code3
A               B               C 
X               Y               C
X               Y               C          D

In the rm_mc_polish_cost_of_items is sub_item_code3 is place which is related with sub_item_code2 only

My question is why this query is inserting the new record for second row only (with X item) and why not for first record (A item)

RM_Mc_polish_cost_of_item has only relation with sub_item_code2
that is with (C Item)

Please tell me where I am wrong in this query.

thanks in advance

Shivpreet2k1

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-26 : 07:14:02

My questions are:
Your posted code is inserting values only for Sub_item_code2,Sub_item_code3 so how can it be that after your insert the columns Item_Code and sub_item_code1 are filled with X and Y?
Whre is the example data for table rm_mc_polish_Cost_of_items?

No, you're never too old to Yak'n'Roll if you're too young to die.

SHIVPREET2K1
Starting Member

32 Posts

Posted - 2010-05-26 : 07:54:11

thnaks for your query buddy
this is my logic for for inserting the item_code and sub_item_code1



update a
set a.Sub_item_code2=b.Sub_item_Code2 from A_R5 a
inner join A_R5 b on a.sub_item_code3=b.sub_item_code3
where a.Sub_item_code2 is null and b.sub_item_code2 is not null



update a
set a.Sub_item_code1=b.Sub_item_Code1 from A_R5 a
inner join A_R5 b on a.sub_item_code2=b.sub_item_code2
where a.Sub_item_code1 is null and b.sub_item_code1 is not null

update a
set a.item_code=b.item_code from A_R5 a
inner join A_R5 b on a.sub_item_code1=b.sub_item_code1
where a.item_code is null and b.item_code is not null

Thanks for ur query

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources