If..else..in Update Statement?

[pwinters]

Is it possible to have an if else statement in a SQL Update statement?

I want to...

Update Table1
If (field1 != 0)
set field1 = variable
,field2 = variable2
,field3 = variable3
else
field4 = variable4
etc..

What I'm really trying to do is allow null values to be entered in my form. Currently, when a user leaves a field blank, I get a "syntax error in Update statement". When the fields are all filled in, it works fine.

Can this be done? Or would there be a better way to do something like this?

Any help would be appreciated.
Thanks.

 

[malexanian]

Post your SQL. If your command is written properly, there should be no problem with NULLs. Where is the SQL statement residing? (SP, dynamic generation from app)?

 

[pwinters]

Here is my code..it's kinda long


String sql=
"UPDATE " +
"(((Projects INNER JOIN Tables_Status ON Projects.project_id = Tables_Status.Project_Id)" +
" INNER JOIN PAR_Status ON Projects.project_id = PAR_Status.Project_Id)" +
" INNER JOIN BDP_Status ON Projects.project_id = BDP_Status.Project_Id)" +
" INNER JOIN BCV_Status ON Projects.project_id = BCV_Status.Project_Id" +
"if (tloe != 0)" +
"{" +
" SET Tables_Status.TBL_LOE=" + tloe +
", Tables_Status.TBL_DD_TD=" + "#" + tddtd + "#" +
", Tables_Status.TBL_DD_Status =" + tddstat +
", Tables_Status.TBL_DD_WT= Format(#"+ tddwt +"#, 'MM/DD/YYYY')" +
", Tables_Status.TBL_PS_TD= Format(#"+ tpstd +"#, 'MM/DD/YYYY')" +
", Tables_Status.TBL_PS_Status =" +tpsstat +
", Tables_Status.TBL_PS_WT= Format(#"+ tpswt +"#, 'MM/DD/YYYY')" +
", Tables_Status.TBL_Code_ETC= Format(#"+ tcetc +"#, 'MM/DD/YYYY')" +
", Tables_Status.TBL_Code_Status =" + tcstat +
", Tables_Status.TBL_UT_ETC= Format(#"+ tuetc +"#, 'MM/DD/YYYY')" +
", Tables_Status.TBL_UT_Status =" + tutstat +
"}" +
"else" +
", PAR_Status.PARLOE =" + ploe +
", PAR_Status.PAR_DD_TD= Format(#"+ pddtd +"#, 'MM/DD/YYYY')" +
", PAR_Status.PAR_DD_Status =" + pddstat +
", PAR_Status.PAR_DD_WT = Format(#"+ pddwt +"#, 'MM/DD/YYYY')" +
", PAR_Status.PAR_PS_TD= Format(#" + ppstd +"#, 'MM/DD/YYYY')" +
", PAR_Status.PAR_PS_Status =" +ppsstat +
", PAR_Status.PAR_PS_WT= Format(#"+ ppswt +"#, 'MM/DD/YYYY')" +
", PAR_Status.PAR_Code_ETC= Format(#"+ pcetc +"#, 'MM/DD/YYYY')" +
", PAR_Status.PAR_Code_Status =" + pcdstat +
", PAR_Status.PAR_UT_ETC= Format(#"+ puetc +"#, 'MM/DD/YYYY')" +
", PAR_Status.PAR_UT_Status =" + putstat +
", BDP_Status.BDP_LOE =" + bdploe +
", BDP_Status.BDP_DD_TD= Format(#"+ bddtd +"#, 'MM/DD/YYYY')" +
", BDP_Status.BDP_DD_Status =" + bdpddstat +
", BDP_Status.BDP_DD_WT= Format(#"+ bddwt +"#, 'MM/DD/YYYY')" +
", BDP_Status.BDP_PS_TD= Format(#"+ bdptd +"#, 'MM/DD/YYYY')" +
", BDP_Status.BDP_PS_Status =" + bdppsstat +
", BDP_Status.BDP_PS_WT= Format(#"+ bdpwt +"#, 'MM/DD/YYYY')" +
", BDP_Status.BDP_Code_Status =" + bdpcstat +
", BDP_Status.BDP_Code_ETC= Format(#"+ bdcetc +"#, 'MM/DD/YYYY')" +
", BDP_Status.BDP_UT_Status =" + bdputstat +
", BDP_Status.BDP_UT_ETC= Format(#"+ bduetc +"#, 'MM/DD/YYYY')" +
", BCV_Status.BCV_LOE =" + bcvloe +
", BCV_Status.BCV_DD_TD= Format(#"+ bcdtd +"#, 'MM/DD/YYYY')" +
", BCV_Status.BCV_DD_Status =" + bcvddstat +
", BCV_Status.BCV_DD_WT= Format(#"+ bcdwt +"#, 'MM/DD/YYYY')" +
", BCV_Status.BCV_PS_TD= Format(#"+ bcptd +"#, 'MM/DD/YYYY')" +
", BCV_Status.BCV_PS_Status =" + bcvpsstat +
", BCV_Status.BCV_PS_WT= Format(#"+ bcpwt +"#, 'MM/DD/YYYY')" +
", BCV_Status.BCV_Code_Status =" + bcvcstat +
", BCV_Status.BCV_Code_ETC= Format(#"+ bccetc +"#, 'MM/DD/YYYY')" +
", BCV_Status.BCV_UT_Status =" + bcvutstat +
", BCV_Status.BCV_UT_ETC= Format(#"+ bcuetc +"#, 'MM/DD/YYYY')" +
" WHERE Projects.project_id=Tables_Status.Project_Id" +
" AND Tables_Status.Project_ID=PAR_Status.Project_Id" +
" AND PAR_Status.PAR_Status_Id=BDP_Status.BDP_Status_Id" +
" AND BDP_Status.BDP_Status_Id=BCV_Status.BCV_Status_Id" +
" AND BCV_Status.BCV_Status_Id=" + bcvstatid;


