Menu
I want to update two columns in a table. The value of the second column is dependant upon the first; If the first is Null the second's value is 'false', otherwise it is 'true'.
Can I do this within TSQL or do I need to work out the values separately in my code before hand and change the SQL to suit. I was looking for something like:
DECLARE @NewColumnValue as nvarchar(10);
SELECT @NewColumnValue = ColumnY From TableY
UPDATE TableX
SET Column1 = @NewColumnValue,
Column2 = (IF (@NewColumnValue IS NULL) THEN 'False' ELSE 'True');
455
Yes! This works because MySQL doesn't update the row, if there is no change, as mentioned in docs: If you set a column to the value it currently has, MySQL notices this and does not update it. Yes!
The CASE expression allows a statement to return one of several possible results, depending on which of several condition tests evaluates to TRUE. You must include at least one WHEN clause within the CASE expression; subsequent WHEN clauses and the ELSE clause are optional.
First, specify the table name that you want to change data in the UPDATE clause. Second, assign a new value for the column that you want to update. In case you want to update data in multiple columns, each column = value pair is separated by a comma (,). Third, specify which rows you want to update in the WHERE clause.
WHERE clause can be used with SQL UPDATE to add conditions while modifying records. Without using any WHERE clause, the SQL UPDATE command can change all the records for the specific columns of the table.
You are looking for the CASE expression:
Column2 = CASE WHEN @NewColumnValue IS NULL THEN 'False' ELSE 'True' END
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
