0

In SQL (whichever variant you like, say MySQL or MonetDB) it's very intuitive and straightforward to do:

CREATE TABLE t2 AS SELECT c1 FROM t1;

and get the selection result as a new column in a new table. But what if you want the result as a new column in the same table (table t1)? Let's assume c1 is of type INT and may be null. We would need to write:

ALTER TABLE t1 ADD c2 INT;
UPDATE TABLE t1 SET c2 = c1;

and that's the easy version, since if it's a non-null column we would need to initialize the new column with some value; and if the data comes from multiple tables I'd need some kind of inner query in the UPDATE (if it would be at all possible).

Can I select-into-a-column with a single command somehow?

einpoklum
  • 118,144
  • 57
  • 340
  • 684

1 Answers1

1

You use join:

UPDATE t1 JOIN
       t2
        ON t2.? = t1.?
   SET t1.c2 = t2.c1;

The ? is a placeholder for the column used to identity which row in t2 should update which row in t1.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786