CODEWITHSUNDEEP
sqldatabaseoracle-database
Jules
Jules

Reputation: 245

How to update null data fields to match another columns data field?

I have a table with a 'user_id' column. Within that same table I have another data field labeled 'GMID'. Within that GMID column there are some fields that are null, the ones that aren't null have values that match the user_id data field within that row. Is there a way to create a script or query that will update all null fields in the GMID column to match the corresponding data values in the user_id row within that row? Are there any best practices I should follow, different approaches? Thanks in advance for anyone that can help.

Upvotes: 0

Views: 814

Answers (2)

DanielC
DanielC

Reputation: 367

Another approach would be using the 'coalesce' function. It will return the first non-null value. This approach does not involve data changes on your table. On a query you can 'select coalesce(GMID, user_id) as GMID ...' it will return the first column that is not null.

Documentation for oracle DB: http://docs.oracle.com/cd/B28359_01/server.111/b28286/functions023.htm

Update: I just reversed the name of the columns inside the coalesce function...

Upvotes: 1

Tatiana
Tatiana

Reputation: 1499

Of course there is

UPDATE your_table
SET GMID=user_id
WHERE GMID IS NULL

But you even don't need WHERE if GMID always should be same as user_id.

By the way, why do you need two columns with same data in one table?

Upvotes: 3

Related Questions