UPDATE TABLE...

[tran008]

Is there an easier way to re-write this update, beside updating 1 at a time with the update statement?

thanks

UPDATE tbl1 SET col1 = ''

UPDATE tbl1 SET col1 = col1 + '-YY'
WHERE col2 IS NULL AND col3is IS NOT NULL AND col3is = 'Y' AND col3b = 0AND col2 IS NULL

UPDATE tbl1 SET col1 = col1 + '-AA'
WHERE col2 IS NULL AND col4 IS NOT NULL AND col4 = 'Y' AND col4b = 0 AND col2 IS NULL

UPDATE tbl1 SET col1 = col1 + '-BB'
WHERE col2 IS NULL AND col5 IS NOT NULL AND col5 = 'Y'AND col5b = 0 AND col2 IS NULL

UPDATE tbl1 SET col1 = col1 + '-XC'
WHERE col2 IS NULL AND col6 IS NOT NULL AND col6 = 'Y' AND col6b = 0

 

[SQLSister]

You could probably use a case statement. Look up the syntax in Books Online.

BTW, your current statments are unnecessarily complicated as well.
WHERE col2 IS NULL AND col3is IS NOT NULL AND col3is = 'Y' AND col3b = 0AND col2 IS NULL

would proably get the same results as
WHERE col2 IS NULL AND col3is = 'Y' AND col3b = 0

becasue you don;t need to specify col2 being null twice and by definition col3 is not null if it has the value of 'y'

The other statements could be handled simlarly.


Questions about posting. See faq183-874