SQL - Update a field value depending on if a record is found in another file
Last Post 17 Jan 2013 12:55 AM by Craig Scargill. 2 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
Craig Scargill
New Member
New Member
Posts:27

--
16 Jan 2013 06:45 AM

Hi

Hope someone can help I am wanting to update a file and set a field to Y or N depending on if a matching record is found in another file.  Something like this

UPDATE style SET flag1 = CASE

WHEN SMSTYL in (Select INSTYL from INSTYLE) THEN 'Y'

ELSE 'N'

END

So if SMSTYL in file STYLE is found in file INSTYLE I want to set flag1 = 'Y'

 

Thanks

Jim_IT
Basic Member
Basic Member
Posts:217

--
16 Jan 2013 08:44 AM

Craig,

Your psuedo logic was right on, so here's the DB2 format.

update qtemp.style set flag1 = coalesce((select distinct 'Y' from qtemp.instyle where instyl = smstyl), 'N')

You can test this fairly quickly.

create table qtemp.style
(FLAG1 Char(1),
SMSTYL Char(1))

create table qtemp.instyle
(INSTYL Char(1))

insert into qtemp.style
(values ('Y', 'A'));
insert into qtemp.style
(values ('Y', 'B'));
insert into qtemp.style
(values ('Y', 'C'));

insert into qtemp.instyle
(values 'B');

update qtemp.style set flag1 = coalesce((select distinct 'Y' from qtemp.instyle where instyl = smstyl), 'N')

select * from qtemp.style

Jim

Craig Scargill
New Member
New Member
Posts:27

--
17 Jan 2013 12:55 AM
Thanks for your reply Jim. That works great
You are not authorized to post a reply.

Acceptable Use Policy