0

I have two tables as below:

tablea

 k |  1    | 2 
--------------------
 a | mango | xx
 b | orange| xx
 c | xx    | apple
 d | xx    | banana
 a | xx    | mango

tableb

 k |  1    | 2 
--------------------
 a |       | 
 b |       |  
 c |       |  
 d |       |

How can I update tableb from tablea so I get the results below?

tableb

 k |  1    | 2 
--------------------
 a | mango | mango
 b | orange| xx
 c | xx    | apple
 d | xx    | banana

if in case I try to use a update statement like below

update tableb 
set 1 = x.1,
    2 = x.2
from 
 (
    select * from tablea
) x 
where tablea.k = x.k

Can I make the update statement to ignore xx if k is duplicate?

Thanks.

Steven
  • 896
  • 2
  • 16
  • 29
Taz
  • 113
  • 12
  • how to determine that row1 (`k='a'`) will be `mango, mango` not `xx,xx` or `mango,xx`? – Pham X. Bach May 18 '16 at 15:32
  • @PhamX.Bach I was hopping `xx` mean `null`? But yes, we need more information. – Juan Carlos Oropeza May 18 '16 at 15:35
  • @JuanCarlosOropeza if `xx` means `null` then I will ask if `tablea` have another row `a | orange | banana` then what should `tableb` be. `Taz` your question need more logic about data in `tablea` and how to update `tableb` – Pham X. Bach May 18 '16 at 15:48
  • hi 'xx' are just two characters(some thing like 'N/A'). in tablea, there is a big backend calculation behind that finally brings me only these 5 rows. I won't be getting an answer like 'a | orange | banana' – Taz May 18 '16 at 16:10

1 Answers1

1

Here is the SELECT, hope you can make the update.

  1. Try to search a match for every one on the left side with name <> 'xx'
  2. Then union with the rest of rows I havent use it yet.

SQL Fiddle Demo

SELECT t1."k", t1."1", COALESCE(t2."2", 'xx') "2"
FROM tablea t1
LEFT JOIN tablea t2
       ON  t1."1" = t2."2"
WHERE t1."1" <> 'xx'
UNION ALL
SELECT t1."k", t1."1", t1."2"
FROM tablea t1
WHERE t1."1" = 'xx'
  AND t1."2" NOT IN (SELECT t2."1" FROM tablea t2 WHERE t2."1" <> 'xx')
Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118