Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Need help writing another SQL query

Posted on 2008-10-27
12
Medium Priority
?
204 Views
Last Modified: 2012-05-05
Thanks for helping me writing sql queries.

I need  your help in writing another one. I am sure it is simple, but i am not able to figure it out.

Table has proj_id, cust_id, prod_id.
proj_id +  cust_id + prod_id  is table primary key.

A customer and project may have multiple products

in prod_id column I have ids like 24, 30, 3003, 50 , 200, 2090 etc etc.....
I need to work on 24 and 30....

for each record in table where prod_id is in 24,30,  if

only ID 24 exists change it to 3980
only ID 30 exists change it to 3980
both 24 and 30 exists change 24 to 3980 and 30 to 00030 (want to delete it in the end, but not right now)

all data and changes are in same table.

Thanks for your help

0
Comment
Question by:immtrac
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
12 Comments
 
LVL 5

Expert Comment

by:PaulKeating
ID: 22817518
Try this: I don't have your tables so of course it isn't properly checked

First change the 24/30s to 3980 if there are no corresponding 30/24s.

update mytable set prod_id = '3980' where prod_id in ('24','30') and not exists
(select * from mytable others
where others.proj_id = mytable.proj_id
and others.cust_id = mytable.cust_id
and others.prod_id in ('24','30')
and others.prod_id <> mytable.prod_id )

Now the only 24s and 30s left are ones where you have both (because the others are now 3890).

update mytable set prod_id = '3890' where prod_id = '24'
update mytable set prod_id = '00030' where prod_id = '30'
0
 

Author Comment

by:immtrac
ID: 22818115
I will try it tomorrow. But something does not seems right.

Will this leave other ids (not 24 and 30) untouched? I don't want to change other ids except 24 and 30.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22818253
The following SHOULD do what you asked if I interpreted correctly.  It is currently set to run as a select, but after you verify that it selects correctly then change it to the update statement (included below) and the rollback to a commit.
begin tran
--update Yt set prod_id = 
select yt.*,
case when yt24.p24 is null or yt30.p30 is null then '3980'
                  when yt24.p24 is null then '00030'
                  when yt30.p30 is null then '3890'
             else prod_id
             end
from YourTable yt
left outer join (select prod_id,1 as p24 from YourTable where prod_id = '24') yt24
on yt.prod_id = yt24.prod_id
left outer join (select prod_id, 1 as p30 from YourTable where prod_id = '30') yt30
on yt.prod_id = yt30.prod_id
where yt24.prod_id is not null or yt30.prod_id is not null
 
 
rollback

Open in new window

0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:immtrac
ID: 22818750
Sorry folks. I thought it gonna be easy one. BrandonGalderisi, the query looks very confusing.

I am attaching a sample table.  primary key is combination of all three ids.

For a proj_id and cust_id:

If 24 and 30 both exist (link in case of 1st and 2nd record) I want to change 24 to 3980 and 30 to 0030. because I can not convert both to 3980 for primary key reason.

Same in case of 12tn and 13th record
======================================================

In case of third record, I want to change 24 to 3980, because it is unique record. No primary key issue.

Same in case of fourth record, change 30 to 3980 as no primary key issue.
================================================================

In plain, for a proj_id and cust_id,

 if 2 records are there (one with prod_id 24 and one with 30) then 24 will be changed to 3980 and 30 will be changed to 0030.

If only one record exists with id 24, it will be changed to 3980
if only one record exists with id 30, it will be changed to 3980

I hope this explains my question better. There are thousands of records with many other prod_ids. But I only need to take care ones with prod_id 24 of 30. So query should not mess up other records.

Thanks for your help and I really appreciate your help.







sample.jpg
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22821831
First I'll ask the most OBVIOUS question... why not add a surrogate key?

Secondly.  I think I've covered it now.  The last column will display the target value if you run it as an update.  If/when it looks right, do the same thing I mentioned above with the comments and transaction.
begin tran
--update Yt set prod_id = 
select *,
  case when yt.prod_id = '24' then case when yt24.p24 is not null and yt30.p30 is not null then '3980' else '3980' end
       when yt.prod_id = '30' then case when yt24.p24 is not null and yt30.p30 is not null then '0030' else '3980' end
       else yt.prod_id
 
end
 
from YourTable yt
left outer join (select proj_id,cust_id,1 as p24 from YourTable where prod_id = '24') yt24
on yt.proj_id = yt24.proj_id and yt.cust_id = yt24.cust_id 
left outer join (select proj_id,cust_id, 1 as p30 from YourTable where prod_id = '30') yt30
on yt.proj_id = yt30.proj_id and yt.cust_id = yt30.cust_id 
where yt24.prod_id is not null or yt30.prod_id is not null
 
 
rollback

Open in new window

0
 

Author Comment

by:immtrac
ID: 22822174
it is giving me an error

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'prod_id''.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'prod_id'.
0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22824263
change:

  where yt24.prod_id is not null or yt30.prod_id is not null

to

  where yt24.p24 is not null or yt30.p34 is not null
0
 

Author Comment

by:immtrac
ID: 22825484
I added "prod_id" in both left joins and it is working now.

I guess I did it right too?



0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22825499
You don't want it in your joins.  Can you post what you have?
0
 

Author Comment

by:immtrac
ID: 22826088
here it is.


Update  prod_id = 
  case when t1.prod_id = '24' then case when t2.p24 is not null and t3.p30 is not null then '3980' else '3980' end
       when t1.prod_id = '30' then case when t2.p24 is not null and t3.p30 is not null then '30001' else '3980' end
       else t1.prod_id
	end 
 
from product t1 
left outer join (select proj_id,cust_id,prod_id,1 as p24 from product where prod_id = '24') t2
on t1.proj_id = t2.proj_id and t1.cust_id = t2.cust_id 
left outer join (select proj_id,cust_id,prod_id, 1 as p30 from product where prod_id = '30') t3
on t1.proj_id = t3.proj_id and t1.cust_id = t3.cust_id 
where t2.prod_id is not null or t3.prod_id is not null 
and t1.prod_id in (24,30)

Open in new window

0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 200 total points
ID: 22826192
prod_id is not necessary in the two derived tables.
line 13 I forgot to re-add.  Other than that, you need line 1/2 to be:

update t1
set prod_id =

Then continue with the existing line 2.


0
 

Author Closing Comment

by:immtrac
ID: 31510549
Thanks for your help.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question