Solved

Need help writing another SQL query

Posted on 2008-10-27
12
197 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
  • 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
 

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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 50 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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQL for monthly balance change 15 61
Access left join query 5 31
query question 13 41
another query question 7 26
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

757 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now