2

I can copy the contents of one column to another using the sql UPDATE easily. But I need to do it without deleting the content already there, so in essence I want to append a column to another without overwriting the other's original content.

I have a column called notes then for some unknown reason after several months I added another column called product_notes and after 2 days realised that I have two sets of notes I urgently need to merge.

Usually when making a note we just add to any note already there with a form. I need to put these two columns like that, keeping any note in the first column eg

Column notes = Out of stock Pete 040618--- ordered 200 units Jade 050618 --- 200 units received Lila 080618

and

Column product_notes = 5 units left Dave 120618 --- unit 10724 unacceptable quality noted in list Dave 130618

I need to put them together with our spacer of --- without losing the first column's content so the result needs to be like this for my test case:

Column notes = Out of stock Pete 040618--- ordered 200 units Jade 050618 --- 200 units received Lila 080618 --- 5 units left Dave 120618 --- unit 10724 unacceptable quality noted in list Dave 130618

Magisch
  • 7,312
  • 9
  • 36
  • 52
Kilisi
  • 402
  • 11
  • 33

2 Answers2

3

It's simple - 

update table1 set notes = notes || '---' || product_notes;
Mahesh H Viraktamath
  • 818
  • 3
  • 14
  • 34
  • pre edit was better, because even if it was null I still need to copy the data from second column – Kilisi Oct 25 '18 at 12:20
  • 2
    @Kilisi Worth pointing out that if either of the columns are NULL then `notes` will become NULL. Probably better to do something like this: `SET notes = NULLIF(CONCAT_WS('---', notes, product_notes), '')` - so if either are NULL, it won't put the separator on either side. And if both are NULL, the NULLIF will ensure the returned value (empty) is set back to NULL. – 404 Oct 25 '18 at 12:31
  • @eurotrash yes I got that problem straight away but was easy to do an UPDATE table1 SET notes=product_notes WHERE notes IS NULL but I like your solution, should put it as an answer so I can give it a vote – Kilisi Oct 25 '18 at 12:34
3

The solution provided by @MaheshHViraktamath is fine, but the problem with simple string concatenation is that if any of the items being concatenated are NULL, the whole result becomes NULL.

Another potential issue is if either field is empty. In that case you might get a result of field a--- or ---field b.

To guard against the first scenario (without putting checks in the WHERE clause) you can use CONCAT_WS like so: CONCAT_WS('---', notes, product_notes). This will combine the two (or however many you put in there) fields with the first parameter, i.e. '---'. If either of those two fields are NULL, the separator won't be used, so you won't get a result with the separator prepended or appended.

There are two issues with the above: if both fields are NULL, the result isn't NULL but an empty string. To handle this case just put it in a NULLIF: NULLIF(CONCAT_WS('---', notes, product_notes), '') so that NULL is returned if both fields are NULL.

The other issue is if either field is empty, the separator will still be used. To guard against this scenario (and only you will know whether it's a scenario worth guarding against, or if this is even desired, based on your data), put each field in a NULLIF as well: NULLIF(CONCAT_WS('---', NULLIF(notes, ''), NULLIF(product_notes, '')), '')

As a result you get: UPDATE your_table SET notes = NULLIF(CONCAT_WS('---', NULLIF(notes, ''), NULLIF(product_notes, '')), '');

404
  • 8,022
  • 2
  • 27
  • 47