SQL simple update query

I am trying update the following table:

id      SUB           pup          Atxt     btxt
----------------------------------------------
24    ENG           101          A          B
23     ENG          100          A         B
24    ENG           100          null       null
24    ENGLA       100          C          C


I want column Atxt and Btxt to be copied from records that have 'ENGLA' in column sub, TO records where column 'sub' has a value of ENG and where column 'pup' values match and only for records with 'id' value of 24

to end up with a table that looks like this:

id      SUB           pup          Atxt     btxt
----------------------------------------------
24    ENG           101          A          B
23     ENG          100          A         B
24    ENG           100          C         C
24    ENGLA       100          C          C

I was thinking of something like:

update table1

set atxt=btxt
where id = 24 and pup=pup  and sub =eng

but im lost with having 2 separate  where's in the query


Any suggestions?

nazateamAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
yes, that's possible:
update t1
  set atxt= t2.atxt 
    , btxt = t2.btxt
from table1 t1
join table1 t2 
  on t2.id = t1.id
 and t2.pup = t1.pup
 and t2.atxt is not null
 and t2.sub = 'ENGA'
where t1.id = 24
  and t1.atxt is null
  and t1.sub = 'ENG'

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
What about this:
update t1
  set atxt= t2.atxt 
    , btxt = t2.btxt
from table1 t1
join table1 t2 
  on t2.id = t1.id
 and t2.pup = t1.pup
 and t2.atxt is null
where t1.id = 24
  and t1.atxt is not null

Open in new window

0
 
nazateamAuthor Commented:
Hmmm Perhaps, But where in there am i specifying the source of the update (records with 'ENGLA' under SUB and the destination (records with 'ENG' under SUB?


0
 
nazateamAuthor Commented:
Thanks for this, worked a treat!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.