0

Thanks for looking, this is probably a simple question.

I have a table, tableA with three columns: tableID, id_1, and id_2

In each row, either id_1 or id_2 is populated with an integer. I want to go through each row and set the tableID to either id_1 or id_2, whichever is not null.

I'm doing this in SSIS with a small dataset, probably less than 20 records. Any help is appreciated.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Aaron Johnsen
  • 93
  • 1
  • 2
  • 10

1 Answers1

2

in its simplest form:

update tableA set tableID = isnull(id_1,id_2)

Thats assuming that id_1 will always be null if id_2 has a value (which is what you say)

EDIT: a bit more sensible statement:

update tableA set tableID = isnull(id_1,id_2) where tableID is null
Rich
  • 867
  • 7
  • 12
  • Thanks! I need to do this on a row by row basis, so could I run this in SSIS as an OLE DB command? And yes, one of the ids will(or should!) be NULL. – Aaron Johnsen Oct 31 '13 at 17:32
  • just executing this command once on the table will update all rows in the table, so you could just stick that inside the "Execute SQL" task in the design planner ;) – Rich Oct 31 '13 at 17:47
  • Even better, I greatly appreciate the wisdom! – Aaron Johnsen Oct 31 '13 at 17:54
  • 2
    `COALESCE` is standard SQL and handles two or more parameters. `ISNULL` is non-standard and is limited to two parameters. Some additional information on `ISNULL` vs. `COALESCE` is [here](http://blogs.msdn.com/b/sqltips/archive/2008/06/26/differences-between-isnull-and-coalesce.aspx) and [here](http://stackoverflow.com/questions/7408893/using-isnull-vs-using-coalesce-for-checking-a-specific-condtion). – HABO Oct 31 '13 at 18:18