0

I am writing an SQL query on two tables products01 and products02 in a PostgreSQL database.

For the entire set of records in products02 take the substring (first 10 characters of volumeId and use those substrings to find all the corresponding records in products01. There can be more than one hit in products01, for instance finding older legacy record of the same item, just a different edition. The eleventh character of volumeId makes it unique.

For instance, in products01 I can have volumeId's like:

record45671
record45672
record45673

And in products02 I would be using substring(volumeId for 10) or 'record456' to find those similar records in products01.

Also I'd like to display the results so I can verify my query is accurate.

Here is my initial attempt:

    SELECT substring(volumeId, for 10) FROM temporary.products01 t01
    WHERE EXISTS (
        SELECT volumeId FROM temporary.products02 t02
        WHERE t01.volumeId LIKE t02.volumeId);
  • 1
    Hi, Welcome to the site. Could you add your table definitions, sample data and expected output to the question. Please edit your question and don't add additional information in comments. – Tom V - try topanswers.xyz Nov 30 '18 at 11:58
1

To find all rows from table products01 where the 10 leading characters of volumeId match any products02.volumeId:

SELECT t1.*
FROM   products01 t1
JOIN   products02 t2 ON left(t1.volumeId, 10) = t2.volumeId;

To delete:

DELETE FROM products01 t1
USING  products02 t2
WHERE  left(t1.volumeId, 10) = t2.volumeId;

You can use this equivalent syntax to get both commands more in line:

SELECT DISTINCT t1.* FROM products01 t1, -- see below
-- DELETE FROM products01 t1 USING  
products02 t2
WHERE  left(t1.volumeId, 10) = t2.volumeId;

Only add DISTINCT if t2.volumeId is not fully deterministic (t1.volumeId can match multiple t2.volumeId).

Now you can either select the whole statement to select or starting with DELETE to delete. See:

  • Question on this syntax in the JOIN example and in the SELECT DISTINCT example: products02 t2 ON left(t1.volumeId, 10) = t2.volumeId; -- should the "=" be a like since we are using the substring (left func) of t1. – user9491577 Nov 30 '18 at 16:21
  • @user9491577: If t2.volumeId consists of 10 characters - the leading 10 characters in t1.volumeId - then it's =. If not, you need to clarify your question, like has been suggested repeatedly. – Erwin Brandstetter Nov 30 '18 at 16:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.