 | | | UPDATE Based on Relation | UPDATE Based on Relation 2006-07-05 - By Remo Tex
Back
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/mysql?unsub=mysql@(protected)
|
|
|