0

Here's what I need to do:

For each row in Table1 where Name is not null or blank, and Table2 has a row with matching Name, replace another column in Table1 with the contents of a column from Table2, and set the name in Table 1 to null.

I can't seem to wrap my head around getting that logic into SQL.

I don't really care if Table2 has multiple rows with matching Names, just grabbing the first one is good enough.

Zook
  • 499
  • 9
  • 20
  • 1
    Consider [this](http://stackoverflow.com/questions/4708082/update-table-from-another-table-in-db2-9) and [this](http://stackoverflow.com/questions/14312885/update-table-with-value-from-another-table) – mustaccio Apr 01 '14 at 14:47
  • I mustaccio a question... I was able to use the syntax in that first link to successfully update the column, but now when I try to set the Name to null whenever the other column is not null, I get "APPLICATION RAISED ERROR WITH DIAGNOSTIC TEXT: INVALID BRANCH ID". Google doesn't know what it means. – Zook Apr 01 '14 at 18:29
  • It's your application custom exception. Apparently your _other column_, which is probably called `BRANCH_ID` or something like that, cannot be null. It's often useful to actually read error messages before pasting them into Google. – mustaccio Apr 01 '14 at 18:38
  • It's not called branch id, the word branch doesn't appear anywhere in the database, and that column can be null. – Zook Apr 01 '14 at 19:03
  • Then check your application code (might be a trigger, for example) to see what it thinks is a valid branch ID. – mustaccio Apr 01 '14 at 19:14
  • Yeah it's an update trigger, I'm checking with a DBA. – Zook Apr 01 '14 at 19:36

1 Answers1

0

In DB2 you could use the MERGE statement to do the job

MERGE INTO Table1 A
USING Table2 B
     on A.name=B.name  
WHEN MATCHED
   THEN UPDATE SET A.another_column=B.Table2_Column
    , A.name=NULL;

In T-Sql You would use an UPDATE statement with a join criteria that would only impact the matching rows, something like the below:

Update A
set A.another_column=B.Table2_Column
, A.name=NULL
From Table1 A
inner join Table2 B
on A.name=B.name
Where isnull(A.name,'')<>''
SoulTrain
  • 1,904
  • 1
  • 12
  • 11
  • This SQL statement is not valid in DB2. – mustaccio Apr 01 '14 at 14:44
  • What's wrong with it? Looks like `ifnull` replaces `isnull` -- http://stackoverflow.com/questions/65071/isnull-function-in-db2-sql – Zook Apr 01 '14 at 14:45
  • I get an error at "Table2" -- ILLEGAL SYMBOL "Table2". SOME SYMBOLS THAT MIGHT BE LEGAL ARE: (. SQLCODE=-104, SQLSTATE=42601, DRIVER=3.63.75 SQL Code: -104, SQL State: 42601 – Zook Apr 01 '14 at 18:03
  • The question is tagged DB2. Why did you include a T-SQL answer? – WarrenT Apr 03 '14 at 04:14