change given a table with other data

[nanadomg]

good the problem and the following I have to change the data of a table with the data of another and I can not appear the message of "sql command not closed correctly, there goes my SQL

Select all

 
UPDATE TB_1 SET data_1 = TB_2.data_2 
FROM TB_2  
where TB_1.cod=TB_2.cod  
and TB_2.data_2<TB_1.data_1  
and TB_2.seq=1  
and TB_1.cod='11111111' 
/ 

[victorhugomuniz]

take a look [url=http://en.glufke.net/oracle/viewtopic.php?t=1656]aqui

[nanadomg]

friend vlw by tip but none of the sql shown in the topic worked, because for me to update it is not enough only the tb_1 cod to be equal to the tb_2 cod.


I want to update the date_1 of the TB_1 with the value of the date_2 of the TB_2

when the value of the date_2 is less than the date_1 Data_2 has to be sequencing = 1 (because for the same COD I have several plugs with the same COD) and a specific code mentioned

[nanadomg]

Example

tb_1

Select all

cod       data_1 
1          20/01/2010 
2          12/02/2009 
3          13/06/2008

tb_2

Select all

cod       data_2            seq 
1          17/10/1999      1  
1          10/01/2010      2 
2          12/03/2009      1 
3          13/05/2008      1

99]] TB_1 has to be thus

Select all

cod          data_1 
1              17/10/1999 
2              12/02/2009 
3              13/05/2008

thanks

[victorhugomuniz]

take a look with affection in your code ..

Select all

UPDATE TB_1 SET data_1 = TB_2.data_2 
FROM..

I think this of update from it does not work no ..

[gfkauer]

Your update would not work by the way this structured, try something close to this:

Select all

 
Update tb_1 
   Set data_1 = (Select tb_2.data_2 
                   From tb_2 
                  Where tb_1.cod = tb_2.cod 
                    And tb_2.data_2 < tb_1.data_1 
                    And tb_2.seq = 1) 
 Where tb_1.cod = '11111111' /

[SergioLBJr]

Daria to do with a subselect in the update

something like

Select all

 
update tab1 
set tab1.campo = (select tab2.campo from tab2 where tab2.codigo = tab1.codigo) 
where tab1.codigo = &codigo 

just have to pay attention to the subselect bring only one result, otherwise going to crash.

[gpngiovani]

I'll enjoy the same topic, I'm starting in SQL and I need a help.
I have two tables (SB2000 'Inventing Balance' and SB9000 'Initial Balance') where I need to update the SB2000.B2_QIINI field with the contents of the field of another SB9.B9_QFIM table.
The detail is that both tables have vans branches, and the update will be only on a subsidiary '09', when the date of the table sb9000.b9_data = '20131031'
I have tried several forms without success, follow an example that went wrong.

Select all

 
UPDATE SB2000   
      SET B2_QFIM  = (SELECT SB9.B9_QINI FROM SB9000 SB9 WHERE 
                                           SB9.B9_LOCAL   = '01'           AND 
                                           SB9.B9_FILIAL    = '09'           AND 
                                           SB9.B9_DATA     = '20131031' AND 
                                           SB9.d_E_L_E_T_ = ' ' )        
   WHERE  
           SB2000.B2_LOCAL      =   '01' AND 
           SB2000.B2_FILIAL      =   '09' AND 
           SB2000.B2_COD         =   SB9.B9_COD AND 
           SB2000.d_E_L_E_T_   =  ' ' 

This is the error:

Error report -
SQL error: 00904: "SB9". "B9_COD": invalid identifier
00904. 00000 - "% s: invalid identifier"

ABS !!

[stcoutinho]

Hello gpngiovani,

Welcome to the Glufke Forum!

For better visibility, and for more people to respond faster to you, it is always good to create a new topic.

In your case, "SB9" is within a subcubray of the "set =" command.
The way you built your update, there is no mentioning SB columns outside the code set in "Set = (......)". That is, you can not mention it in the WHERE and neither in another column to update.

ABS,

Sergio

[gpngiovani]

OPÁ !!

Stcoutinho, would you like to give me an example of what the correct way would be? I'vê tried other ways but without success.

Thanks even for the return,

strong embrace !!

[stcoutinho]

Um ..

I do not know if your original queries was complete .. But try this:

Select all

 
UPDATE SB2000   
  SET B2_QFIM  = (SELECT B9_QINI  
                     FROM SB9000 
                    WHERE B9_LOCAL  = '01'            
                      AND B9_FILIAL = '09'          
                      AND B9_DATA     = '20131031'  
                      AND d_E_L_E_T_ = ' ' )        
WHERE B2_LOCAL   =   '01'  
  AND B2_FILIAL  =   '09'  
  AND d_E_L_E_T_ =  ' ' 
  AND B2_COD IN (SELECT B9_COD 
                      FROM SB9000  
                     WHERE B9_LOCAL  = '01'            
                       AND B9_FILIAL = '09'          
                       AND B9_DATA     = '20131031'  
                       AND d_E_L_E_T_ = ' ' )        

one thing : If your subcuberies return more than one b9_qini to the filter conditions in SB9000, your update command will generate an error because it is not possible to update a column with a list of values.

Hugs,

Sergio