SQL - Select tables where row number is XX

Question

Been searching on Google for a while now without finding the answer to my problem. I have like 10 tables where 5 of them contains 150 rows. I want to add 15 rows to these 5 tables, is there any simple solution for this? I know it's easy to add the rows manually but I want to know anyway. What I'm looking for is something like this:

INSERT INTO all_tables VALUES (col1, col2, col3) WHERE row_number() = '150'

Is it possible? Thanks in advance!

Could you explain your problem in another way. It's unclear... Do you want to replicate the 150th row or add values where rows equal a certain value? Also what flavor of SQL? — Jason McCreary, Jan 16 '12 at 18:25
You meean adding additional columns or inserting values into those columns? — Emmanuel N, Jan 16 '12 at 18:25
I want to add 15 extra rows to all tables containing 150 rows. I'm using phpmyadmin. — Cheezen, Jan 16 '12 at 18:28

score 4 · Answer 1 · edited Jan 17 '12 at 09:50

4

You can only target updates to one table at a time, which must always be specified by name. Also, you cannot specify a WHERE clause on an INSERT. Your best bet is probably to write one INSERT and copy and paste for the rest.

edited Jan 17 '12 at 09:50

onedaywhen

55,269
12
100
138

answered Jan 16 '12 at 18:28

ron tornambe

10,452
7
33
60

I was afraid of that. Thanks for your answer! – Cheezen Jan 16 '12 at 18:30

score 1 · Answer 2 · edited May 23 '17 at 12:21

You could:

Loop through a list of the relevant table names.
Run a dynamic query like select count(*) into @c1 from SpecifiedTable against the relevant table, returning the count into a declared variable.
If the returned value is 150, run another dynamic query to insert the relevant values into the specified table.

You can find out more about dynamic queries and returning values from them in MySQL here. If this is a once-off, you will probably find it easier to do it manually.

SQL - Select tables where row number is XX

2 Answers2