Improve company productivity with a Business Account.Sign Up

x
?
Solved

Fill blank spaces in SQL table

Posted on 2009-06-30
9
Medium Priority
?
278 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 61

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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
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 61

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 61

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: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

This article will show a step by step guide on how to mask column values in Oracle 12c using DBMS_REDACT full redaction option. This option is available on licensed Oracle Enterprise edition as part of Oracle's Advanced Security.
In this article, we will see two different methods to recover deleted data. The first option will be using the transaction log to identify the operation and restore it in a specified section of the transaction log. The second option is simpler and c…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

588 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