Update query

Database

Update query

I need to updata column in target table.

1. source table
2. Lkp table
3. target table

the query i wrote is not working can any one help me out.

update target.table
from( select lkptable.clm1,srctable.clm2 from lkptable,srctable
where
lkp.clm3=src.clm3
lkp.clm4=src.clm4
lkp.clm5=src.clm5
lkp.clm6=src.clm6
lkp.clm7=src.clm7
lkp.clm8=src.clm8) as c
set target.clm1 = c.clm1
where target.clm2 = c.clm2
4 REPLIES
Enthusiast

Re: Update query

From your question I didn't get what you are looking for..
You may try this logic.. that should work..

update target
SET clm1 = lkp.clm1
WHERE EXISTS ( Select 1
from lkp,src
where lkp.clm3=src.clm3
lkp.clm4=src.clm4
lkp.clm5=src.clm5
lkp.clm6=src.clm6
lkp.clm7=src.clm7
lkp.clm8=src.clm8)
Enthusiast

Re: Update query

The following sql will work

update target.table
from( select lkptable.clm1 x,srctable.clm2 y from lkptable,srctable
where
lkp.clm3=src.clm3
lkp.clm4=src.clm4
lkp.clm5=src.clm5
lkp.clm6=src.clm6
lkp.clm7=src.clm7
lkp.clm8=src.clm8)c
set clm1 = c.x
where clm2 = c.y

Re: Update query

Hi all, 

I am new to this forum. I am updating a table here, but I am getting error. Can some ne please help me with it. Thanks in advance.

UPDATE test1 A, test2 B

SET A.WK = B.PER_NO 

WHERE A.data BETWEEN 1 AND 4

AND A.T_Date = B.T_Date + 7

AND A.T_DT > '2010-01-01' 

Enthusiast

Re: Update query

Hi,

Try the following query.it shld work syntactically

UPDATE A FROM test1 A, test2 B

SET A.WK = B.PER_NO

WHERE A.data BETWEEN 1 AND 4

AND A.T_Date = B.T_Date + 7

AND A.T_DT > '2010-01-01'