1

I am working on sqlite 1st time
I have need help on following problem
I have 2 tables as follows

test1

key value
a    1
b    2
c    3
e    7
f    41
j    52

test2

key value
a    null
b    null
c    null
d    null
e    null
f    null
g    null
j    null

I am trying to updating values of test2 if key in table test2 = test1 then update value or else put null

expected output is like this

test2

key value
a    1
b    2
c    3
d    null
e    7
f    41
g    null
j    52

I try this query

insert into test2([value]) select test1.value
from test1, test2
where test2.value= test1.value;

but it not working
how to solve this?

HaveNoDisplayName
  • 8,291
  • 106
  • 37
  • 47
Grumpy Cat
  • 1,219
  • 2
  • 11
  • 17
  • You don't have to use HTML to format your question. Code snippets can be indented using four spaces. –  Jan 07 '15 at 14:29

3 Answers3

1

(Assuming you mean conditional UPDATE, not INSERT)

Fortuitously, it seems you need to reset test2 to null where the join fails, so you can do an update with the set specified as a subquery:

update test2
set value = 
(SELECT t1.value
 FROM test1 t1 where t1.key = test2.key
 LIMIT 1);

SqlFiddle:

The LIMIT will ensure just one row returned, but if the relationship between test1 and test2 isn't 1:1 you will need to apply logic to determine how to join the two tables.

StuartLC
  • 104,537
  • 17
  • 209
  • 285
  • hey thanks for answering, but there is problem it fills table test2 all rows with 1st match values not putting null. and you are right i am asking for conditional updating not insert :) – Grumpy Cat Jan 07 '15 at 14:32
  • ? I can't repeat as per the SqlFiddle. What you might do is explicitly alias the inner test table (t1) - this will make it easier to identity which column belongs to which table. I've also updated the fiddle with a variant which will retain the original value (i.e. it won't overwrite with null if there is no match). I wasn't quite sure what you wanted in this scenario – StuartLC Jan 07 '15 at 14:39
0

Try this, not know sqllite syntax but should be like this. Because your test2 table already have null value so only you have to update value column where key value matched with test1 table

MSSQL:-

UPDATE R 
SET R.[value] = p.[value]
FROM dbo.test2 AS R
INNER JOIN dbo.test1 AS P 
       ON R.[key] = P.[key]

I just follow this then I see that sql lite does not support join in update statement, but you can use in sub query

SQLLITE

UPDATE test2 SET [value] = ( 
             SELECT [value] FROM test1 WHERE test1.key = test2.key)
Community
  • 1
  • 1
HaveNoDisplayName
  • 8,291
  • 106
  • 37
  • 47
0

Try this

UPDATE test2 SET value = (SELECT test1.value FROM test1 WHERE test2.key =test1.key) ;

Somnath Sarode
  • 477
  • 3
  • 8