CODEWITHSUNDEEP
sqlpostgresql
Kyle Jenkins
Kyle Jenkins

Reputation: 1

Writing an UPDATE query with LIKE

I have two tables named TableA and TableB. Both TableA and TableB contain the following columns: name, member_id. TableB currently contains the "correct" values for member_id, whereas TableA does not.

What I'd like to do is migrate all of the values in column member_id of TableB to the respective column in TableA (matched by the values in column name column). However, sometimes the names are typed slightly differently between the two tables.

I wrote this following SQL query, though the subquery under WHEN EXISTS is not correct. Can someone assist me with this? Thanks.

UPDATE TableA
SET member_id = 
CASE
    WHEN EXISTS
    (SELECT member_id
    FROM TableB
    WHERE TableB.name ILIKE '%' || 'TableA.name' || '%'
    LIMIT 1)

    THEN member_id

    ELSE '1'
END
WHERE 
TableA.member_id = '0'

Also, I set it so that when the member_id is unknown, it is set to 0. The ELSE '1' cluase is to update the 0 to a 1 in the member_id column so that I know the query ran and modified that row already.

Clarification: I wish to set TableA.member_id = TableB.member_id where TableA.name is similar to TableB.name. The 0 and 1 are for debugging, not the end goal.

Upvotes: 0

Views: 97

Answers (2)

wildplasser
wildplasser

Reputation: 44250

Alternative scenario without the need for like:

UPDATE TableA dst
SET member_id =  COALESCE ( src.member_id, -1 )
FROM (
        SELECT a.name , b.member_id
        FROM TableA a
        LEFT JOIN TableB b ON POSITION (b.name IN a.name) > 0
        ) src
WHERE  dst.name = src.name
AND dst.member_id < 10
        ;

NOTE: I changed member_id from character to numeric, which makes more sense to me.

Test data:

CREATE TABLE TableA ( member_id INTEGER , name varchar);
CREATE TABLE TableB ( member_id INTEGER , name varchar);

INSERT INTO TableA(member_id,name) VALUES
(0, 'Jack' ), ( 1, 'Jill'), (2 , 'Bob'), ( 3, 'Alice' );
INSERT INTO TableB(member_id,name) VALUES
(11, 'ack' ), ( 12, 'ill'), (13 , 'Do'), ( 14, 'lic' );

RESULT:

 member_id | name  
-----------+-------
        11 | Jack
        12 | Jill
        -1 | Bob
        14 | Alice

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270391

You have TABLEA.NAME in quotes. Remove them:

UPDATE TableA
    SET member_id = (CASE WHEN EXISTS (SELECT member_id
                                       FROM TableB
                                       WHERE TableB.name ILIKE '%' || TableA.name || '%'
                                      )

                          THEN member_id
                          ELSE '1'
                     END)
    WHERE TableA.member_id = '0'

Also, the LIMIT 1 is not necessary, because you are checking for existence.

And, you could move the condition to the where clause, clarifying the logic (in my opinion):

UPDATE TableA
    SET member_id = '1'
    WHERE TableA.member_id = '0' and
          exists (SELECT member_id
                  FROM TableB
                  WHERE TableB.name ILIKE '%' || TableA.name || '%'
                 )

EDIT (in response to comment)

Here is one way to get the member id. This approach is taking the maximum value. By using an aggregation function, the query can use coalesce() in the aggregation to supply an unknown value:

UPDATE TableA
    SET member_id = (SELECT coalesce(max(member_id), 'Unknown')
                     FROM TableB
                     WHERE TableB.name ILIKE '%' || TableA.name || '%'
                    )
    WHERE TableA.member_id = '0';

And, finally, you can write this using join syntax:

UPDATE TableA
    SET TableA.member_id = TableB.member_id
    FROM TableB
    WHERE TableB.name ILIKE '%' || TableA.name || '%' and
          TableA.member_id = '0'

If there are multiple matches, then the query will choose an arbitrary one.

Upvotes: 1

Related Questions