?
Solved

Query Syntax

Posted on 2011-04-26
13
Medium Priority
?
183 Views
Last Modified: 2012-05-11
okay heres what im trying to do..

i want to insert into an email into the email field value if the field is null if its not null do nothing

so...

select * from table1 where userid = 'aaaa'
if email is null then
insert into table1(email) values(123@mail.com) where userid ='aaaa'
(else do nothing)

a little help please
0
Comment
Question by:plusone3055
  • 8
  • 5
13 Comments
 
LVL 33

Expert Comment

by:knightEknight
ID: 35467915
insert into table1(email) values(123@mail.com) where userid ='aaaa' and email is null
0
 
LVL 33

Expert Comment

by:knightEknight
ID: 35467922
oh wait, duh, that won't work.  You want an UPDATE, not an INSERT:

update table1
set email = '123@mail.com'
where userid ='aaaa'
  and email is null
0
 
LVL 33

Expert Comment

by:knightEknight
ID: 35467934
... and to catch EMPTY email (instead of just null) do this:

update table1
set email = '123@mail.com'
where userid ='aaaa'
  and isNull(email,'') = ''
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 22

Author Comment

by:plusone3055
ID: 35468019
knight:
thanks for the quick response...
which one do i use..

if this makes any difference.. the email will either be empty or matching what i set it at

so the email txtbox will either be blank or it wont be blank.. its its blank(empty) fill it with the email address.. if its not blank/empty do nothing

thanks again lemmie know which one to use and I'll give you the 2 grand :)                            
0
 
LVL 33

Expert Comment

by:knightEknight
ID: 35468039
I recommend this version:

update table1
set email = '123@mail.com'
where userid ='aaaa'
  and isNull(email,'') = ''

It will update the email column if the column is null or blank (e.g. empty string: '' )
0
 
LVL 22

Author Comment

by:plusone3055
ID: 35468193
i copied from above


Error line 4 column 6
ISNULL invalid identifier :(

this is oracle by the way
0
 
LVL 33

Expert Comment

by:knightEknight
ID: 35468204
oooh, oracle ....

in that case just do this:

update table1
set email = '123@mail.com'
where userid ='aaaa'
  and email is null
0
 
LVL 33

Accepted Solution

by:
knightEknight earned 2000 total points
ID: 35468225
optionally, to check for a blank condition, do this:

update table1
set email = '123@mail.com'
where userid ='aaaa'
  and ( email = '' or email is null )

I'm assuming that Oracle strings are denoted by single-quotes ' instead of double-quotes "
0
 
LVL 22

Author Closing Comment

by:plusone3055
ID: 35468522
excellent thanks..

interesting though.. the update shows up in the database but not in the textbox that would normally display it.. any thoughts ?
0
 
LVL 33

Expert Comment

by:knightEknight
ID: 35468661
Did you commit the update?  Does the page need to be refreshed?
0
 
LVL 22

Author Comment

by:plusone3055
ID: 35468679
how do i commit it ??
0
 
LVL 33

Expert Comment

by:knightEknight
ID: 35468694
after the update, just do this:

commit;
0
 
LVL 22

Author Comment

by:plusone3055
ID: 35468749
yup that did it

Thanks
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

839 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