SQL: How to update an empty column with pre-defined set of values

Question

I have a table with, let's say, 100 records. The table has two columns. The first column (A) has unique values. The second column (B) has NULL values

For 4 elements from column A I'd like to associate some earlier defined values, and they are unique as well.

Example

I don't care about which value from column B will be associated with the value from column A. I'd like to associate 4 unique values with another 4 unique values. Basically, like I'd cut and paste a block of values from one column to another in excel.

How can I do it without using cursors?

I'd like to use one Update statement for ALL rows instead one Update statement for EVERY row as I do now.

the number 100 and 4 were used to present the problem. In real world the table is a few million rows and the chunks to update are around 300. — user1146081, May 07 '15 at 14:34
Now you tell us?! Obviously this makes a difference. can you arrange the values you want to 'paste' into tables? — Zohar Peled, May 07 '15 at 14:38
@Zohar, I kept example simple, sorry for misleading. I'd like to use one Update statement for ALL rows instead one update statement for EVERY row as I do now — user1146081, May 07 '15 at 14:45
Are your predefined values already inside some other table? do you care if the predefined values will have consecutive values in ColumnA? What RDBMS Are you using? — Zohar Peled, May 07 '15 at 14:50
No, the predefined values comes from the text file. RDBMS is M$ SQL 2008. The values in Colum A are consecutive. The values in column B are random. — user1146081, May 08 '15 at 08:58

score 0 · Answer 1 · answered May 07 '15 at 14:34

Try this:

UPDATE t
SET ColumnB = BValue
FROM Table t 
INNER JOIN
(
SELECT 1 AValue, 'Mouse' BValue UNION
SELECT 2, 'Cat' UNION
SELECT 3, 'Dog' UNION
SELECT 4, 'Wolf' 
) PreDefined ON(t.ColumnA = PreDefined.AValue)

Use any number you want in the 'PreDefined' table, as long as they are unique and within the range of values in columnA of your original table.

score 0 · Answer 2 · answered May 07 '15 at 14:34

If you are only trying to fill a table for testing purposes, I guess you could:

A) Use the value from Column A itself (as it is already unique).

B) If they are to be different, use some function on the column A's value to obtain a column B value (something simple, like (ColumnA * 10), and this would give youA)

C) Create a temp table with a "dictionary" setting a B value for each possible A value, and then update the rows desired on your table looking up from values on this dictionary table.

Anyway, if you explain a little further your purpose it will be easier to try suggesting you a solution.

score -2 · Answer 3 · edited May 23 '17 at 12:14

if your animal data is already in a database table, then you can use a single update statement like this:

update target_table t4 
set columnb = (
    select animal_name 
    from (select columna, animal_name 
            from (select rownum rowNumber, animal_name from animal_table) t1
                join (select rownum rowNumber, columna from target_table t1 where columnb is null) t2
                on t1.rowNumber = t2.rowNumber
            ) t3 
    where t4.columna = t3.columna
    )
;

this works by selecting a sequence number and animal name from the source table, then selecting a sequence number and columna value from your target table. by joining those records on the sequence number you guarantee you get exactly 1 animal name for each columna value. you can then join those columna-to-animal records to your target table to do an update of columnb.

for more background on updating one table from values in another, you might consider the solutions presented here: Update rows in one table with data from another table based on one column in each being equal. the only difference is that in your example, you do not have any column that matches between your target table and your animal names table, so you need to use the rownum to create an arbitrary 1-to-1 matching of records.

if your unique options are in a text file or spreadsheet, then you can format them into a fixed-width space-padded string and pick the one you want using the rownum index like so:

update table_name 
set columnb = trim(substr('mouse cat   dog   wolf  ', rownum*6-6, 6)) 
where columnb is null;

now I use one update statement for every row I want to update (simirally as you presented above), I'd like to use ONE update statements for all rows — user1146081, May 07 '15 at 14:43
@user1146081 i have replaced my previous answers using excel or regex to generate individual update statements for each row with a single update statement as requested. if this suits your needs, please accept the answer. — james turner, May 07 '15 at 15:26
@user1146081 i have added a specific example of how to update your target table using values from some existing source table. — james turner, May 07 '15 at 16:22

SQL: How to update an empty column with pre-defined set of values

3 Answers3