Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 543
  • Last Modified:

How to set a Yes 'Y' or No 'N' flag in and SQL query

I am writing a query in SQL and wanted to know how to set an indicator of 'Y' for yes or 'N' for no if a name is in a table.  

For example, if John Doe is located in tblRegistration, RegistrationFlag = Y.
0
sparklineyes
Asked:
sparklineyes
  • 3
  • 2
  • 2
  • +4
1 Solution
 
derekkrommCommented:
You can use a bit field, which essentially does the same thing - except it uses a 1 or 0.

Set it to 1 = 'Y' and 0 = 'N'.

If you need help with the specific query could you post a little more info on what you're trying to do? Do you need to update a separate table/field, or just return Y/N?
0
 
mbizupCommented:
What database are you using? Access?

Use an update query:
Assuming a text field:

UPDATE YourTable
SET YourField = 'Y'
Where YourName = [Enter a Name:]

Or if the field is boolean:

UPDATE YourTable
SET YourField = True
Where YourName = [Enter a Name:]

0
 
sabeeshCommented:

                Your Question is not quite clear.is the RegistrationFlag in the same table?. if  it is in the same table
                why cant you put a condition and set 'N'/'Y'.Please give more details.
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
rsomasundarCommented:
Hi,
You can consider the @strName is an parameter required to verify the name in tblRegistration
Ex: @strName = 'John Doe'

Using CASE stmt, it should be updated as 'Y' or 'N' as per the condition given like Name = @strName

Update tblRegistration
set RegistrationFlag = CASE
when Name = @strName then 'Y'
else 'N'
END

You can build seperate stored procedure to do this task, if you want individualy do this.
           
0
 
harfangCommented:
Let me make the following assumptions:

You have a table tblPeople ( ID, FirstName, LastName ), and a table tblRegistration ( RegID, PID, etc ), PID being the foreign key linking back to tblPeople. You now want to show 'Y' or 'N' for every record in tblPeople, depending on whether that person exists in the table tblRegistration.

As you see, we need a bit more details to help you write the query. In any case, this would then work:

SELECT
    tblPeople.*,
    ID In (Select PID From tblRegistration) As Registered
FROM tblPeople

This creates a simple boolean field, which you can format as Y/N depending on the database and front-end application you are using. If you want to generate the Y/N directly, we would need to know what database you are using. For example, this works in Access:

SELECT
    tblPeople.*,
    IIf(ID In (Select PID From tblRegistration), 'Y', 'N') As Registered
FROM tblPeople

And something like this should work in SQL Server:

SELECT
    tblPeople.*,
    (case when ID In (Select PID From tblRegistration) then 'Y' else 'N') As Registered
FROM tblPeople

Cheers!
(°v°)
0
 
sparklineyesAuthor Commented:
The data is not coming from the same table.  I am using two tables;  one table is CustomerContacts and the other is HistoricalCustomers.  The field that is in both tables would be the telephone number.  If the phone number is in the HistoricalCustomers table, then I would like to update a field called HistCustContacts in the CustomerContacts table with a 'Y' if it is in the table or a 'N' if it is not in the table.
0
 
derekkrommCommented:
update CustomerContacts
set HistCustContacts = 'Y'
where phoneNumber in (select phoneNumber from HistoricalCustomers)
go
update CustomerContacts
set HistCustContacts = 'N'
where phoneNumber not in (select phoneNumber from HistoricalCustomers)
go

or if you're going to use a bit field, make the 'Y' 1 and 'N' 0
0
 
sparklineyesAuthor Commented:
SQL is being used.
0
 
harfangCommented:
Derek has it nailed. -- (°v°)
0
 
Scott PletcherSenior DBACommented:
UPDATE CustomerContacts
SET HistCustContacts = CASE WHEN hc.[phone number] IS NULL THEN 'N' ELSE 'Y' END
FROM CustomerContacts cc
LEFT OUTER JOIN HistoricalCustomers hc ON cc.[phone number] = hc.[phone number]
--WHERE HistCustContacts IS NOT NULL   --if you want to only update new/previously unset rows
0
 
derekkrommCommented:
sparklineyes, any update?
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 3
  • 2
  • 2
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now