CODEWITHSUNDEEP
pythonsql
Aaron
Aaron

Reputation: 202

Iterating in SQL

Given the following Python code:

keys1 = [1, 2, 3, 4, 5]
keys2 = [6, 7, 8, 9, 10]

for k1, k2 in zip(keys1, keys2):
    # do something with the pair of keys, k1 and k2

How would I implement something like this in SQL?

Basically, I have about 15 lines of code in SQL. This SQL performs some actions based on two keys it currently has hard coded into it. Well now I need to execute the same SQL with about 10 other pairs of keys.

The simple, albeit inelegant solution would just be to copy/paste the code 10 times and hard code the keys but I was curious if there was some iterative way of doing this, like demonstrated above in Python.

Important to note is that I cannot implement the logic in Python while having it connected to a database. It has to be purely SQL.

Thanks in advance.

EDIT: For clarification, however this may be achieved in SQL doesn't have to be exactly like my python demonstration. I only used lists and zip bc that's how I know to do it in Python. I could have just as easily defined a function and called it ten times, passing a pair of keys each time, avoiding zip and lists altogether.

My main goal is reducing the number of times I have to write the same sql over and over again.

In Python, I'd use iteration with data structures, or functions. In SQL? I don't know.

And I'm sorry, it's for work. I cannot post the sql. But conceptually I wouldn't think it'd matter. It might as well just be: from someTable select col1 where attr1 = key1 and attr2 = key2

Upvotes: 1

Views: 66

Answers (2)

daxim
daxim

Reputation: 39158

with
    foo(keys1) as (
        values (1),(2),(3),(4),(5)
    ),
    bar(keys2) as (
        values (6),(7),(8),(9),(10)
    )
select keys1, keys2 from
    (select
        row_number() over () as foo_id, keys1
        from foo)
    join
    (select
        row_number() over () as bar_id, keys2
        from bar)
    on foo_id = bar_id
    order by keys1

-- (1, 6),
-- (2, 7),
-- (3, 8),
-- (4, 9),
-- (5, 10)

Upvotes: 0

Schwern
Schwern

Reputation: 164799

The concept of zip doesn't map well to SQL. While a few databases have arrays most do not. SQL rows have no inherent order. You wouldn't say "match the first row of table A with the first row of table B" because there is no first row.

One way you could interpret your question is using in to match multiple values. For example, if you wanted to select all the rows whose ID matches a list of IDs...

create table1 (
  id integer primary key,
  some_data integer
);

select some_data
from table1
where id in (1, 2, 3);

And that would return the value of some_data in all rows with an id of 1 or 2 or 3.

Another interpretation is to join two tables together based on some criteria. For example...

create table1 (
  id integer primary key
);

create table2 (
  id integer primary key,
  table1_id integer references table2(id)
);

select *
from table1
join table2 on table1.id = table2.table1_id
where table2.id = (6, 7, 8);

That matches rows in table1 which have a matching entry in table2 whose id in table2 is 6, 7, or 8.

Which is to say, put those hard coded lists into tables, temporary tables will do, and join them with other tables.

Upvotes: 1

Related Questions