I'm using jsp to pull the info from the website form and then sql to append the data to a database.

 

[SQLBill]

One thing you might check is the schema for the table(s). Let's say you are UPDATEing columns 1 and 3 of a table. If the schema for the table set column 2 to NOT NULL, then the update will return an error. You can still update the table, but you must enter something, even if it's a blank, into column 2.

ie.

UPDATE myTable
SET c1 = somevalue
SET c2 = ""
set c3 = somevalue

-SQLBill

 

[SQLBill]

By the way, to answer your question....yes T-SQL permits IF ELSE, this is the format (see the Books Online)

Update Table1
If (field1 != 0)
BEGIN
set field1 = variable
,field2 = variable2
,field3 = variable3
END
else
BEGIN
set field4 = variable4
etc..

IF statements need the BEGIN and END.

-SQLBill

 

[malexanian]

Instead of putting the criteria in the SQL string, why not do the if...else... in jsp and build the string there? Then you can select what fields to include on the Java side. You end up with a shorter SQL statement to pass to the server, which is always good:

String sql = "UPDATE tbl JOIN tbl ON ..."

if ( tloe != 0 ) {
sql += "SET a, b, c..."
}
...


T-SQL does NOT permit the use of IS ELSE within a SQL statement block. There are other options within T-SQL, but stick with the Java side; it's easier.

Are you checking for SQL injection? Do a search online for the security risk this poses.

 

[pwinters]

SQLBill & Malexanian, Thanks for your help!

SQLBill,
I checked my db, and everything looks ok to me. It seems the text fields are accepting null values, but the dates and drop down boxes in the forms will not accept Nulls. I tried setting them equal to ""(space) when I got the value, but that isn't working either.

Malexanian, I'm still pretty new to all of this, so I'm a little confused by your suggestion.

Could you ellaborate a little more? In this file, I'm getting all the values entered on the form. Are you suggesting I do the If statment after I pull the values and then Update the table? Maybe with multiple Sql statements? I'm sorry, I'm just not sure how to do this. I assumed my sql would just accept the null values, but that doesn't seem to be working.

Thanks again for your help.

 

[malexanian]

In the jsp page that receives the submitted form, build the SQL string. However, don't pass everything to the SQL Server. Place the if else logic inside the jsp page. Instead of what you proposed:

String sql = "UPDATE tbl SET if (a) { b, c } else { d, e }"

Do:

String sql = "UPDATE tbl SET "
if ( a ) {
sql += "b, c"
}
else {
sql += "d, e"
}

