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

Question

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.

score 196 · Accepted Answer · answered Oct 19 '11 at 05:46

196

Sounds like you're working in just one table so something like this:

update your_table
set B = A
where B is null

answered Oct 19 '11 at 05:46

mu is too short

426,620
70
833
800

One strange thing that I noticed about this command is in phpMyAdmin when you type this command and click on 'simulate query', it says 'no rows affected', but if you actually run the query, it works. I know that this bug is not directly about the question above, but it might be good to know. – Saeed Vrz May 19 '23 at 07:38

score 27 · Answer 2 · answered Oct 19 '11 at 05:53

I would do it this way:

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

COALESCE is a function that returns its first non-null argument.

In this example, if B on a given row is not null, the update is a no-op.

If B is null, the COALESCE skips it and uses A instead.

score 25 · Answer 3 · answered Oct 19 '11 at 05:46

25

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

answered Oct 19 '11 at 05:46

Icarus

63,293
14
100
115

score 6 · Answer 4 · edited Jul 16 '19 at 01:02

6

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;

edited Jul 16 '19 at 01:02

Bhargav Rao

50,140
28
121
140

answered Oct 25 '16 at 05:50

Waruna Manjula

3,067
1
34
33

score 6 · Answer 5 · answered Oct 19 '11 at 05:46

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

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

5 Answers5

Linked