Updating from a select query with a left join

Question

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.

Why don't you just run the `UPDATE` query after the `SELECT`? — Explosion Pills, Mar 23 '13 at 16:36

score 1 · Accepted Answer · answered Mar 23 '13 at 16:37

1

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 (?)".

answered Mar 23 '13 at 16:37

atw13

719
3
7

Thank You for clarifying guys. I have added an update query at the end of the script to make the desired updates. Cheers – user2172307 Mar 23 '13 at 16:51

score 0 · Answer 2 · edited May 23 '17 at 12:21

0

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... )

edited May 23 '17 at 12:21

Community

1
1

answered Mar 23 '13 at 16:41

Athlan

6,389
4
38
56

HI Athlan. The script displays the messages first before marking them as read, so the select has to come before the update. Thanks for the links. – user2172307 Mar 23 '13 at 16:53
Yop, so you can join these records in UPDATE query or set them to IN() statement by collecting theirs ID's or execute the select statement again. – Athlan Mar 23 '13 at 17:00

Updating from a select query with a left join

2 Answers2