In the first case, you make SQL Server do the work. In the second case, your jsp page does the work. The second should be easier to write because program flow is easier in Java.

 

[SQLBill]

pwinters,

I think you misunderstood my script. You said "I tried setting them equal to ""(space)" But that is doublequotedoublequote. There isn't any space in between the two doublequotes.

-SQLBill

 

[pwinters]

SQLBill,

I solved the problem of Nulls not being allowed on the text boxes and drop down boxes with: Table Column = db.formatInsertStringValue(VARIABLE, true). It still doesn't work for the dates though. I tried setting them to " " , but that doesn't seem to work for me either.

Malexanian,

Thanks for your help! I think I'm on the right track now...BUT...

I'm getting an error with the code though. It's expecting a } instead of the ; at the end of tstatusid...I have one, but it doesn't seem to recognize it. Any suggestions?

String sql=
"UPDATE " +
"(((((Projects INNER JOIN Tables_Status ON Projects.project_id = Tables_Status.Project_Id)" +
" INNER JOIN PAR_Status ON Projects.project_id = PAR_Status.Project_Id)" +
" INNER JOIN BDP_Status ON Projects.project_id = BDP_Status.Project_Id)" +
" INNER JOIN AST_Status ON Projects.project_id = AST_Status.Project_Id)" +
" INNER JOIN BCV_Status ON Projects.project_id = BCV_Status.Project_Id)" +
"if (db.formatInsertStringValue(tloe, true)!=0)"+
{
sql+=
" SET "+
"Tables_Status.TBL_LOE=" + db.formatInsertStringValue(tloe, true) +
", Tables_Status.TBL_DD_TD=" + "#" + tddtd + "#" +
", Tables_Status.TBL_DD_Status =" + db.formatInsertStringValue(tddstat, true) +
", Tables_Status.TBL_DD_WT= Format(#"+ tddwt +"#, 'MM/DD/YYYY')" +
", Tables_Status.TBL_PS_TD= Format(#"+ tpstd +"#, 'MM/DD/YYYY')" +
", Tables_Status.TBL_PS_Status =" +db.formatInsertStringValue(tpsstat, true) +
", Tables_Status.TBL_PS_WT= Format(#"+ tpswt +"#, 'MM/DD/YYYY')" +
", Tables_Status.TBL_Code_ETC= Format(#"+ tcetc +"#, 'MM/DD/YYYY')" +
", Tables_Status.TBL_Code_Status =" + db.formatInsertStringValue(tcstat, true) +
", Tables_Status.TBL_UT_ETC= Format(#"+ tuetc +"#, 'MM/DD/YYYY')" +
", Tables_Status.TBL_UT_Status =" + db.formatInsertStringValue(tutstat, true) +
" WHERE Tables_Status.Project_Id = Projects.Project_Id " +
" AND Tables_Status.Project_Id =" + tstatusid;}
else
{
sql+=
" SET "+
"Tables_Status.TBL_LOE=" + db.formatInsertStringValue(tloe, true) +
", PAR_Status.PARLOE =" +db.formatInsertStringValue(ploe, true) +
", PAR_Status.PAR_DD_TD= Format(#"+ pddtd +"#, 'MM/DD/YYYY')" +
.....etc....
}

Hopefully, you can see what I'm missing.
THANKS!

 

[malexanian]

Your if line has quotes around it. I think that's it.

 

[pwinters]

Thanks for checking my code.

When I remove the quotes around the if statement, I get the following error:
';' expected. " INNER JOIN BCV_Status ON Projects.project_id = BCV_Status.Project_Id)" ^

I didn't think I should put a ; before the if. Am I missing something else?

Thanks again.

 

[malexanian]

Jiminy jillickers! How did I miss that glaring FROM clause? I hit the same wall when I migrated from Access SQL: In SQL Server, your UPDATE statements can only update one table at a time. Check Books Online for the proper syntax.

In general:

UPDATE <table-to-be-updated>
SET a = b,
c = d...
FROM
<table-to-be-updated> JOIN ...

Note that fields a and c must be from the one table you are updating. b and d can be expressions from the entire FROM clause below. This means you will have to split your updates into several updates for each table being updated.