CODEWITHSUNDEEP
phpmysqlsql
user4655002
user4655002

Reputation:

Updating one table if another table id matches

Okay, I am trying to update a table depending if it belongs to the user, So In one table for example I have pictures, like so:

+----------------------------------------------------+
| picture_id   |   picture_user   |    picture_title |
+----------------------------------------------------+

and in another table I have:

+--------------------------------------------------------------+
| delete_pic_id   |  delete_pic_user  |   delete_pic_status    |
+--------------------------------------------------------------+

now I want to update delete_pic_status only if the delete_pic_user matches the picture_user

Here's what I am thinking but need a little help:

$manage_application = $database->prepare(
            "UPDATE deleted_pic 
              SET delete_pic_status=1 
             WHERE delete_pic_user(the other table)=the id");

How would I go about accomplishing this

Upvotes: 2

Views: 66

Answers (5)

david sam
david sam

Reputation: 531

UPDATE deleted_pic SET delete_pic_status=1 WHERE deleted_pic.delete_pic_user in(SELECT picture_user from tab2)

Upvotes: 0

Zafar Malik
Zafar Malik

Reputation: 6844

Below query will fulfill your need-

update all rows based on checking existence in another table

UPDATE deleted_pic dp 
JOIN other_table ot ON ot.picture_user = dp.delete_pic_user
SET delete_pic_status = 1;

update one table's data in another table for all rows-

UPDATE deleted_pic dp 
JOIN other_table ot ON ot.picture_user = dp.delete_pic_user
SET dp.column1 = ot.column5;

update one table's data in another table for specific rows-

UPDATE deleted_pic dp 
JOIN other_table ot ON ot.picture_user = dp.delete_pic_user
SET dp.column1 = ot.column5
where dp.status=1;

Upvotes: 0

RiggsFolly
RiggsFolly

Reputation: 94652

I assume you are using PDO and as you have the id of the user in a session variable you can use a parameterised query like this

$sth = $database->prepare( "UPDATE deleted_pic 
                              SET delete_pic_status = 1 
                             WHERE delete_pic_user = :id");

$result = $sth->execute(array(':id' => $_SESSION['userId']) );

if ( $result ) {
    // the delete worked
} else {
    // the delete failed
}

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93694

Use Exists

UPDATE deleted_pic
SET    delete_pic_status = 1
WHERE  EXISTS (SELECT 1
               FROM   other_table ot
               WHERE  ot.picture_user = deleted_pic.delete_pic_user)

Upvotes: 2

apomene
apomene

Reputation: 14389

UPDATE deleted_pic SET delete_pic_status=1 WHERE 
delete_pic_user in (select picture_user from myOthertable)

Upvotes: 1

Related Questions