On a SQL Delete that joins multiple tables, will all tables be affected?
I want to join several tables, but I want only to delete lines from the threadsread table. I got the following SQL that showsthe lines I want to delete.
SELECT * FROM threadsread tr, threads t WHERE
tr.tid=t.tid and tr.uid=2111 and t.fid=30
On Mysql docs it states: "*For the multiple-table syntax, DELETE deletes from each tbl_name the rows that satisfy the conditions.*"
Getting the above select to a delete , will the threads table be also affected?
DELETE FROM threadsread tr, threads t
WHERE tr.tid=t.tid and tr.uid=211开发者_如何学C1 and t.fid=30
If it will, how can I get only the threadsread table affected?
In many (maybe most or all) SQL flavors, the delete statement you provide is actually invalid. Instead use:
DELETE FROM threadsread tr
USING threads t
WHERE tr.tid=t.tid AND tr.uid=2111 AND t.fid=30;
This makes it obvious that you are only deleting from the one table specified in the 'FROM' clause.
精彩评论