CODEWITHSUNDEEP
sqlstringpostgresql-9.3
user3522963
user3522963

Reputation: 1

Updating table where LIKE has several criteria

I have two tables in PostgreSQL (version 9.3). The first holds id, title and the second holds schdname. I'm trying to create a select statement that will retrieve id and title where the title contains the schdname from the other table. The id, title table can hold several thousand rows. I can do this fine if I use WHERE LIKE for an individual schdname example but there are 40 plus names so this is not practical.

My original query ran like this which I know doesn't work but would show what I'm trying to achieve.

SELECT id, title, dname FROM mytable

WHERE title LIKE ( SELECT schdname FROM schedule )

This produces an error of more than one row returned by the subquery used as an expresssion. So my question is can this be achieved another way?

Upvotes: 0

Views: 32

Answers (2)

Max Al Farakh
Max Al Farakh

Reputation: 4486

Here is one way to do that:

SELECT id, title, dname FROM mutable
JOIN schedule ON mutable.title like '%' || schedule.schdname || '%'

Or a sligtly more readable way:

SELECT id, title, dname FROM mutable
JOIN schedule ON POSITION(schedule.schdname in mutable.title)<>0

Upvotes: 1

kayakpim
kayakpim

Reputation: 995

Are you actually using a wildcard with like? You don't say so above. If not you can replace like with IN. If you do want to do a wildcard join I'd recommend taking a substring of the columns and comparing that e.g.

names

james jack janice

select substr(names,1,2) as names_abbr from names_table where names_abbr = (select ...)

Upvotes: 0

Related Questions