|
View previous topic :: View next topic
|
| Author |
Message |
prasun dhara
New User
Joined: 01 Mar 2008
Posts: 49
Location: kolkata
|
|
|
|
Hi,
I have follwoing requirement.There are two table( table1 and table2)
table1
PK1 emp_num field1 field2 ...
11 12 10 a
10 13 13 b
12 12 44 c
table2
PK1 emp_num field2 ...
11 10
12 11
13 33
Now I need to write a query to update emp_num of table1 with the value of emp_num from table2 for all matching PK1 (table1.PK1 =table2.PK1)
So after execution of the query table1 should be like below
table1
PK1 emp_num field1 field2 ...
11 10 10 a
10 13 13 b
12 11 44 c
Thanks |
|
| Back to top |
|
 |
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
this stil is basic SQL:
| Code: |
update tableX X
set (colX1,colX2) = (select y.ColX1,y.colX2 from tableY where x.PK = y.pk)
where exists (select y.ColX1,y.colX2 from tableY where x.PK = y.pk) |
Just make sure the subselect returns maximum 1 Row.
the "where exists" is to make sure that for rows which aren't in tableY the update does not set colX1,colx2 to NULL
ps.: plz don't sollicit for answer thru PM's |
|
| Back to top |
|
|
gylbharat
Active Member
.jpg)
Joined: 31 Jul 2009
Posts: 565
Location: Bangalore
|
|
|
|
Hi GuyC,
Why you have selected both the columns (primary key and Empno) from tabley?
cant we re write as
| Code: |
update tableX X
set colX2 = (select colX2 from tableY where x.PK = y.pk)
where exists (select y.colX2 from tableY where x.PK = y.pk)
|
|
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
ooh, I thought I answered this, before my absence.
I do not select PK and empno.
I just gave an example for updating two columns from a subselect.
It can be simplified to 1 column like you did, but then whenever it becomes two or more columns , people start to write the strangest things like :
Bad example below (good example two posts up)
| Code: |
update tableX X
set colX1 = (select colX1 from tableY where x.PK = y.pk)
, colX2 = (select colX2 from tableY where x.PK = y.pk)
, colX3 = (select colX3 from tableY where x.PK = y.pk)
where exists (select y.colX2 from tableY where x.PK = y.pk) |
|
|
| Back to top |
|
|
|
|
