Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
hutty
Starting Member
37 Posts |
 Posted - 2012-02-09 : 15:17:09
|
| Hi, I have a web project where I using UPDATE command to update the database. The webform has about 520 input values, 14 days x 26 lines. My problem is that a user may input values on Day1. Then do the same on Day2. But Day1 is erase because the field is null. Is there a way to UPDATE the database with only the fields that have values each time the user submits?I did read about the Coalesce function. I can also pad my coding with a select statement to retrieve the database from previous submission, but that would be a lot of lines to add.Thanks |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2012-02-09 : 15:25:11
|
update tableset column1 = coalesce(<submitted_value>, column1)So if the submitted value is null the column will not change.Is that what you mean?
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
hutty
Starting Member
37 Posts |
Posted - 2012-02-09 : 15:36:59
|
| Here is the command for one of the lines. A person can put a value in Sun1. Then come back next day for Mon1. They would leave Sun1 blank and the update will overwrite previous value with null."UPDATE Table1 Set ID =@ID,Sun1 = @Sun1,Mon1 =@Mon1, Tue1= @Tue1, Wed1=@Wed1, Thu1= @Thu1, Fri1=@Fri1, Sat1=@Sat1, Sun2=@Sun2, Mon2=@Mon2, Tue2=@Tue2, Wed2=@Wed2, Thu2=@Thu2, Fri2=@Fri2, Sat2=@Sat2 where ID = @ID "Webfred, do I need put coalese before each day?Thanks |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2012-02-09 : 16:11:03
|
yes and you don't need to update the ID because it will never change."UPDATE Table1 Set Sun1 = COALESCE(@Sun1,Sun1),Mon1 =COALESCE(@Mon1,Mon1), ... where ID = @ID "
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
hutty
Starting Member
37 Posts |
Posted - 2012-02-09 : 16:12:33
|
| Thanks WebFred. I will give it a shot. |
|
|
|
hutty
Starting Member
37 Posts |
Posted - 2012-02-10 : 07:48:22
|
| I am still getting the same results where previous values in database are overwritten by null. I think the hickup is on the parameter command.Set Sun1 = COALESCE(@Sun1,Sun1)mycommand.Parameters.Add("@Sun1", SqlDbType.NVarChar).Value = Trim(Sun1.Text.ToString)Thanks |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2012-02-10 : 09:01:17
|
Are you sure that @Sun1 is NULL if the user enters nothing? or might it be "" (empty string)?If empty string then you coudl do this:Set Sun1 = COALESCE(NullIf(@Sun1, ''), Sun1) of it you need to guard against both empty string and a string that only contains blanks then:Set Sun1 = COALESCE(NullIf(RTrim(@Sun1), ''), Sun1) |
|
|
|
hutty
Starting Member
37 Posts |
Posted - 2012-02-10 : 09:59:22
|
| That works.Set Sun1 = COALESCE(NullIf(RTrim(@Sun1), ''), Sun1) Thanks guys. |
|
|
|
|
|
|
|
|
UPDATE IF NOT NULL