CODEWITHSUNDEEP
sqlpostgresqldatabase-design
ekclone
ekclone

Reputation: 1092

Use LIKE with multiple names from a different table in UPDATE

I'm looking to update a field to a different value if the string in name contains a value from a different table in PostgreSQL:

Table Types that needs to be updated

id  |  name          |  type
1   |  Google Page   |  WEBSITE
2   |  Yahoo Page    |  WEBSITE
3   |  Facebook Page |  WEBSITE
...

Table Companies that has the names

id  |  name    
1   |  Google
2   |  Yahoo
3   |  Facebook
4   |  Twitter
5   |  Stackoverflow
...

What I tried

UPDATE types
SET type = 'BUSINESS'
WHERE name LIKE CONCAT((SELECT companies.name from companies), '% Page')

But I'm getting this issue: [21000] ERROR: more than one row returned by a subquery used as an expression

Upvotes: 0

Views: 41

Answers (3)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657847

Much cheaper than comparing concatenated strings:

UPDATE types t
SET    type = 'BUSINESS'
FROM   companies c
WHERE  right(t.name, 5) = ' Page'
AND    left(t.name, -5) = c.name;

db<>fiddle here

Upvotes: 1

StaticVoid
StaticVoid

Reputation: 1537

You should use the IN directive:

WHERE name IN (SELECT CONCAT(companies.name, ' Page') name from companies)

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521979

You could use a subquery with exists logic to retain your current logic:

UPDATE types t
SET type = 'BUSINESS'
WHERE EXISTS (SELECT 1 FROM companies c
              WHERE CONCAT(c.name, ' Page') = t.name);

You could also use an update join:

UPDATE types t
SET type = 'BUSINESS'
FROM companies c
WHERE t.name = CONCAT(c.name, ' Page');

Upvotes: 3

Related Questions