Could not update from table1 to table2 in 1 statement (MySQL limit: Pls see
manual)
try select what you want to update in buffer then put it in loop (look
slower than 1 statement but try to see how it fast enough)
Sommai Fongnamthip
At 04:34 30/7/2001 -0400, Steve Prior wrote:
>Here are some table definitions:
>
>CREATE TABLE x10_device_types
>(
> device_typename VARCHAR(20) NOT NULL,
> respondstoalloff enum('TRUE','FALSE') NOT NULL,
> respondstoalllightson enum('TRUE','FALSE') NOT NULL,
> respondstoalllightsoff enum('TRUE','FALSE') NOT NULL,
> respondstoallunitsoff enum('TRUE','FALSE') NOT NULL,
> dimmable enum('TRUE','FALSE') NOT NULL,
> device_category VARCHAR(20) NOT NULL
>);
>
>CREATE TABLE x10_devices
>(
> device_name VARCHAR(25) NOT NULL,
> device_typename VARCHAR(20) NOT NULL,
> location_name VARCHAR(25) NOT NULL,
> x10_housecode CHAR(1) NOT NULL,
> x10_unitcode TINYINT unsigned NOT NULL,
> x10_dev_description VARCHAR(75),
> lastknownstate TINYINT unsigned
>);
>
>
>What I'm trying to do is to set the lastknownstate to 0 for all devices in
>x10_housecode='A' where they respond to
>respondstoallunitsoff and have a device_category='Module'.
>
>I can't figure out how to do this because the update criteria spans 2
>tables and
>I don't have views. I tried the following, but it doesn't like my listing of
>2 tables after the update. Any ideas?
>
>mysql> update x10_devices,x10_devicetypes set lastknownstate=16 where
>x10_devices.device_typename=x10_device_types.device_typename and
>x10_device_types.respondstoalloff and
>x10_device_types.device_category='Module'
>and x10_housecode='A';
>
>Steve
>
>---------------------------------------------------------------------
>Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
>To request this thread, e-mail <[EMAIL PROTECTED]>
>To unsubscribe, e-mail
><[EMAIL PROTECTED]>
>Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
