SQL set values of one column equal to values of another column in the same table

I have a table with two DATETIME columns.

One of them is never NULL, but one of them is sometimes NULL.

I need to write a query which will set all the NULL rows for column B equal to the values in column A.

I have tried this example but the SQL in the selected answer does not execute because MySQL Workbench doesn't seem to like the FROM in the UPDATE.

mysql
sql

173739 次浏览

小开

UPDATE YourTable
SET ColumnB=ColumnA
WHERE
ColumnB IS NULL
AND ColumnA IS NOT NULL

小开

最佳答案

听起来好像你只在一张桌子上工作，所以是这样的:

update your_table
set B = A
where B is null

小开

I don't think that other example is what you're looking for. If you're just updating one column from another column in the same table you should be able to use something like this.

update some_table set null_column = not_null_column where null_column is null

小开

我会这样做:

UPDATE YourTable SET B = COALESCE(B, A);

COALESCE 是一个返回其第一个非空参数的函数。

在本例中，如果给定行上的 B 不为空，则更新为无操作。

如果 B 为空，COALESCE 将跳过它，而使用 A。

小开

Here is sample code that might help you coping Column A to Column B:

UPDATE YourTable
SET ColumnB = ColumnA
WHERE
ColumnB IS NULL
AND ColumnA IS NOT NULL;