We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Query Syntax

plusone3055
plusone3055 asked
on
Medium Priority
195 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
Comment
Watch Question

CERTIFIED EXPERT

Commented:
insert into table1(email) values(123@mail.com) where userid ='aaaa' and email is null
CERTIFIED EXPERT

Commented:
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
CERTIFIED EXPERT

Commented:
... and to catch EMPTY email (instead of just null) do this:

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

Author

Commented:
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 :)                            
CERTIFIED EXPERT

Commented:
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: '' )

Author

Commented:
i copied from above


Error line 4 column 6
ISNULL invalid identifier :(

this is oracle by the way
CERTIFIED EXPERT

Commented:
oooh, oracle ....

in that case just do this:

update table1
set email = '123@mail.com'
where userid ='aaaa'
  and email is null
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
excellent thanks..

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

Commented:
Did you commit the update?  Does the page need to be refreshed?

Author

Commented:
how do i commit it ??
CERTIFIED EXPERT

Commented:
after the update, just do this:

commit;

Author

Commented:
yup that did it

Thanks
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.