2-table update using Database.php?

[Daniel Barrett-3]

Is there a way to perform a two-table SQL Update statement using functions in Database.php?

update table1, table2
set table1.x = table2.y
where table1.foo = table2.bar

Function Database.php::update() appears to accept only a single table name. (This is in MW 1.12.)

Thanks.
DanB

_______________________________________________
MediaWiki-l mailing list
MediaWiki-l@...
https://lists.wikimedia.org/mailman/listinfo/mediawiki-l

[Jim R. Wilson]

Looks like there's no built-in way to do a multi-table update, but you
could construct such a function in a manner such as this:

function updateMultiple( &$dbw, $tables, $values, $conds, $fname =
null, $options = array() ) {
        if (!$fname) $fname = __METHOD__;
        $tables = $dbw->tableNamesWithUseIndexOrJOIN( $tables );
        $opts = $dbw->makeUpdateOptions( $options );
        $sql = "UPDATE $opts $tables SET " . $dbw->makeList( $values, LIST_SET );
        if ( $conds != '*' ) {
                $sql .= " WHERE " . $dbw->makeList( $conds, LIST_AND );
        }
        return $dbw->query( $sql, $fname );
}

Then

$dbw =& wfGetDB( DB_MASTER );
updateMultiple( $dbw, array( 'table1', 'table2' ), "table1.x =
table2.y", "table1.foo = table2.bar", __METHOD__ );

Disclaimer: I have not tested the above at all - mostly just
copy/pasted from Database::update.

-- Jim R. Wilson (jimbojw)

On Wed, Jul 16, 2008 at 8:36 AM, Daniel Barrett <danb@...> wrote:

> Is there a way to perform a two-table SQL Update statement using functions in Database.php?
>
> update table1, table2
> set table1.x = table2.y
> where table1.foo = table2.bar
>
> Function Database.php::update() appears to accept only a single table name. (This is in MW 1.12.)
>
> Thanks.
> DanB
>
> _______________________________________________
> MediaWiki-l mailing list
> MediaWiki-l@...
> https://lists.wikimedia.org/mailman/listinfo/mediawiki-l
>

_______________________________________________
MediaWiki-l mailing list
MediaWiki-l@...
https://lists.wikimedia.org/mailman/listinfo/mediawiki-l

[Daniel Barrett-3]

Thanks Jim, that's pretty much what I did (while awaiting a better method).  I appreciate the help.

DanB

-----Original Message-----
function updateMultiple( &$dbw, $tables, $values, $conds, $fname =
null, $options = array() ) {
        if (!$fname) $fname = __METHOD__;
        $tables = $dbw->tableNamesWithUseIndexOrJOIN( $tables );
        $opts = $dbw->makeUpdateOptions( $options );
        $sql = "UPDATE $opts $tables SET " . $dbw->makeList( $values, LIST_SET );
        if ( $conds != '*' ) {
                $sql .= " WHERE " . $dbw->makeList( $conds, LIST_AND );
        }
        return $dbw->query( $sql, $fname );
}

_______________________________________________
MediaWiki-l mailing list
MediaWiki-l@...
https://lists.wikimedia.org/mailman/listinfo/mediawiki-l

[Giuseppe Briotti]

> Message: 2
> Date: Wed, 16 Jul 2008 09:36:24 -0400
> From: Daniel Barrett <danb@...>
> Subject: [Mediawiki-l] 2-table update using Database.php?
> To: MediaWiki announcements and site admin list
> <mediawiki-l@...>
> Message-ID:
> <9445EBEE35E2D148921D2550399D1D3715CB321E@...>
> Content-Type: text/plain; charset="us-ascii"
>
> Is there a way to perform a two-table SQL Update statement using
> functions in Database.php?
>
> update table1, table2
> set table1.x = table2.y
> where table1.foo = table2.bar
>
> Function Database.php::update() appears to accept only a single
> table name. (This is in MW 1.12.)
>
> Thanks.
> DanB
>

mmm... this seems a SQL issue :-)

well, you can update joined table, but you must select the PK.

as example:

update (
   select table1.id, table1.x as x, table2.id, table2.y as y
   where table1.foo = table2.bar )
set x=y

G.

--

Giuseppe Briotti
g.briotti@...

"Alme Sol, curru nitido diem qui
promis et celas aliusque et idem
nasceris, possis nihil urbe Roma
visere maius."
                        (Orazio)





_______________________________________________
MediaWiki-l mailing list
MediaWiki-l@...
https://lists.wikimedia.org/mailman/listinfo/mediawiki-l