CODEWITHSUNDEEP
mysqlsql
user1002358
user1002358

Reputation: 2942

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.

Upvotes: 128

Views: 209470

Answers (5)

Waruna Manjula
Waruna Manjula

Reputation: 3477

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;

Upvotes: 8

Bill Karwin
Bill Karwin

Reputation: 562260

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.

Upvotes: 29

rwilliams
rwilliams

Reputation: 21487

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

Upvotes: 6

mu is too short
mu is too short

Reputation: 434606

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

update your_table
set B = A
where B is null

Upvotes: 203

Icarus
Icarus

Reputation: 63956

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

Upvotes: 25

Related Questions