CODEWITHSUNDEEP
phpmysql
Andrew Scrivener
Andrew Scrivener

Reputation: 53

Add column of data to an existing mySQL table

I have a basic SQL problem that's been driving me mad. If I have a mySQL table e.g below. How would I add another 80+ values to Column 2 starting from the first empty row (in this example row 3).

I've been trying a number of queries using INSERT or UPDATE but the closest I've got is to add the values to column 2 starting from the last defined ID value (e.g. row 80ish).

   ID  |    Column 2    |
--------------------------------
  1    |    value       |
  2    |    value       |
  3    |                |
  4    |                |
  5    |                |

etc

The real table has around 10 columns, all with data in but I just need to add content (a list of around 80 different strings in CSV format to one of the columns)

I'd appreciate it if anyone could point me in the right direction.

Upvotes: 5

Views: 2197

Answers (2)

davek
davek

Reputation: 22895

I'd load the data into a separate table with the same structure and then update the target table using join or subquery to determine which columns are currently empty.

i.e. load interim table and then:

update target_table set column2 = (select column2 from interim_table where ... 
where column2 is null

(slow but intuitive)

update target table, interim_table 
set target table.column2 = interim_table.column2 
where target table... = interim_table...
and target_table.column2 is null

(better performance)

Upvotes: 3

Leandro Battochio
Leandro Battochio

Reputation: 364

Why don't you first run a query to find out the first empty row ID number? you can use SELECT COUNT(*) FROM TABLE_NAME for that.

then you create a for loop and inside run a INSERT query, starting with the value returned by the previous query. just a scratch:

for(var id = last; id < totalOfQueries; id++)
{
  var query = new MysqlCommand("INSERT INTO table VALUES ('" + id + "',....);
}

Upvotes: 0

Related Questions