SQL uploading replacing an entire column with (id, value) pairs

Question

I have a SQL table and want to update one of the columns with an entirely new column of data. I have a list of (ID, value) pairs that looks as follows:

(2336, 21),
(2337, 13),
(2338, 17),
(2339, 20),
(2340, 21),
(2341, 4),
.
.
.

How can I write an SQL statement that will use these to look for each id in the table and replace it with the new value?

For example, it would find the row with id 2336 and replace its value with 21. (value is the name of another column).

I have looked into replace but it doesn't look like that will do the trick.

1) Edit the question add some sample data & desired result would helpful. 2) Tag the DBMS (i.e. `MySQL`, `SQL Server`, etc..) that you are using. — Yogesh Sharma, Aug 27 '18 at 14:46
Your example is confusing. Do you have a table with two separate columns for `id` and `value`? Or one column with comma separated key value pairs (which is what your example data looks like to me)? — Andrew, Aug 27 '18 at 14:55
@Iza . . . Please tag the question with the database you are using. — Gordon Linoff, Aug 27 '18 at 15:12
it is a table with 3 columns. ID, name, and value. I have a list of (id, value) pairs. i want to go through each row, and replace the value with the new value in the (id, value) pair. So if (10, 50) is in the list, on the row with id 10 t will change the value column to 50 — , Aug 27 '18 at 18:34

score 0 · Answer 1 · answered Aug 27 '18 at 14:55

0

Your can create temp table for your new values and use UPDATE with JOIN to this temp table. Please try this:

create table #newValues (Id INT, Value INT)
insert into #newValues (Id, Value) VALUES (1, 21)
insert into #newValues (Id, Value) VALUES (2, 22)

create table #originalTable (Id INT, Value INT)
insert into #originalTable (Id, Value) VALUES (1, 31)
insert into #originalTable (Id, Value) VALUES (2, 32)
insert into #originalTable (Id, Value) VALUES (3, 33)


UPDATE #originalTable 
    SET 
        #originalTable.Value = #newValues.Value
    FROM #originalTable
    JOIN #newValues ON #newValues.Id = #originalTable.Id

answered Aug 27 '18 at 14:55

Kiryl Khadanovich

420
7
10

the problem is that the list is every long. The table has 3 columns: an id, a name, and a value column. I have a list of (id, value) pairs and I want to go through every row and change the matching id with the new value – Aug 27 '18 at 18:28
1

Where do you store you list of new Id-Value pair? As variant you can create Store procedure, Table Variable and send your data via table variable to SP. This if you are using programming language as C#. In case if your data is csv file, there are functions in the sql. For example, load csv - https://stackoverflow.com/questions/15242757/import-csv-file-into-sql-server But this is for sql-server. – Kiryl Khadanovich Aug 27 '18 at 20:17

score 0 · Answer 2 · answered Aug 27 '18 at 15:11

Each database seems to have its own format for updating values from another table. If we assuming that the new data is already in a table, then this would work in most (if not all) databases:

update original
    set value = (select n.value from newdata n where n.id = original.id)
    where exists (select 1 from newdata n where n.id = original.id);

score 0 · Answer 3 · answered Aug 27 '18 at 15:11

I'm not sure I completely understand but....

If you have two fields within the same table, I'll define them as old pair and new pair. You want the "old" ID to match the "new" value?

What I would probably do is separate the fields into IDs and Values. And then create a new table that will only be used to bring in the NEW values.

Something like this:

SELECT 
 LEFT(OriginalTable.OldPair,7) AS OriginalID
,RIGHT(OriginalTable.OldPair,3) AS OriginalValue

,LEFT(OriginalTable.OldPair,7) + RIGHT(NewTable.NewPair,3)

FROM OriginalTable
LEFT OUTER JOIN NewTable
ON LEFT(OriginalTable.OldPair,7) = LEFT(NewTable.NewPair,7)

SQL uploading replacing an entire column with (id, value) pairs

3 Answers3