|
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| ||||||||||||||||||||||||||
![]() |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
|
#1
|
|||
|
|||
|
How to Update Column from column of another table
I have table A and B, A is subset of B. all of A's Primary Key field are in Table B. I need to update some fields in Table A from those fields in Table B.
Structure of Table A ID FIELD1 FIELD2 Structure of Table B ID FIELD1 FIELD2 Sample Data of Table A ID FIELD1 FIELD2 1 11 12 2 21 22 3 31 32 Sample Data of Table B ID FIELD1 FIELD2 1 111 122 2 211 222 3 311 322 4 411 422 5 511 522 How to write the Update command to have the Table A updated to result below? Table A after Updated ID FIELD1 FIELD2 1 111 122 2 211 222 3 311 322 |
|
#2
|
|||
|
|||
|
There is a way to write SQL update in MSSQL like
UPDATE dbo.Table2 SET dbo.Table2.ColB = dbo.Table2.ColB + dbo.Table1.ColB FROM dbo.Table2 INNER JOIN dbo.Table1 ON (dbo.Table2.ColA = dbo.Table1.ColA); Is there any equivalent to do as MSSQL did or I have to write store procedure to this update ? |
|
#3
|
|||
|
|||
|
Quote:
UPDATE TABLEA SET FIELD1 = (SELECT TABLEB.FIELD1 FROM TABLEB WHERE TABLEB.ID = TABLEA.ID), FIELD2= (SELECT TABLEB.FIELD2 FROM TABLEB WHERE TABLEB.ID = TABLEA.ID) |
|
#4
|
|||
|
|||
|
Quote:
Thank you for your valuable reply. I have 26000 rows in Table B and 2800 rows in Table A. I use a command per your suggestion it takes so long to get it done but it works. Is there any way to make it run faster. |
|
#5
|
|||
|
|||
|
Quote:
Are there indexes for TableA.ID and TableB.ID? |
|
#6
|
|||
|
|||
|
Quote:
I got it. Table B has no index. That why it so slow. Actually ID Field of Table A and B is the primary key. But I import the table from other database and I forgot to set the Primary. Now it work so fast. Thank to asking me about the index. |
![]() |
| Viewing: Dev Shed Forums > Databases > Firebird SQL Development > How to Update Column from column of another table |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
|
|