SQL Stuffs: Insert using Select cross Constants help

A place to discuss the implementation and style of computer programs.

Moderators: phlip, Moderators General, Prelates

GTM
Posts: 90
Joined: Tue Nov 10, 2009 4:53 am UTC

SQL Stuffs: Insert using Select cross Constants help

Postby GTM » Sat Dec 19, 2015 7:17 am UTC

I want to do something like this:

INSERT INTO table2 (ID, value)
select distinct table1.ID, 1 from table1 where something = somethingelse;
INSERT INTO table2 (ID, value)
select distinct table1.ID, 2 from table1 where something = somethingelse;
INSERT INTO table2 (ID, value)
select distinct table1.ID, 3 from table1 where something = somethingelse;
INSERT INTO table2 (ID, value)
select distinct table1.ID, 4 from table1 where something = somethingelse;
INSERT INTO table2 (ID, value)
select distinct table1.ID, 5 from table1 where something = somethingelse;
...
INSERT INTO table2 (ID, value)
select distinct table1.ID, 100 from table1 where something = somethingelse;

Is there someway to do this in 1 query so I don't have to write it out 100 times, while at the same time I don't have to compute that select statement 100 times?

User avatar
PeteP
What the peck?
Posts: 1451
Joined: Tue Aug 23, 2011 4:51 pm UTC

Re: SQL Stuffs: Insert using Select cross Constants help

Postby PeteP » Sat Dec 19, 2015 9:05 am UTC

for clarification what is different each time. I assume table1 and 2 remain the same. I assume somethingelse changes, does omething stay the same, does value change?

GTM
Posts: 90
Joined: Tue Nov 10, 2009 4:53 am UTC

Re: SQL Stuffs: Insert using Select cross Constants help

Postby GTM » Sat Dec 19, 2015 7:49 pm UTC

so table1 has a bunch of IDs, say 1-10, and I want all the even numbers, for example.

I want to put them in table2 multiple times like this:

so I want the f

ID value
2 1
2 2
2 3
2 4
2 5
...
2 99
2100
4 1
4 2
4 3
...
10 99
10 100

so I end up with results of subquery x 100 lines in the table

User avatar
Thesh
Made to Fuck Dinosaurs
Posts: 6598
Joined: Tue Jan 12, 2010 1:55 am UTC
Location: Colorado

Re: SQL Stuffs: Insert using Select cross Constants help

Postby Thesh » Sat Dec 19, 2015 8:23 pm UTC

What database server are we talking about. If it was SQL Server, I would do something like this:

Code: Select all

declare @i int = 0
while @i < 100
begin
    set @i = @i + 1
    insert into table2 (ID, value)
    select distinct table1.ID, @i from table1 where something = somethingelse;
end


But different flavors of SQL will have different syntax.

Alternatively, if you have a table with the values 1-100, you can do a cross join using old-school join syntax:

Code: Select all

insert into table2 (ID, value)
select distinct, table1.ID, nums.val from table1, nums where something = somethingelse;
Summum ius, summa iniuria.

BedderDanu
Posts: 39
Joined: Tue Jan 14, 2014 6:18 am UTC

Re: SQL Stuffs: Insert using Select cross Constants help

Postby BedderDanu » Sat Dec 19, 2015 10:23 pm UTC

Can you build a select statement that will put the ids from table 1 in the order you need?

If so, then the following should work:

Code: Select all

--PostgreSQL
INSERT INTO
  table2 (id, value)
SELECT
  t1.id
 ,row_number() OVER (ORDER BY t1.id) --This is the row number, ordered by id. It starts at 1 and counts up.
FROM
  table1 AS t1
WHERE
  t1.id % 2 = 0 --only select even rows
ORDER BY
  t1.id; -- order the rows in the desired order. This should match the "Order By" above in the row_number line.
 


The point is, once I have the ids in the order I want them, I can import the ID and the row number of the select statement.

EDIT:

Rereading your more specific description, That answer really depends on your SQL version. Here's how I would do that query in PostgreSQL:

Code: Select all

--tested against PostgreSQL 9.4
INSERT INTO
  table2 (id, value)
SELECT
  t1.id,
  a
FROM
  table1 AS t1,
  generate_series(1, 100) AS a --creates a reference to the values I need, in this case the numbers from 1 to 100.
WHERE
  t1.id % 2 = 0 --Selects only the ids I need from table1, in this case the even ones.


Although, that above is just thresh's solution, I'm just building the table of numbers dynamically with the "generate series" function.

GTM
Posts: 90
Joined: Tue Nov 10, 2009 4:53 am UTC

Re: SQL Stuffs: Insert using Select cross Constants help

Postby GTM » Mon Dec 21, 2015 7:02 am UTC

I'm learning so much about what I need to work on with my skills, in this case, specifying exactly what the problem is (and being aware when I'm describing something different than I want!)

So actually, the 2nd columns numbers aren't ordered 1 to 100. They are some set of numbers, so I would end up with:
2, 5
2, 15
2, 50
2, 4
2, 27
4, 5
4, 15
4, 50
4, 4
4, 27
6, 5

and so on.


I'm using MySQL.

Knowing this, the 2nd solution by thesh should work well. Create a single column table, insert 5,15, 50, 4 and 27, then do the cross, but another constraint I want to have is that I cannot create new tables.

Thanks for your help so far!

mousewiz
Posts: 107
Joined: Wed Oct 26, 2011 6:50 pm UTC

Re: SQL Stuffs: Insert using Select cross Constants help

Postby mousewiz » Tue Dec 22, 2015 9:33 pm UTC

Avoiding the use of a temp table can typically be accomplished by creating a subquery.

I believe MySQL syntax allows for this, but somebody else might correct me:

Code: Select all

SELECT SQ.n
FROM (SELECT T.n FROM Values T) SQ


From there, it sounds like your list of values needs to be hardcoded somehow. Stackoverflow leads me to believe that you'd need to make your subquery look like this to make it work:

Code: Select all

SELECT 5 AS n
UNION ALL SELECT 15 AS n
...
UNION ALL SELECT 4397 AS n


I'd use search & replace in notepad++ or sed to generate that if you have a comma separated list already.


Return to “Coding”

Who is online

Users browsing this forum: No registered users and 3 guests