SQL Update all entries from a table but insert different values on thecolumn

Question

I have a table and I want to insert in the same column for all entries different values.
The column has no value inserted yet and I like to insert a different string in it for each entry.
Is it possible? If it is, can you help me with some code?
I know how to update all entries with the same value on a column:

UPDATE table_name SET column_name = 'your_string';

Possible duplicate of [SQL Update with row\_number()](https://stackoverflow.com/questions/13648898/sql-update-with-row-number) — Raunak Thomas, Apr 26 '18 at 06:58
Double quotes are for delimited identifiers, e.g. columns with odd names like `"First Name"`. Use single quotes for string literals, like `'you_string'`. — jarlh, Apr 26 '18 at 06:58
Add some sample table data and the expected result - as formatted text, not images. And tag the dbms you're using. — jarlh, Apr 26 '18 at 06:59
@RaunakThomas it is not a duplicate because there the solution is to iterate and just put the integer in the column. I want to set the value of the column with a string — developer, Apr 26 '18 at 07:17
How is the value in each row to be determined? Is it based on some of the existing data in other columns? If so, some examples would be extremely helpful. If not, how else are these values being defined? — Damien_The_Unbeliever, Apr 26 '18 at 07:19
The value that will be inserted in the column is a string that will be created randomly with a function in Asp.Net MVC — developer, Apr 26 '18 at 07:22

score 0 · Answer 1 · answered Apr 26 '18 at 07:13

0

You can populate a column with the different values, and use it to populate the target column.

Consider the following example using MySQL database.

Table creation:

CREATE TABLE a (
column1 varchar(20),
column2 varchar(20),
column3 varchar(50)
);

Table population:

insert into a(column1, column2) values('a','100');
insert into a(column1, column2) values('b','200');
insert into a(column1, column2) values('c','300');

Check table:

select * from a;
+---------+---------+---------+
| column1 | column2 | column3 |
+---------+---------+---------+
| a       | 100     | NULL    |
| b       | 200     | NULL    |
| c       | 300     | NULL    |
+---------+---------+---------+

Populate column3 using column2:

update a set column3=concat('value-',column2);

Check table again:

select * from a;
+---------+---------+-----------+
| column1 | column2 | column3   |
+---------+---------+-----------+
| a       | 100     | value-100 |
| b       | 200     | value-200 |
| c       | 300     | value-300 |
+---------+---------+-----------+

(Optional) Drop column2 if not needed:

alter table a drop column column2;

select * from a;
+---------+-----------+
| column1 | column3   |
+---------+-----------+
| a       | value-100 |
| b       | value-200 |
| c       | value-300 |
+---------+-----------+

answered Apr 26 '18 at 07:13

Jagrut Sharma

4,574
3
14
19

Thanks for your answer but is not helpful for my problem. – developer Apr 26 '18 at 07:23
I want to insert the value from an external source. I will create there the string and I need just the query to populate the column – developer Apr 26 '18 at 07:30
How are you planning to determine which row in target table gets matched to the external source? – Jagrut Sharma Apr 26 '18 at 07:33
I know the exact number of rows in the table. I will create that number strings and I hope that when I will apply a query those values to go in that column – developer Apr 26 '18 at 07:37
Can you share the structure of your target table, and the table that is containing the generated strings you want to insert into target table? – Jagrut Sharma Apr 26 '18 at 07:42
Col1 Col2 Col3 1 100 RandomString1 2 200 RandomString2 3 300 RandomString3 And RandomStringX(where X is a number) are stored in a vector – developer Apr 26 '18 at 07:49
Sorry, I don't know how to create a table like your in comment – developer Apr 26 '18 at 07:54
It appears you are trying to update the target table via an application. If so, you can use a loop in your application code similar to this: for (int i=0; i < vector.size(); i++) { update targettable set Col3=vector.get(i) where Col1=i; } – Jagrut Sharma Apr 26 '18 at 08:01
This is helpful.Thanks! I thought it is a way to update all rows by once with those values. Thank you again! – developer Apr 26 '18 at 08:04

score 0 · Answer 2 · answered Apr 26 '18 at 11:42

SQL Server supports updatable CTEs and subqueries. This turns out to be pretty easy:

with toupdate as (
      select t.*, row_number() over (order by (select null)) as seqnum
      from t
     )
update toupdate
    set col = cast(seqnum as varchar(255));

The inserted value is a number converted to a string.

A simpler alternative is to use newid():

update t
    set col = newid();

You can assign an id to a string column.

SQL Update all entries from a table but insert different values on thecolumn

2 Answers2