Jesse wrote:
I need to be able to do an UPDATE based on a relation. The following
code works in Microsoft SQL. What is the MySQL Equivalent?
UPDATE Chapters
SET MatSentDate='2006-07-04'
FROM Invoices I JOIN Chapters C ON C.ID=I.ChapterID
JOIN Schools S ON S.ID=C.SchoolID
WHERE I.InvoiceDate >= '2006-01-06' AND I.InvoiceDate <= '2006-01-31'
AND MatSentDate IS NULL
Thanks,
Jesse
Hi, Jess
What version of mysql you're using? Since v 4.0 (I think) it is possible
to use query like this:
update t1, t2 set t1.field = t2.value where t1.this = t2.that;
or in your case s.th. like:
UPDATE Invoices I JOIN Chapters C ON C.ID=I.ChapterID
JOIN Schools S ON S.ID=C.SchoolID
SET C.MatSentDate='2006-07-04'
WHERE I.InvoiceDate >= '2006-01-06' AND I.InvoiceDate <= '2006-01-31'
AND MatSentDate IS NULL
- for more insight please read for multi-table updates here:
http://dev.mysql.com/doc/refman/5.1/en/update.html
...
You can also perform UPDATE operations covering multiple tables.
However, you cannot use ORDER BY or LIMIT with a multiple-table UPDATE.
The table_references clause lists the tables involved in the join. Its
syntax is described in Section 13.2.7.1, “JOIN Syntax”. Here is an example:
UPDATE items,month SET items.price=month.price
WHERE items.id=month.id;
HTH
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
