DECLARE @sql NVARCHAR(MAX);
SET @sql = N'';
SELECT @sql = @sql + '
' + QUOTENAME(name) + ' = CASE
WHEN ' + QUOTENAME(name) + ' = ''NULL'' THEN NULL ELSE '
+ QUOTENAME(name) + ' END,'
FROM sys.columns
WHERE [object_id] = OBJECT_ID('dbo.YourTableName')
AND system_type_id IN (35,99,167,175,231,239);
SELECT @sql = N'UPDATE dbo.YourTableName SET ' + LEFT(@sql, LEN(@sql)-1) + ';';
PRINT @sql;
--EXEC sp_executesql @sql;
The principal difficulty here is probably that the alternative value of MemoText you want to use in case the fieldnum=142 value is null is in a different row. It would be much easier if it was just a different column of the same row. That way you could use the pattern suggested by Manu – I mean specifically this part:
set <field_to_update> = ISNULL(<field142>, <field17>)
The ISNULL function will evaluate to <field142>
if that value is not null, otherwise the function will evaluate to <field17>
.
So is it possible to arrange two values from different rows on the same row? Yes, there are at least two ways to do so.
First, you could replace the eqanswer reference with a derived table that groups eqanswer rows by entitynum and uses conditional aggregation to present field 142 and field 17 values on the same row, like this:
SELECT
entitynum,
MAX(CASE fieldnum WHEN 142 THEN MemoText END) AS MemoText142,
MAX(CASE fieldnum WHEN 17 THEN MemoText END) AS MemoText17
FROM
eqanswer
WHERE
entityrole = 'DEFENDANT'
AND fieldnum IN (142, 17)
GROUP BY
entitynum
Now you can use the above query in the UPDATE statement like this:
UPDATE
xrji_:Usernum
SET
DefendantAddress = TRIM(ISNULL(eqa.MemoText142, eqa.MemoText17))
FROM
(
SELECT
entitynum,
MAX(CASE fieldnum WHEN 142 THEN MemoText END) AS MemoText142,
MAX(CASE fieldnum WHEN 17 THEN MemoText END) AS MemoText17
FROM
eqanswer
WHERE
entityrole = 'DEFENDANT'
AND fieldnum IN (142, 17)
GROUP BY
entitynum
) AS eqa
WHERE
eqa.entitynum = xrji_:Usernum.DefendantEntityNum
;
The other method would require joining the eqanswer table to xrji_:Usernum twice: once to get the field 142 MemoText and once again to get the field 17 MemoText. For this it would be more convenient to rewrite UPDATE to use an explicit join. The complete statement would look something like this:
UPDATE
xun
SET
DefendantAddress = TRIM(ISNULL(eqa142.MemoText, eqa17.MemoText))
FROM
xrji_:Usernum AS xun
LEFT JOIN eqanswer AS eqa142 ON eqa142.entitynum = xun.DefendantEntityNum
AND eqa142.entityrole = 'DEFENDANT'
AND eqa142.fieldnum = 142
LEFT JOIN eqanswer AS eqa17 ON eqa17.entitynum = xun.DefendantEntityNum
AND eqa17.entityrole = 'DEFENDANT'
AND eqa17.fieldnum = 17
WHERE
eqa142.entitynum IS NOT NULL
OR eqa17.entitynum IS NOT NULL
;
You can see that this UPDATE statement uses the same ISNULL pattern as the previous UPDATE does. The difference is only in how the MemoText values from different rows manage to get on the same row. This time they simply come from different, independently joined subsets of eqanswer.
The query uses outer joins instead of inner joins because of the assumption that field 142 or field 17 may be absent for any given entitynum. The query still checks that at least one of the two rows is present – otherwise the corresponding xrji_:Usernum row is not updated at all. If you still would like to update such rows (with a null, apparently), just remove the WHERE clause.
Best Answer
You can use something like
The reference to
Id
in theORDER BY
is just to make it correlated so that SQL Server is less likely to spool the result and replay it for multiple rows. Select a column that is unique for this.BTW: There is a
CHOOSE
function that looks tempting but when this gets expanded out toCASE
the random number function gets copied out too and so this is not suitable for the task.Don't use this
Because it is evaluated as
(And
CASE ABS(CRYPT_GEN_RANDOM(4) % 2) WHEN 0 THEN 2012 WHEN 1 THEN 2015 END
would have the same problem)