RE: [PHP-DB] Subquery
It doesn't work because MySQL version 3.23.50 does not support sub-select statement. http://dev.mysql.com/doc/mysql/en/subqueries.html And How to re-write it in MySQL < 4.1 http://dev.mysql.com/doc/mysql/en/rewriting-subqueries.html Nandar wrote on Monday, 19 September 2005 1:03 PM: > i hope it's helping > > sql code: > > UPDATE T020Employee SET T020FaxNo = T010FaxNo FROM T020Employee a JOIN > T010Company b ON a.T020CoCd =b.T010CoCd > > - Original Message - > From: "Ng Hwee Hwee" <[EMAIL PROTECTED]> > To: "PHP DB List" > Sent: Monday, September 19, 2005 9:14 AM > Subject: [PHP-DB] Subquery > > > hi, > > can some one enlighten me on what is wrong with my query? > > I want to set the fax number of all the employees of a particular > company to the company's fax number. This is because the employees do > not have personal fax machines, thus instead of just leaving T020FaxNo > blank, I want to update it with the company's fax number. > However, the following > doesn't work?! > i'm > hoping i don't have to write a PHP while loop to do this simple task! > =( > > UPDATE T020Employee SET T020FaxNo = ( SELECT T010FaxNo FROM > T010Company WHERE T010CoCd = 'XXX' ) WHERE T020CoCd = 'XXX' > > MySQL version: 3.23.58. PHP version: 4.3.4. > My database structure is: > T010Company > === > T010CoCd (primary key) > T010CoName > T010Address > T010TelNo > T010FaxNo > > T020Employee > === > T020CoCd (primary key, linking to T010CoCd) T020EmpCd (primary key) > T020JobTitle T020TelNo T020FaxNo > > Thanks so much! > Hwee -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Subquery
hi all, i found the solution! it's so easy actually.. i think my brain is not functioning well on a Monday morning!! ;p UPDATE T020Employee, T010Company SET T020FaxNo = T010FaxNo WHERE T020CoCd = T010CoCd thanks bastien and nandar for your help! =) hwee - Original Message - From: "Bastien Koert" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]>; Sent: Monday, September 19, 2005 11:30 AM Subject: RE: [PHP-DB] Subquery > only versions 4.1 and above support subqueries... > > bastien > > >From: "Ng Hwee Hwee" <[EMAIL PROTECTED]> > >To: "PHP DB List" > >Subject: [PHP-DB] Subquery > >Date: Mon, 19 Sep 2005 10:14:34 +0800 > > > >hi, > > > >can some one enlighten me on what is wrong with my query? > > > >I want to set the fax number of all the employees of a particular company > >to > >the company's fax number. This is because the employees do not have > >personal > >fax machines, thus instead of just leaving T020FaxNo blank, I want to > >update > >it with the company's fax number. However, the following doesn't work?! i'm > >hoping i don't have to write a PHP while loop to do this simple task! =( > > > >UPDATE T020Employee SET T020FaxNo = ( SELECT T010FaxNo FROM T010Company > >WHERE T010CoCd = 'XXX' ) WHERE T020CoCd = 'XXX' > > > >MySQL version: 3.23.58. PHP version: 4.3.4. > >My database structure is: > >T010Company > >=== > >T010CoCd (primary key) > >T010CoName > >T010Address > >T010TelNo > >T010FaxNo > > > >T020Employee > >=== > >T020CoCd (primary key, linking to T010CoCd) > >T020EmpCd (primary key) > >T020JobTitle > >T020TelNo > >T020FaxNo > > > >Thanks so much! > >Hwee > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Subquery
thanks but it doesn't work =( is it because i'm on MySQL version 3.23? i have an error 1064 when running it... hwee - Original Message - From: "Nandar" <[EMAIL PROTECTED]> To: "Ng Hwee Hwee" <[EMAIL PROTECTED]>; "PHP DB List" Sent: Monday, September 19, 2005 11:02 AM Subject: Re: [PHP-DB] Subquery > i hope it's helping > > sql code: > > UPDATE T020Employee SET T020FaxNo = T010FaxNo > FROM T020Employee a JOIN T010Company b ON a.T020CoCd =b.T010CoCd > > - Original Message - > From: "Ng Hwee Hwee" <[EMAIL PROTECTED]> > To: "PHP DB List" > Sent: Monday, September 19, 2005 9:14 AM > Subject: [PHP-DB] Subquery > > > hi, > > can some one enlighten me on what is wrong with my query? > > I want to set the fax number of all the employees of a particular > company to > the company's fax number. This is because the employees do not have > personal > fax machines, thus instead of just leaving T020FaxNo blank, I want to > update > it with the company's fax number. However, the following doesn't work?! > i'm > hoping i don't have to write a PHP while loop to do this simple task! =( > > UPDATE T020Employee SET T020FaxNo = ( SELECT T010FaxNo FROM T010Company > WHERE T010CoCd = 'XXX' ) WHERE T020CoCd = 'XXX' > > MySQL version: 3.23.58. PHP version: 4.3.4. > My database structure is: > T010Company > === > T010CoCd (primary key) > T010CoName > T010Address > T010TelNo > T010FaxNo > > T020Employee > === > T020CoCd (primary key, linking to T010CoCd) > T020EmpCd (primary key) > T020JobTitle > T020TelNo > T020FaxNo > > Thanks so much! > Hwee > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Subquery
only versions 4.1 and above support subqueries... bastien From: "Ng Hwee Hwee" <[EMAIL PROTECTED]> To: "PHP DB List" Subject: [PHP-DB] Subquery Date: Mon, 19 Sep 2005 10:14:34 +0800 hi, can some one enlighten me on what is wrong with my query? I want to set the fax number of all the employees of a particular company to the company's fax number. This is because the employees do not have personal fax machines, thus instead of just leaving T020FaxNo blank, I want to update it with the company's fax number. However, the following doesn't work?! i'm hoping i don't have to write a PHP while loop to do this simple task! =( UPDATE T020Employee SET T020FaxNo = ( SELECT T010FaxNo FROM T010Company WHERE T010CoCd = 'XXX' ) WHERE T020CoCd = 'XXX' MySQL version: 3.23.58. PHP version: 4.3.4. My database structure is: T010Company === T010CoCd (primary key) T010CoName T010Address T010TelNo T010FaxNo T020Employee === T020CoCd (primary key, linking to T010CoCd) T020EmpCd (primary key) T020JobTitle T020TelNo T020FaxNo Thanks so much! Hwee -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Subquery
i hope it's helping sql code: UPDATE T020Employee SET T020FaxNo = T010FaxNo FROM T020Employee a JOIN T010Company b ON a.T020CoCd =b.T010CoCd - Original Message - From: "Ng Hwee Hwee" <[EMAIL PROTECTED]> To: "PHP DB List" Sent: Monday, September 19, 2005 9:14 AM Subject: [PHP-DB] Subquery hi, can some one enlighten me on what is wrong with my query? I want to set the fax number of all the employees of a particular company to the company's fax number. This is because the employees do not have personal fax machines, thus instead of just leaving T020FaxNo blank, I want to update it with the company's fax number. However, the following doesn't work?! i'm hoping i don't have to write a PHP while loop to do this simple task! =( UPDATE T020Employee SET T020FaxNo = ( SELECT T010FaxNo FROM T010Company WHERE T010CoCd = 'XXX' ) WHERE T020CoCd = 'XXX' MySQL version: 3.23.58. PHP version: 4.3.4. My database structure is: T010Company === T010CoCd (primary key) T010CoName T010Address T010TelNo T010FaxNo T020Employee === T020CoCd (primary key, linking to T010CoCd) T020EmpCd (primary key) T020JobTitle T020TelNo T020FaxNo Thanks so much! Hwee -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Subquery
hi, can some one enlighten me on what is wrong with my query? I want to set the fax number of all the employees of a particular company to the company's fax number. This is because the employees do not have personal fax machines, thus instead of just leaving T020FaxNo blank, I want to update it with the company's fax number. However, the following doesn't work?! i'm hoping i don't have to write a PHP while loop to do this simple task! =( UPDATE T020Employee SET T020FaxNo = ( SELECT T010FaxNo FROM T010Company WHERE T010CoCd = 'XXX' ) WHERE T020CoCd = 'XXX' MySQL version: 3.23.58. PHP version: 4.3.4. My database structure is: T010Company === T010CoCd (primary key) T010CoName T010Address T010TelNo T010FaxNo T020Employee === T020CoCd (primary key, linking to T010CoCd) T020EmpCd (primary key) T020JobTitle T020TelNo T020FaxNo Thanks so much! Hwee
[PHP-DB] subquery
I'm trying to use a delete subquery in Oracle (using SQL) and it is preventing me from deleting when no records are found in the SELECT statement. DELETE roles WHERE roleid NOT IN (SELECT DISTINCT roleid FROM users WHERE roleid='Staff Member'; There are no records in the USERS table that has a roleid='Staff Members' Any suggestions? Jimmy Brock Web Developer
