Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Update every row with a value from another column from same table

I have the following example of table content

+ Column1 | Column2 | Column3 +
+---------|---------|---------+
+   1     | val.txt |         +
+   2     | test.xls|         +
+   3     | abc.dwg |         +
+   4     | y.txt   |         +
+---------|---------|---------+

I want to update Column3 with information from Column2, which means I want to (in this case) extract the extension from Column2 and put it in Column3.

I want the result to look like

+ Column1 | Column2 | Column3 +
+---------|---------|---------+
+   1     | val.txt |   .txt  +
+   2     | test.xls|   .xls  +
+   3     | abc.dwg |   .dwg  +
+   4     | y.txt   |   .txt  +
+---------|---------|---------+

How to do that with an UPDATE statement?

I know how to extract the extension:

SUBSTRING(Column2, LEN(Column2)-3, LEN(Column2)) AS Extension
like image 635
Snake Eyes Avatar asked Dec 18 '12 08:12

Snake Eyes


People also ask

How can we UPDATE one column value with another column in the same table?

In such a case, you can use the following UPDATE statement syntax to update column from one table, based on value of another table. UPDATE first_table, second_table SET first_table. column1 = second_table. column2 WHERE first_table.id = second_table.

Can you modify the rows in a table based on values from another table?

You can update an entire row in one table with values from a row in another table. Suppose that a master class schedule table needs to be updated with changes that have been made in a copy of the table. The changes are made to the work copy and merged into the master table every night.


1 Answers

How about this:

UPDATE dbo.YourTable
SET Column3 = SUBSTRING(Column2, LEN(Column2)-3, LEN(Column2)) 

If needed, you can also include a WHERE clause to limit the rows being updated, e.g.:

UPDATE dbo.YourTable
SET Column3 = SUBSTRING(Column2, LEN(Column2)-3, LEN(Column2)) 
WHERE Column3 IS NULL

or something like that.

like image 87
marc_s Avatar answered Sep 19 '22 17:09

marc_s