How to update the same column for multiple rows in DB2

Question

I need to update all the records in the Client table if address_1 is blank, but address_2 is not. In those cases I want to move address_2 into address_1. Here is my query so far:

UPDATE Client SET Address_1 = 'address1', address_2 = ''
WHERE client_id = 'client_id'

But instead of passing in client_id, I want to update every record.

Leave out the where criteria perhaps -- that will update the entire table? What exactly are you trying to do? — sgeddes
– sgeddes, Commented Mar 4, 2016 at 19:18
I am trying to update our table where address 1 is blank but address 2 is not and move the adress 2 to address 1 — OVO
– OVO, Commented Mar 4, 2016 at 19:20
Update Client Set Address_1 = address_2, address_2 = '' where Address_1 = '' and address_2 <> '' — Iłya Bursov
– Iłya Bursov, Commented Mar 4, 2016 at 19:21
I ended up extracting the data onto excel and writing the script there thanks — OVO
– OVO, Commented Mar 4, 2016 at 19:39

Justin Howard · Accepted Answer · 2016-03-04 20:25:19Z

1

The query you need is

UPDATE client SET address_1 = address_2, address_2 = ''
WHERE address_1 = '' AND address_2 != ''

In the WHERE, it finds all the problem rows, then it moves address_2 to address_1 and blanks out address_2

Note: Make sure you're not confusing empty string '' with NULL. In DB2, those are not the same. If your values are actually NULL, your query would need to be:

UPDATE client SET address_1 = address_2, address_2 = NULL
WHERE address_1 IS NULL AND address_2 IS NOT NULL

answered Mar 4, 2016 at 20:25

Justin Howard

5,6421 gold badge25 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to update the same column for multiple rows in DB2

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related