Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Fill blank spaces in SQL table

Posted on 2009-06-30
9
Medium Priority
?
268 Views
Last Modified: 2012-08-14
Hi

I've got 2 columns fields in a table.

Field1 is awlays populated with data
Field 2 is mostly blank.

How do I populate field2 with data from field 1 if field 2 is empty, but only if field2 is empty?

Thanks
0
Comment
Question by:Ed
9 Comments
 
LVL 6

Assisted Solution

by:jwenting
jwenting earned 400 total points
ID: 24744208
You want to do that on inserting a record or at a later date?
If the former, use a trigger on post-insert. See documentation for your database engine on how to implement triggers.

If the latter, a simple update query will do:
update mu_table set column2 = column1 where column2 is null;

commit that and you're done.
0
 
LVL 5

Assisted Solution

by:mnialon
mnialon earned 400 total points
ID: 24744212
hello
please try the following :

update t1 set field2 = field1 where field2 is null

regards,

0
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 800 total points
ID: 24744260
The above are correct.  However, if your field can be empty string '' instead of null, then just adjust like this:
update your_table_name
set field2 = field1
where isnull(field2, '') = ''

Open in new window

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 18

Assisted Solution

by:brejk
brejk earned 400 total points
ID: 24744292
If blank means an empty string:

UPDATE YourTable
SET Field2 = Field1
WHERE Field2 = ''
0
 
LVL 18

Expert Comment

by:brejk
ID: 24744295
@mwvisa1: If field2 is indexed the better would be:

update your_table_name
set field2 = field1
where field2 = '' or field2 is null
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 24744317
I typically don't worry about performance on one time data cleanup activities, but you are correct that should always code to take advantage of indexes.  As a programmer, I like checking for NULL first then compare as string, BTW. :) habit!

0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 24744329
@brejk: And I should correct that, I don't worry on simple stuff like this. :) I would be in trouble if I ran anything too nasty on a production system...
0
 
LVL 18

Expert Comment

by:brejk
ID: 24744344
@mwvisa1: What a sad world would be without our habits ;-)
0
 
LVL 14

Expert Comment

by:shru_0409
ID: 24744901
nvl(field2,field1)
or
nullif(field1,field2)
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

926 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