Need help writing another SQL query

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'

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.

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

Select allOpen in new window

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

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

Select allOpen in new window

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'.

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

I added "prod_id" in both left joins and it is working now.

I guess I did it right too?

You don't want it in your joins.  Can you post what you have?

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)

Select allOpen in new window

Thanks for your help.