Update Inner Join (update in two tables) - Php; Mysql

Asked

Viewed 855 times

1

I have two tables and I want to do a Join to update both at the same time.

Table1: Clipping (id, dia_semana, data)

Table2: imagemClipping( id, id_clipping, titulo, dia_semana, data, imagem)

id_clipping is the common data on them. I would like to edit day of week and date. Could you help me?

my consultation:

//inicia a edição
$id = $_POST['i'];

//Gravando o nome do arquivo tabela do banco 

$data = $_POST['data'];
$nova_data = implode("-", array_reverse(explode("/", $data)));

$dia_semana = $_POST['dia_semana'];

 $sql = "UPDATE clipping INNER JOIN imagemClipping ON 'clipping.id' = 'imagemClipping.id_clipping'
 SET 'clipping.dia_semana' = clipping.$dia_semana + imagemClipping.$dia_semana,
 'clipping.data' = clipping.$nova_data + imagemClipping.$nova_data WHERE id = $id";
mysql_query($sql) or die (mysql_error());

    echo "<script language='javascript'>
 alert('Editou tudo');
 parent.location='cadastro_clipping.php';
   </script>
";
php mysql sql join inner

2 answers

3

According to the syntax below, the UPDATE accepts only one table. And in the clause FROM it is possible to use more than one table. A recommendation for these cases is to perform 2 separate UPDATES and involve the 2 in a TRANSACTON, using BEGIN TRANSACTION and COMMIT TRANSACTION. In addition to an error treatment with ROLLBACK TRANSACTION, Because if an error occurs in the second UPDATE the first one is undone. Following example below, after the syntax.

Syntax

UPDATE 
    [ TOP ( expression ) [ PERCENT ] ] 
    { { table_alias | <object> | rowset_function_limited 
         [ WITH ( <Table_Hint_Limited> [ ...n ] ) ]
      }
      | @table_variable    
    }
    SET
        { column_name = { expression | DEFAULT | NULL }
          | { udt_column_name.{ { property_name = expression
                                | field_name = expression }
                                | method_name ( argument [ ,...n ] )
                              }
            }
          | column_name { .WRITE ( expression , @Offset , @Length ) }
          | @variable = expression
          | @variable = column = expression
          | column_name { += | -= | *= | /= | %= | &= | ^= | |= } expression
          | @variable { += | -= | *= | /= | %= | &= | ^= | |= } expression
          | @variable = column { += | -= | *= | /= | %= | &= | ^= | |= } expression
        } [ ,...n ] 

    [ <OUTPUT Clause> ]
    [ FROM { <table_source> } [ ,...n ] ] 
    [ WHERE { <search_condition> 
            | { [ CURRENT OF 
                  { { [ GLOBAL ] cursor_name } 
                      | cursor_variable_name 
                  } 
                ]
              }
            } 
    ]

Example

BEGIN TRANSACTION;  

BEGIN TRY  

    //Atualizacao da tabela1 com dados da tabela3 com JOIN
    UPDATE dbo.Table1 
    SET dbo.Table1.ColA = dbo.Table3.ColA + dbo.Table3.ColB
    FROM dbo.Table3 
    INNER JOIN dbo.Table1 
    ON (dbo.Table3.ColA = dbo.Table3.ColA);

    //Atualizacao da tabela2 com dados da tabela1 com JOIN
    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);

END TRY  
BEGIN CATCH  
    SELECT   
        ERROR_NUMBER() AS ErrorNumber  
        ,ERROR_SEVERITY() AS ErrorSeverity  
        ,ERROR_STATE() AS ErrorState  
        ,ERROR_PROCEDURE() AS ErrorProcedure  
        ,ERROR_LINE() AS ErrorLine  
        ,ERROR_MESSAGE() AS ErrorMessage;  

    IF @@TRANCOUNT > 0  
        ROLLBACK TRANSACTION;  
END CATCH;  

IF @@TRANCOUNT > 0  
    COMMIT TRANSACTION;  
GO

1

The JOIN with UPDATE has the function of only contributing to the filter of the records that will be updated. Therefore it is not possible to update two tables with the same UPDATE. In this case you will have to actually perform each update individually for your respective table.

Browser other questions tagged php mysql sql join inner

You are not signed in. Login or sign up in order to post.