CODEWITHSUNDEEP
phpmysqlselectsql-update
user2172307
user2172307

Reputation: 31

Updating from a select query with a left join

I ask this with limited SQL understanding, apologies in advance if it’s obvious. I have a select query that returns a result set of ‘unread’ messages and matches the sender ID from a table of users (called authors) for a simple web application I am working on. The tables are described below:

Authors:

aId int(20) NO  PRI NULL    auto_increment
aUser   varchar(30) NO  UNI NULL    
aPass   varchar(40) NO      NULL    
aEmail  varchar(30) NO  UNI NULL    
aBio    mediumtext  YES     NULL    
aReg    datetime    NO      NULL

Messages:

msgId   int(20) NO  PRI NULL    auto_increment
mSender int(20) NO  MUL NULL    
mReciever   int(20) NO  MUL NULL    
mTitle  tinytext    NO      NULL    
mBody   mediumtext  NO      NULL    
mRead   tinyint(4)  NO      NULL    
mDate   datetime    NO      NULL

The select query is I am using is (Tks martina): 

 SELECT messages.mTitle, messages.mBody, messages.mDate, authors.aUser 
 FROM messages LEFT JOIN authors on aId=mSender WHERE mReciever = '$aId' AND mRead = '0'

$aId is a php session variable that contains the userID of the active user. My php application will assume since this ‘unread’ message has been processed it is now read, could anyone assist with modification of the above query to also update mRead to 1, indicating it is read, from an existing post on here i read you can't SELECT with UPDATE in the same SQL statement, would there be another way around this, am i miles away from the correct solution?.any help much appreciated. Many thanks in advance.

Upvotes: 0

Views: 721

Answers (2)

Athlan
Athlan

Reputation: 6619

You can JOIN the table during UPDATE:

UPDATE table1
LEFT JOIN table2 ON(table1.id = table2.id)
SET ...
WHERE ...

and select the records in WHERE statement.

Related: https://stackoverflow.com/a/806925/1815881

My blog post: http://athlan.pl/mysql-update-join/

The second way is select ID's of records to update and execute:

UPDATE table
SET ...
WHERE id IN( ...your select... )

Upvotes: 0

atw13
atw13

Reputation: 719

What you heard is correct. You cannot SELECT and UPDATE in one statement. Your PHP code has to execute two different SQL commands. After selecting, use PHP with your result to figure out the IDs of the messages you're returning, and then execute an UPDATE statement on the messages table, something like "UPDATE messages SET mRead=1 WHERE msgId IN (?)".

Upvotes: 1

Related Questions