CODEWITHSUNDEEP
sqlsqlite
luis.ap.uyen
luis.ap.uyen

Reputation: 1364

How can I update a column if another one is empty (but not NULL)?

I have this table in SQLite:

clients
-----------------
id
name
coords
waypoint

And whenever I set a waypoint for a client, I want to ensure that if they don't have any value in column coords (I mean, empty value), this will take the same value as in column waypoint.

This is a sample of how I most approached the solution, but it will not work if there is already any value in coords:

UPDATE clients SET waypoint = '39.232,0.2323', coords = IFNULL(NULLIF(LENGTH(coords), 0), '39.232,0.2323') WHERE id=1

In case coords had already a value, like '38.09107838452561,-3.788372494 ', it is updated to '30'. Why is that?

SQLite is limited, so there are simple functions in other SQL engines that I can't use here.

Upvotes: 0

Views: 51

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

You can use COALESCE() for this:

UPDATE clients
    SET waypoint = '39.232,0.2323',
        coords = COALESCE(coords, '39.232,0.2323')
    WHERE id = 1;

EDIT:

If by "empty value" you mean a zero-length string, then just modify the logic:

        coords = COALESCE(NULLIF(coords, ''), '39.232,0.2323')

Upvotes: 2

forpas
forpas

Reputation: 164089

In case coords had already a value, like '38.09107838452561,-3.788372494', it is updated to '30'. Why is that?

Because the length of the string '38.09107838452561,-3.788372494' is 30.
NULLIF(LENGTH(coords), 0) returns 30 since 30 <> 0.
Finally IFNULL(NULLIF(LENGTH(coords), 0) returns 30 since NULLIF(LENGTH(coords), 0) is not null.

I think what you need is this:

UPDATE clients 
SET waypoint = '39.232,0.2323', 
    coords = CASE
      WHEN coords IS NULL OR coords = '' THEN '39.232,0.2323' 
      ELSE coords
    END
WHERE id = 1;

Upvotes: 1

Related Questions