Altering column syntax help/and repare
I think I really misunderstood the directions for alter table, an I'm not sure how to fix this. I was trying to allow certain columns to be null in an existing table. I used the following syntax: ALTER TABLE users ALTER COLUMN title SET DEFAULT NULL; Now 'describe users;' shows extra columns with no titles. A. What did that syntax do? B. How do I undo it? C. What is the proper syntax? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Altering column syntax help/and repare
I think I really misunderstood the directions for alter table, an I'm not sure how to fix this. I was trying to allow certain columns to be null in an existing table. I used the following syntax: ALTER TABLE users ALTER COLUMN title SET DEFAULT NULL; Now 'describe users;' shows extra columns with no titles. Extra columns with no titles, what does that mean? A. What did that syntax do? B. How do I undo it? C. What is the proper syntax? SET DEFAULT NULL has nothing to do with the null-ability of a column, it has to do with the DEFAULT of a column. Proper syntax: http://dev.mysql.com/doc/refman/5.0/en/alter-table.html Check out: MODIFY [COLUMN] column_definitionMartijn ToniesDatabase Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL ServerUpscene Productionshttp://www.upscene.comDatabase development questions? Check the forum!http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Advanced SELECT Syntax Help Needed!
On Fri, 5 Nov 2004 17:49:29 -0500, Rhino [EMAIL PROTECTED] wrote: I'm not sure why you want to use a subquery; if MySQL is anything like DB2, a join usually performs better than a subquery and the optimizer converts a subquery to a join (under the covers) whenever it can anyway. Therefore, how about something like: select id, name, linkname1, linkname2 from main m right outer join links1 l1 on m.id = l1.id right outer join links l2 on m.id = l2.id; Yes, indeed joins usually perform better than subselects. I agree to your suggested query, but I think we ought to use LEFT joins here rather than RIGHT joins because Monique wants to receive any main record regardless of the existence of corresponding links. So the query I would use is: SELECT m.id, l1.linkname1, l2.linkname2 FROM main m LEFT JOIN links1 l1 ON (m.id = l1.id) LEFT JOIN links2 l2 ON (m.id = l2.id); Please let us know if it worked, Monique. Regards Fred -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Advanced SELECT Syntax Help Needed!
Thank you Frederic! It worked perfectly!!! I didn't know you could have several joins linked together! Monique. -Original Message- From: Frederic Wenzel [mailto:[EMAIL PROTECTED] Sent: Saturday, November 06, 2004 3:56 AM To: Rhino Cc: Monique; [EMAIL PROTECTED] Subject: Re: Advanced SELECT Syntax Help Needed! On Fri, 5 Nov 2004 17:49:29 -0500, Rhino [EMAIL PROTECTED] wrote: I'm not sure why you want to use a subquery; if MySQL is anything like DB2, a join usually performs better than a subquery and the optimizer converts a subquery to a join (under the covers) whenever it can anyway. Therefore, how about something like: select id, name, linkname1, linkname2 from main m right outer join links1 l1 on m.id = l1.id right outer join links l2 on m.id = l2.id; Yes, indeed joins usually perform better than subselects. I agree to your suggested query, but I think we ought to use LEFT joins here rather than RIGHT joins because Monique wants to receive any main record regardless of the existence of corresponding links. So the query I would use is: SELECT m.id, l1.linkname1, l2.linkname2 FROM main m LEFT JOIN links1 l1 ON (m.id = l1.id) LEFT JOIN links2 l2 ON (m.id = l2.id); Please let us know if it worked, Monique. Regards Fred -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Advanced SELECT Syntax Help Needed!
Hi! I would love some help with my syntax (or another strategy). I keep bombing. I've simplified it. Here is the deal: Three files: Main: id, name Links1: id, linkname1 (a record may or may not exist for each record in Main) Links2: id, linkname2 (a record may or may not exist for each record in Main) I need a list of every record in main plus linkname1 and linkname2. I am trying to use a subquery. SELECT name, linkname2, links1.id, links1.linkname1 FROM (SELECT main.id, main.name, links2.linkname2 from main LEFT OUTER JOIN links2 using (id)) AS subfile LEFT OUTER JOIN links1 using (id) I keep getting syntax errors. I would really appreciate any input. Thanks a bunch -- Monique.
Re: Advanced SELECT Syntax Help Needed!
I'm not sure why you want to use a subquery; if MySQL is anything like DB2, a join usually performs better than a subquery and the optimizer converts a subquery to a join (under the covers) whenever it can anyway. Therefore, how about something like: select id, name, linkname1, linkname2 from main m right outer join links1 l1 on m.id = l1.id right outer join links l2 on m.id = l2.id; I haven't tested it but it ought to work. Rhino - Original Message - From: Monique [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, November 05, 2004 4:59 PM Subject: Advanced SELECT Syntax Help Needed! Hi! I would love some help with my syntax (or another strategy). I keep bombing. I've simplified it. Here is the deal: Three files: Main: id, name Links1: id, linkname1 (a record may or may not exist for each record in Main) Links2: id, linkname2 (a record may or may not exist for each record in Main) I need a list of every record in main plus linkname1 and linkname2. I am trying to use a subquery. SELECT name, linkname2, links1.id, links1.linkname1 FROM (SELECT main.id, main.name, links2.linkname2 from main LEFT OUTER JOIN links2 using (id)) AS subfile LEFT OUTER JOIN links1 using (id) I keep getting syntax errors. I would really appreciate any input. Thanks a bunch -- Monique. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
newbie ALTER syntax help
alter table keywords change key_id page_id int(10) unsigned NOT NULL PK auto_increment; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PK auto_increment' at line 1 I'm trying to change the name of a column, but I have to also restate the type and sttribute on the column while i do that AFAIK. Can someone show me my syntax error? The statement above is exactlymy try from the mysql comd. line Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: newbie ALTER syntax help
At 12:12 -0400 9/23/04, leegold wrote: alter table keywords change key_id page_id int(10) unsigned NOT NULL PK auto_increment; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PK auto_increment' at line 1 I'm trying to change the name of a column, but I have to also restate the type and sttribute on the column while i do that AFAIK. Can someone show me my syntax error? The statement above is exactlymy try from the mysql comd. line Thanks. The problem is shown by the error message: PK isn't valid. Do you mean PRIMARY KEY? -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: newbie ALTER syntax help
On Thu, 23 Sep 2004 15:51:56 -0500, Paul DuBois [EMAIL PROTECTED] said: At 12:12 -0400 9/23/04, leegold wrote: alter table keywords change key_id page_id int(10) unsigned NOT NULL PK auto_increment; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PK auto_increment' at line 1 I'm trying to change the name of a column, but I have to also restate the type and sttribute on the column while i do that AFAIK. Can someone show me my syntax error? The statement above is exactlymy try from the mysql comd. line Thanks. The problem is shown by the error message: PK isn't valid. Do you mean PRIMARY KEY? Yes. Fixed it, Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
query syntax help
Hello all, I've been looking at this SQL query a dozen times or more, but keep getting a syntax error message, Query: SELECT ai.affilid,ai.create_date,CONCAT(ai.fname,' ',ai.lname) AS name,aw.siteid,ai.email,as.username,as.status FROM affiliate_info ai,affiliate_signup as,affiliate_website aw WHERE aw.siteid = 1000 AND ai.affilid = as.affilid AND aw.affilid = ai.affilid what is wrong with this query syntax ?? the syntax error is suppose to be in this area: `affiliate_website aw WHERE aw.siteid = 1000` MySQL version 4.0.15 w/InnoDB tables TIA, -- MikemickaloBlezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://www.thunder-rain.com Quality Web Hosting http://www.justlightening.net MSN: [EMAIL PROTECTED] =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query syntax help
On Tue, Jan 20, 2004 at 04:10:44PM -0600, Mike Blezien wrote: I've been looking at this SQL query a dozen times or more, but keep getting a syntax error message, Query: SELECT ai.affilid,ai.create_date,CONCAT(ai.fname,' ',ai.lname) AS name,aw.siteid,ai.email,as.username,as.status FROM affiliate_info ai,affiliate_signup as,affiliate_website aw AS is a reserved word. WHERE aw.siteid = 1000 AND ai.affilid = as.affilid AND aw.affilid = ai.affilid what is wrong with this query syntax ?? the syntax error is suppose to be in this area: `affiliate_website aw WHERE aw.siteid = 1000` A bit before that. Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query syntax help
Mike Blezien said: I've been looking at this SQL query a dozen times or more, but keep getting a syntax error message, Query: SELECT ai.affilid,ai.create_date,CONCAT(ai.fname,' ',ai.lname) AS name,aw.siteid,ai.email,as.username,as.status FROM affiliate_info ai,affiliate_signup as,affiliate_website aw ^^ reserved word WHERE aw.siteid = 1000 AND ai.affilid = as.affilid AND aw.affilid = ai.affilid Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query syntax help
Thx's Fred... as soon as I sent the email and re-read it again... I spotted the 'as' alias table reference to the table, was actual a reserved word,..causing the error :) thx's again. -- MikemickaloBlezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://www.thunder-rain.com Quality Web Hosting http://www.justlightening.net MSN: [EMAIL PROTECTED] =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Fred van Engen wrote: On Tue, Jan 20, 2004 at 04:10:44PM -0600, Mike Blezien wrote: I've been looking at this SQL query a dozen times or more, but keep getting a syntax error message, Query: SELECT ai.affilid,ai.create_date,CONCAT(ai.fname,' ',ai.lname) AS name,aw.siteid,ai.email,as.username,as.status FROM affiliate_info ai,affiliate_signup as,affiliate_website aw AS is a reserved word. WHERE aw.siteid = 1000 AND ai.affilid = as.affilid AND aw.affilid = ai.affilid what is wrong with this query syntax ?? the syntax error is suppose to be in this area: `affiliate_website aw WHERE aw.siteid = 1000` A bit before that. Regards, Fred. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
select query syntax help
Hello All, I have two table the look like this (greatly simplified): ResourceTable - int ResourceID var ResourceName ResourceLinkTable - int ResourceLinkID int ResourceID var Text I need to find all rows in ResourceTable for which there is NO entry in ResourceLinkTable. I know I could do this with sub-selects like this: Select * from ResourceTable where ResourceID not in (select distinct ResourceID from ResourceLinkTable) How can I do this in MySQL 4.0 without using sub-selects? Thanks, Dan Lamb -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: select query syntax help [ANSWER]
This is a common question. The syntax looks like this: SELECT a.* FROM tbl_a AS a LEFT JOIN tbl_b AS b ON a.id = b.id WHERE b.id.id IS NULL; The idea is you're retrieving a recordset of the two tables where the rows are joined on the id. For tbl_b, the id field has no value (its null) so you can identify those rows by asking for nulls in the `tbl_b` `id` column. In your case, I would try: SELECT tbl.* FROM ResourceTable AS tbl LEFT JOIN ResourceLinkTable AS lnk ON tbl.ResourceID= lnk.ResourceID WHERE lnk.ResourceID.id IS NULL ORDER BY ResourceName ASC; Regards, Adam -Original Message- From: Dan Lamb [mailto:[EMAIL PROTECTED] Sent: Thursday, October 30, 2003 9:39 AM To: [EMAIL PROTECTED] Subject: select query syntax help Hello All, I have two table the look like this (greatly simplified): ResourceTable - int ResourceID var ResourceName ResourceLinkTable - int ResourceLinkID int ResourceID var Text I need to find all rows in ResourceTable for which there is NO entry in ResourceLinkTable. I know I could do this with sub-selects like this: Select * from ResourceTable where ResourceID not in (select distinct ResourceID from ResourceLinkTable) How can I do this in MySQL 4.0 without using sub-selects? Thanks, Dan Lamb -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select query syntax help
Dan, SELECT ResourceTable.* FROM ResourceTable LEFT JOIN ResourceLinkTable ON ResourceTable.ResourceID = ResourceLinkTable.ResourceID WHERE ResourceLinkTable.ResourceID IS NULL; Regards, Thomas On Thu, 30 Oct 2003, Dan Lamb wrote: Hello All, I have two table the look like this (greatly simplified): ResourceTable - int ResourceID var ResourceName ResourceLinkTable - int ResourceLinkID int ResourceID var Text I need to find all rows in ResourceTable for which there is NO entry in ResourceLinkTable. I know I could do this with sub-selects like this: Select * from ResourceTable where ResourceID not in (select distinct ResourceID from ResourceLinkTable) How can I do this in MySQL 4.0 without using sub-selects? Thanks, Dan Lamb -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SHA literal String Syntax Help
I need help with the proper syntax of my INSERT
Statement.
I have spoken to the support staff of my RADIUS
Vendor they stated that enable to support SHA
The Coolum for password has to have the encrypted
password prefixed with {SHA} not just the hash
I need to include the literal string of {SHA} plus
the hash, see below.
Quote from support
When you select a user password from the table Radius will then retrieve:
{SHA}15346b593c4d0cf05fb6e67a5669d852e6550481
This one encrypts the whole string {SHA}'smith'
mysql INSERT INTO user_profile (userid,password,alias,profile)
- VALUES ('bob',SHA1({SHA}'smith'),'max',default);
Query OK, 1 row affected (0.00 sec)
This one pukes
mysql INSERT INTO user_profile (userid,password,alias,profile)
- VALUES ('bob',({SHA}SHA1'smith'),'max',default);
ERROR 1064 You have an error in you SQL syntax
This one has a could mismatch
mysql INSERT INTO user_profile (userid,password,alias,profile)
- VALUES ('bob',SHA,HA1'smith'),'max',default);
Please help I'm new to SQL and it's syntax flow.
Thanks
Jeff Stout
CSG Systems, Inc.
303-200-3204
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Idea: Syntax help on command line
Hello
I use the mysql command line tool quite often and always wondered why
there's no feature that lets me quickly see the syntax of a FOREIGN
KEY or GRANT command. Now while browsing the source I found the
new syntax command in 4.0.12 and got the idea of implementing this
syntax help of myself.
What do you think of a client command:
mysql syntax select; (or \S or SHOW SYNTAX OF)
SELECT [STRAIGHT_JOIN]
[SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
[SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS] [HIGH_PRIORITY]
[DISTINCT | DISTINCTROW | ALL]
select_expression,...
[INTO {OUTFILE | DUMPFILE} 'file_name' export_options]
[FROM table_references
[WHERE where_definition]
[GROUP BY {unsigned_integer | col_name | formula} [ASC | DESC], ...]
[HAVING where_definition]
[ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC] ,...]
[LIMIT [offset,] rows | rows OFFSET offset]
[PROCEDURE procedure_name(argument_list)]
[FOR UPDATE | LOCK IN SHARE MODE]]
Reference: 6.4.1 http://www.mysql.com/doc/en/SELECT.html
I managed to hacked a working demo in less than 10 minutes which is
simply a copy of the syntax function and which uses syntax() to pipe the
output of an arbitrary command back to mysql (of course it could be a C
function, too, but why the unnecessary burden and having it as seperate
program makes it useable standalone, too!)
This program could be a perl program which either has syntax definitions
hardcoded or, even better, is able to extract them from the docs.
A preinstalled html documentation could be prerequisite which has
two comments like !-- BEGIN SYNTAX: SELECT -- ... !-- END SYNTAX --
so that the syntax is easily extractable. Something in the kind of
cat /usr/share/doc/mysql/en/$1.html
could be enough for the first start, too.
The final stage would then be that this help command is called whenever
on presses e.g. two times tab after writing a command like a command
completition.
Any suggestions and comments?
bye,
-christian-
P.S.: Please CC me, I'm not subscribed. Thanks.
--
The end of the human race will be that it will eventually die of
civilization.
-- Ralph Waldo Emerson
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: UPDATE syntax help
Very close. Only one table may be updated per UPDATE statement. Doug On Sun, 23 Mar 2003 00:52:59 -, [EMAIL PROTECTED] wrote: Hi I may be way off base here but - why do you reference IMPORT_USERS in your UPDATE statement? You aren't updating any columns in that table. Regards, Sal -Original Message- From: MySQL [mailto:[EMAIL PROTECTED] Sent: 12 March 2003 22:33 To: MySQL Subject: UPDATE syntax help Hi all, I am having a little UPDATE syntax issue. According to the manual UPDATE EBA_USERS, IMPORT_USERS SET EBA_USERS.HUB_ID = IMPORT_USERS.HUB_ID, EBA_USERS.REP_LOCATION_ID = IMPORT_USERS.REP_LOCATION_ID, EBA_USERS.REP_FIRST_NAME = IMPORT_USERS.REP_FIRST_NAME, EBA_USERS.REP_LAST_NAME = IMPORT_USERS.REP_LAST_NAME, EBA_USERS.REP_DISABLED = IMPORT_USERS.REP_DISABLED WHERE EBA_USERS.REP_ID = IMPORT_USERS.REP_ID should work (as I understand it ;-) but I get the error ERROR 1064: You have an error in your SQL syntax near ' IMPORT_USERS SET EBA_USERS.HUB_ID = IIMPORT_USERS.HUB_ID, EBA_USERS.REP_LOCATION_ID = IMPO' at line 1 I did note in the comments section at the bottom someone else with same/similar problem, but have been unable to find a thread in the mail archive. My apologies if this has been dealt with already, or a workaround suggested... MySQL 3.23.51-nt on a Win2K box MyODBC 3.51 Thanks Jeff Creed Throbware (0417) 797 592 http://www.throbware.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: UPDATE syntax help
Hi I may be way off base here but - why do you reference IMPORT_USERS in your UPDATE statement? You aren't updating any columns in that table. Regards, Sal -Original Message- From: MySQL [mailto:[EMAIL PROTECTED] Sent: 12 March 2003 22:33 To: MySQL Subject: UPDATE syntax help Hi all, I am having a little UPDATE syntax issue. According to the manual UPDATE EBA_USERS, IMPORT_USERS SET EBA_USERS.HUB_ID = IMPORT_USERS.HUB_ID, EBA_USERS.REP_LOCATION_ID = IMPORT_USERS.REP_LOCATION_ID, EBA_USERS.REP_FIRST_NAME = IMPORT_USERS.REP_FIRST_NAME, EBA_USERS.REP_LAST_NAME = IMPORT_USERS.REP_LAST_NAME, EBA_USERS.REP_DISABLED = IMPORT_USERS.REP_DISABLED WHERE EBA_USERS.REP_ID = IMPORT_USERS.REP_ID should work (as I understand it ;-) but I get the error ERROR 1064: You have an error in your SQL syntax near ' IMPORT_USERS SET EBA_USERS.HUB_ID = IIMPORT_USERS.HUB_ID, EBA_USERS.REP_LOCATION_ID = IMPO' at line 1 I did note in the comments section at the bottom someone else with same/similar problem, but have been unable to find a thread in the mail archive. My apologies if this has been dealt with already, or a workaround suggested... MySQL 3.23.51-nt on a Win2K box MyODBC 3.51 Thanks Jeff Creed Throbware (0417) 797 592 http://www.throbware.com.au - 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
UPDATE syntax help
Hi all, I am having a little UPDATE syntax issue. According to the manual UPDATE EBA_USERS, IMPORT_USERS SET EBA_USERS.HUB_ID = IMPORT_USERS.HUB_ID, EBA_USERS.REP_LOCATION_ID = IMPORT_USERS.REP_LOCATION_ID, EBA_USERS.REP_FIRST_NAME = IMPORT_USERS.REP_FIRST_NAME, EBA_USERS.REP_LAST_NAME = IMPORT_USERS.REP_LAST_NAME, EBA_USERS.REP_DISABLED = IMPORT_USERS.REP_DISABLED WHERE EBA_USERS.REP_ID = IMPORT_USERS.REP_ID should work (as I understand it ;-) but I get the error ERROR 1064: You have an error in your SQL syntax near ' IMPORT_USERS SET EBA_USERS.HUB_ID = IIMPORT_USERS.HUB_ID, EBA_USERS.REP_LOCATION_ID = IMPO' at line 1 I did note in the comments section at the bottom someone else with same/similar problem, but have been unable to find a thread in the mail archive. My apologies if this has been dealt with already, or a workaround suggested... MySQL 3.23.51-nt on a Win2K box MyODBC 3.51 Thanks Jeff Creed Throbware (0417) 797 592 http://www.throbware.com.au - 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
Re: UPDATE syntax help
At 8:33 +1000 3/13/03, MySQL wrote: Hi all, I am having a little UPDATE syntax issue. According to the manual According to the manual, this won't work until MySQL 4.x UPDATE EBA_USERS, IMPORT_USERS SET EBA_USERS.HUB_ID = IMPORT_USERS.HUB_ID, EBA_USERS.REP_LOCATION_ID = IMPORT_USERS.REP_LOCATION_ID, EBA_USERS.REP_FIRST_NAME = IMPORT_USERS.REP_FIRST_NAME, EBA_USERS.REP_LAST_NAME = IMPORT_USERS.REP_LAST_NAME, EBA_USERS.REP_DISABLED = IMPORT_USERS.REP_DISABLED WHERE EBA_USERS.REP_ID = IMPORT_USERS.REP_ID should work (as I understand it ;-) but I get the error ERROR 1064: You have an error in your SQL syntax near ' IMPORT_USERS SET EBA_USERS.HUB_ID = IIMPORT_USERS.HUB_ID, EBA_USERS.REP_LOCATION_ID = IMPO' at line 1 I did note in the comments section at the bottom someone else with same/similar problem, but have been unable to find a thread in the mail archive. My apologies if this has been dealt with already, or a workaround suggested... MySQL 3.23.51-nt on a Win2K box MyODBC 3.51 Thanks Jeff Creed Throbware (0417) 797 592 http://www.throbware.com.au - 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
Re: UPDATE syntax help
That explains it then D'OH Is there a workaround? Thanks :-) - Original Message - From: Paul DuBois [EMAIL PROTECTED] To: MySQL [EMAIL PROTECTED]; MySQL [EMAIL PROTECTED] Sent: Thursday, March 13, 2003 9:02 AM Subject: Re: UPDATE syntax help At 8:33 +1000 3/13/03, MySQL wrote: Hi all, I am having a little UPDATE syntax issue. According to the manual According to the manual, this won't work until MySQL 4.x UPDATE EBA_USERS, IMPORT_USERS SET EBA_USERS.HUB_ID = IMPORT_USERS.HUB_ID, EBA_USERS.REP_LOCATION_ID = IMPORT_USERS.REP_LOCATION_ID, EBA_USERS.REP_FIRST_NAME = IMPORT_USERS.REP_FIRST_NAME, EBA_USERS.REP_LAST_NAME = IMPORT_USERS.REP_LAST_NAME, EBA_USERS.REP_DISABLED = IMPORT_USERS.REP_DISABLED WHERE EBA_USERS.REP_ID = IMPORT_USERS.REP_ID should work (as I understand it ;-) but I get the error ERROR 1064: You have an error in your SQL syntax near ' IMPORT_USERS SET EBA_USERS.HUB_ID = IIMPORT_USERS.HUB_ID, EBA_USERS.REP_LOCATION_ID = IMPO' at line 1 I did note in the comments section at the bottom someone else with same/similar problem, but have been unable to find a thread in the mail archive. My apologies if this has been dealt with already, or a workaround suggested... MySQL 3.23.51-nt on a Win2K box MyODBC 3.51 Thanks Jeff Creed Throbware (0417) 797 592 http://www.throbware.com.au - 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
Re: UPDATE syntax help
At 9:45 +1000 3/13/03, JJ wrote: That explains it then D'OH Is there a workaround? Thanks :-) One way is to use a script that issues a join to figure out which records to update, then constructs the appropriate UPDATE statements. - Original Message - From: Paul DuBois [EMAIL PROTECTED] To: MySQL [EMAIL PROTECTED]; MySQL [EMAIL PROTECTED] Sent: Thursday, March 13, 2003 9:02 AM Subject: Re: UPDATE syntax help At 8:33 +1000 3/13/03, MySQL wrote: Hi all, I am having a little UPDATE syntax issue. According to the manual According to the manual, this won't work until MySQL 4.x UPDATE EBA_USERS, IMPORT_USERS SET EBA_USERS.HUB_ID = IMPORT_USERS.HUB_ID, EBA_USERS.REP_LOCATION_ID = IMPORT_USERS.REP_LOCATION_ID, EBA_USERS.REP_FIRST_NAME = IMPORT_USERS.REP_FIRST_NAME, EBA_USERS.REP_LAST_NAME = IMPORT_USERS.REP_LAST_NAME, EBA_USERS.REP_DISABLED = IMPORT_USERS.REP_DISABLED WHERE EBA_USERS.REP_ID = IMPORT_USERS.REP_ID should work (as I understand it ;-) but I get the error ERROR 1064: You have an error in your SQL syntax near ' IMPORT_USERS SET EBA_USERS.HUB_ID = IIMPORT_USERS.HUB_ID, EBA_USERS.REP_LOCATION_ID = IMPO' at line 1 I did note in the comments section at the bottom someone else with same/similar problem, but have been unable to find a thread in the mail archive. My apologies if this has been dealt with already, or a workaround suggested... MySQL 3.23.51-nt on a Win2K box MyODBC 3.51 Thanks Jeff Creed Throbware (0417) 797 592 http://www.throbware.com.au - 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
RE: UPDATE syntax help
Paul, You have to use the results of one select to generate lots of update statements. If you execute all these from your program, make sure you use a different database connection for the updates, if you're keeping a results set open. Alternatively, if it's a one off, generate a script file with your code (or directly from SQL if you're a martyr) and then run it. I've used both methods successfully, Andy -Original Message- From: JJ [mailto:[EMAIL PROTECTED] Sent: 12 March 2003 23:45 To: MySQL Cc: Paul DuBois Subject: Re: UPDATE syntax help That explains it then D'OH Is there a workaround? Thanks :-) - Original Message - From: Paul DuBois [EMAIL PROTECTED] To: MySQL [EMAIL PROTECTED]; MySQL [EMAIL PROTECTED] Sent: Thursday, March 13, 2003 9:02 AM Subject: Re: UPDATE syntax help At 8:33 +1000 3/13/03, MySQL wrote: Hi all, I am having a little UPDATE syntax issue. According to the manual According to the manual, this won't work until MySQL 4.x UPDATE EBA_USERS, IMPORT_USERS SET EBA_USERS.HUB_ID = IMPORT_USERS.HUB_ID, EBA_USERS.REP_LOCATION_ID = IMPORT_USERS.REP_LOCATION_ID, EBA_USERS.REP_FIRST_NAME = IMPORT_USERS.REP_FIRST_NAME, EBA_USERS.REP_LAST_NAME = IMPORT_USERS.REP_LAST_NAME, EBA_USERS.REP_DISABLED = IMPORT_USERS.REP_DISABLED WHERE EBA_USERS.REP_ID = IMPORT_USERS.REP_ID should work (as I understand it ;-) but I get the error ERROR 1064: You have an error in your SQL syntax near ' IMPORT_USERS SET EBA_USERS.HUB_ID = IIMPORT_USERS.HUB_ID, EBA_USERS.REP_LOCATION_ID = IMPO' at line 1 I did note in the comments section at the bottom someone else with same/similar problem, but have been unable to find a thread in the mail archive. My apologies if this has been dealt with already, or a workaround suggested... MySQL 3.23.51-nt on a Win2K box MyODBC 3.51 Thanks Jeff Creed Throbware (0417) 797 592 http://www.throbware.com.au - 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
Re: UPDATE syntax help
Thanks... Currently I am doing exactly that. One data set open creating another. I was trying to speed the process up, although I am only dealing with 20,000 records so shouldn't complain. Thanks all for the suggestions... I look forward to 4.x JJ - Original Message - From: Andy Eastham [EMAIL PROTECTED] To: [EMAIL PROTECTED] Mysql. Com [EMAIL PROTECTED] Sent: Thursday, March 13, 2003 10:14 AM Subject: RE: UPDATE syntax help Paul, You have to use the results of one select to generate lots of update statements. If you execute all these from your program, make sure you use a different database connection for the updates, if you're keeping a results set open. Alternatively, if it's a one off, generate a script file with your code (or directly from SQL if you're a martyr) and then run it. I've used both methods successfully, Andy -Original Message- From: JJ [mailto:[EMAIL PROTECTED] Sent: 12 March 2003 23:45 To: MySQL Cc: Paul DuBois Subject: Re: UPDATE syntax help That explains it then D'OH Is there a workaround? Thanks :-) - Original Message - From: Paul DuBois [EMAIL PROTECTED] To: MySQL [EMAIL PROTECTED]; MySQL [EMAIL PROTECTED] Sent: Thursday, March 13, 2003 9:02 AM Subject: Re: UPDATE syntax help At 8:33 +1000 3/13/03, MySQL wrote: Hi all, I am having a little UPDATE syntax issue. According to the manual According to the manual, this won't work until MySQL 4.x UPDATE EBA_USERS, IMPORT_USERS SET EBA_USERS.HUB_ID = IMPORT_USERS.HUB_ID, EBA_USERS.REP_LOCATION_ID = IMPORT_USERS.REP_LOCATION_ID, EBA_USERS.REP_FIRST_NAME = IMPORT_USERS.REP_FIRST_NAME, EBA_USERS.REP_LAST_NAME = IMPORT_USERS.REP_LAST_NAME, EBA_USERS.REP_DISABLED = IMPORT_USERS.REP_DISABLED WHERE EBA_USERS.REP_ID = IMPORT_USERS.REP_ID should work (as I understand it ;-) but I get the error ERROR 1064: You have an error in your SQL syntax near ' IMPORT_USERS SET EBA_USERS.HUB_ID = IIMPORT_USERS.HUB_ID, EBA_USERS.REP_LOCATION_ID = IMPO' at line 1 I did note in the comments section at the bottom someone else with same/similar problem, but have been unable to find a thread in the mail archive. My apologies if this has been dealt with already, or a workaround suggested... MySQL 3.23.51-nt on a Win2K box MyODBC 3.51 Thanks Jeff Creed Throbware (0417) 797 592 http://www.throbware.com.au - 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 - 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
Query syntax help?
OK, I am having a bit of trouble designing a MySQL query that returns what I want. Here is the query as I have it thus far: SELECT DISTINCT regformfields.name AS thename, regformfields.label AS thelabel, regfields.name AS fieldsname FROM regformfields INNER JOIN regfields ON (regformfields.name = regfields.Name) WHERE regformfields.label != '' ORDER BY regfields.saveorder; In this particular query, there can be multiple occurrences of thename(can be filtered by DISTINCT), therefore multiple occurrences of thelabel (which can't be filtered by DISTINCT, as it is always different for the same thename), but fieldsname is always unique. I don't care which thename or which thelabel is returned, but I only want one (these two tables, together with some others, construct a schema for yet others...), i.e thename = 'email' may be returned twice in this result set, but I only want it to appear once. DISTINCT, as it is used here, does not return what I want, as thelabel will rarely, if ever, be distinct. The ideal query would force the DISTINCT to be related ONLY to thename, and return whatever thelabel it happens to grab, based on however it is indexing, which would be the first saveorder it stumbles upon. Any help would be appreciated! TIA, --Scott Brown - 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
Re: Query syntax help?
Try: SELECT FF.name AS thename, MAX(FF.label) AS thelabel, F.name AS fieldsname FROM regformfields as FF INNER JOIN regfields as F ON (FF.name = F.Name) WHERE FF.label != '' GROUP BY FF.name, F.name I don't think you can include the ORDER BY F.saveorder (another column) in this case, unless you include it (F.saveorder) in the SELECT and GROUP BY list. HTH, Tore. - Original Message - From: Scott Brown [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, February 22, 2003 1:57 PM Subject: Query syntax help? OK, I am having a bit of trouble designing a MySQL query that returns what I want. Here is the query as I have it thus far: SELECT DISTINCT regformfields.name AS thename, regformfields.label AS thelabel, regfields.name AS fieldsname FROM regformfields INNER JOIN regfields ON (regformfields.name = regfields.Name) WHERE regformfields.label != '' ORDER BY regfields.saveorder; In this particular query, there can be multiple occurrences of thename(can be filtered by DISTINCT), therefore multiple occurrences of thelabel (which can't be filtered by DISTINCT, as it is always different for the same thename), but fieldsname is always unique. I don't care which thename or which thelabel is returned, but I only want one (these two tables, together with some others, construct a schema for yet others...), i.e thename = 'email' may be returned twice in this result set, but I only want it to appear once. DISTINCT, as it is used here, does not return what I want, as thelabel will rarely, if ever, be distinct. The ideal query would force the DISTINCT to be related ONLY to thename, and return whatever thelabel it happens to grab, based on however it is indexing, which would be the first saveorder it stumbles upon. Any help would be appreciated! TIA, --Scott Brown - 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
Re: SQL Syntax Help
On Fri, Jan 31, 2003 at 02:07:11PM -, Kevin Smith wrote: Hi All, Can anyone help me get this query working in MySQL, this was created using Access, but it doesn't port well for MySQL syntax: SELECT b.id, p.part_code, p.product_type, p.description, po1.options, b.price, b.quantity, b.price*b.quantity AS total FROM (products AS p LEFT JOIN product_options AS po ON p.prod_id = po.prod_id) RIGHT JOIN (product_options AS po1 RIGHT JOIN (basket_header AS bh INNER JOIN basket AS b ON bh.basket_id = b.basket_id) ON po1.po_id = b.op_id) ON p.prod_id = b.prod_id GROUP BY b.id, p.part_code, p.product_type, p.description, po1.options, b.price, b.quantity, b.price*b.quantity, bh.basket_id, p.options HAVING (((bh.basket_id)=4)); Try FROM (((basket_header AS bh INNER JOIN basket AS b ON bh.basket_id = b.basket_id) LEFT JOIN product_options AS po1 ON po1.po_id = b.op_id) LEFT JOIN products AS p ON p.prod_id = b.prod_id) LEFT JOIN product_options AS po ON p.prod_id = po.prod_id MySQL tends to be more finicky than Jet about how you group things. I haven't tried this, but I think it will avoid confusing the MySQL optimizer. Bob Hall - 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
SQL Syntax Help
Hi All, Can anyone help me get this query working in MySQL, this was created using Access, but it doesn't port well for MySQL syntax: SELECT b.id, p.part_code, p.product_type, p.description, po1.options, b.price, b.quantity, b.price*b.quantity AS total FROM (products AS p LEFT JOIN product_options AS po ON p.prod_id = po.prod_id) RIGHT JOIN (product_options AS po1 RIGHT JOIN (basket_header AS bh INNER JOIN basket AS b ON bh.basket_id = b.basket_id) ON po1.po_id = b.op_id) ON p.prod_id = b.prod_id GROUP BY b.id, p.part_code, p.product_type, p.description, po1.options, b.price, b.quantity, b.price*b.quantity, bh.basket_id, p.options HAVING (((bh.basket_id)=4)); Here is the error message MySQL reports: You have an error in your SQL syntax near '(product_options AS po1 RIGHT JOIN (basket_header AS bh INNER JOIN basket AS b O' at line 1 Any ideas to the correct syntax? Thanks, Kevin - 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
Re: Thanks and SQL Syntax help
I don't know if i understood you very well, but here's a try.. mysql select * from Classes; ++-+ | ID | Name| ++-+ | 1 | XO-312 | | 2 | PA-211a | | 3 | XUL-001 | ++-+ 3 rows in set (0.00 sec) mysql select * from Workshops order by ClassID,Date; ++-++ | ID | ClassID | Date | ++-++ | 1 | 1 | 2002-05-15 | | 8 | 1 | 2002-09-22 | | 7 | 1 | 2002-10-29 | | 2 | 1 | 2003-02-20 | | 3 | 2 | 2002-05-15 | | 9 | 2 | 2003-01-01 | | 4 | 2 | 2003-02-17 | | 5 | 3 | 2002-05-15 | | 10 | 3 | 2002-12-16 | | 6 | 3 | 2003-01-01 | ++-++ 10 rows in set (0.00 sec) mysql select ClassID, MIN(Date) min, MAX(Date) max, Classes.Name - FROM Workshops LEFT JOIN Classes ON (ClassID=Classes.ID) - GROUP BY ClassID HAVING now() BETWEEN min and max; +-+++-+ | ClassID | min| max| Name| +-+++-+ | 1 | 2002-05-15 | 2003-02-20 | XO-312 | | 2 | 2002-05-15 | 2003-02-17 | PA-211a | +-+++-+ 2 rows in set (0.00 sec) Hope this helps... On Sat, 2003-01-11 at 16:25, Steve Lefevre wrote: First of, thanks to all who replied to my questions earlier! Now I have another problem. I have a table of Classes and Workshops. Each Class has a number of workshops. Each workshop has a date. I have a query that gives me the date range of a class - the min and max dates of its workshops. select ClassID, MIN(Date), MAX(Date), Classes.Name FROM Workshops LEFT JOIN Classes ON ClassID=Classes.ID GROUP BY ClassID; gives me: +-+++-+ | ClassID | MIN(Date) | MAX(Date) | Name| +-+++-+ | 56 | 2002-05-15 | 2002-12-29 | XO-312 | | 408 | 2002-05-15 | 2002-05-17 | PA-211a | | 600 | 2002-05-15 | 2002-05-16 | XUL-001 | +-+++-+ 3 rows in set (0.00 sec) Now I want to get *active* classes - WHERE Now() Between MIN(Date) and Max(Date) -- but I can't figure out where to put the friggin clause. I get errors all over the place. Can I use the between function with a group by function? select ClassID, MIN(Date), MAX(Date), Classes.Name FROM Workshops LEFT JOIN Classes ON ClassID=Classes.ID WHERE Now() BETWEEN MIN(Date) and MAX(Date) GROUP BY ClassID; What am I doing wrong? - 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 -- Diana Soares - 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
Thanks and SQL Syntax help
First of, thanks to all who replied to my questions earlier! Now I have another problem. I have a table of Classes and Workshops. Each Class has a number of workshops. Each workshop has a date. I have a query that gives me the date range of a class - the min and max dates of its workshops. select ClassID, MIN(Date), MAX(Date), Classes.Name FROM Workshops LEFT JOIN Classes ON ClassID=Classes.ID GROUP BY ClassID; gives me: +-+++-+ | ClassID | MIN(Date) | MAX(Date) | Name| +-+++-+ | 56 | 2002-05-15 | 2002-12-29 | XO-312 | | 408 | 2002-05-15 | 2002-05-17 | PA-211a | | 600 | 2002-05-15 | 2002-05-16 | XUL-001 | +-+++-+ 3 rows in set (0.00 sec) Now I want to get *active* classes - WHERE Now() Between MIN(Date) and Max(Date) -- but I can't figure out where to put the friggin clause. I get errors all over the place. Can I use the between function with a group by function? select ClassID, MIN(Date), MAX(Date), Classes.Name FROM Workshops LEFT JOIN Classes ON ClassID=Classes.ID WHERE Now() BETWEEN MIN(Date) and MAX(Date) GROUP BY ClassID; What am I doing wrong? - 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
Re: syntax help , sql,query
* Dragos Madalin Popa I am trying to run this update statement, but I got an error syntax...near offsetCould You please point out what is wrong? Update TIMEZONE_INFO set ABBREVIATION = 'GMT' || to_char(OFFSET) where ABBREVIATION is null; Use the CONCAT function: URL: http://www.mysql.com/doc/en/String_functions.html -- Roger - 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
re: syntax help , sql,query
Dragos, Monday, October 07, 2002, 7:30:47 PM, you wrote: DMP I am trying to run this update statement, but I got an error syntax...near DMP offsetCould You please point out what is wrong? DMP Update TIMEZONE_INFO set ABBREVIATION = 'GMT' || to_char(OFFSET) where DMP ABBREVIATION is null; There is no function TO_CHAR() in MySQL. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - 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
Re: sql syntax help
You almost got it. Your syntax will be something like this: UPDATE Table SET address=REPLACE(address,'#','Number') WHERE column like%#% When I am trying to figure out the syntax for something, I always add a LIMIT 1 at the end so that only one record gets changed. On Saturday, October 5, 2002, at 12:45 AM, Scott Johnson wrote: I have a db with slightly over 614,000 records of names and addresses. In the address column, there are quite a few records like 123 any rd # 2 319 w. 1st st # B 4321 test blvd # 42 etc I want to replace all the number signs with the actual word 'number'. Is there a SQL command I can use for this or do I need the help of a scripting language (php or vb)? I was trying to construct something like update into table.column select where column like '%#%' replace with '%number%' but of course that is not going to work. I am a SQL newb btw. Any help appreciated. Thanks Scott - 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 mysql-unsubscribe- [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 - 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
re: sql syntax help
Scott, Saturday, October 05, 2002, 7:45:16 AM, you wrote: SJ I have a db with slightly over 614,000 records of names and addresses. In SJ the address column, there are quite a few records like SJ 123 any rd # 2 SJ 319 w. 1st st # B SJ 4321 test blvd # 42 SJ etc SJ I want to replace all the number signs with the actual word 'number'. SJ Is there a SQL command I can use for this or do I need the help of a SJ scripting language (php or vb)? SJ I was trying to construct something like update into table.column select SJ where column like '%#%' replace with '%number%' SJ but of course that is not going to work. I am a SQL newb btw. Take a look at string function REPLACE(): http://www.mysql.com/doc/en/String_functions.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - 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
syntax help , sql,query
sql,query Hi, I am trying to run this update statement, but I got an error syntax...near offsetCould You please point out what is wrong? Update TIMEZONE_INFO set ABBREVIATION = 'GMT' || to_char(OFFSET) where ABBREVIATION is null; Thank You, Best Regards, Madalin. - 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
sql syntax help
I have a db with slightly over 614,000 records of names and addresses. In the address column, there are quite a few records like 123 any rd # 2 319 w. 1st st # B 4321 test blvd # 42 etc I want to replace all the number signs with the actual word 'number'. Is there a SQL command I can use for this or do I need the help of a scripting language (php or vb)? I was trying to construct something like update into table.column select where column like '%#%' replace with '%number%' but of course that is not going to work. I am a SQL newb btw. Any help appreciated. Thanks Scott - 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
Re: Conditional join SQL syntax help?
Hi. On Mon 2002-07-08 at 22:54:09 -0700, [EMAIL PROTECTED] wrote: I'm trying to get the name of a booth or tradeshow depending on the customer_link_type (which is an ENUM) combined with the customer_link_table_id which tells me the index/id of the correct table to look in. I've tried this SQL command, but it doesn't work right. I get multiple permutations still. Is this even possible to do with mySQL? Or do I have to make two queries (one for 'booth' and one for 'tradeshow' and store them in a PHP array or something) In MySQL 4.x you can use UNION to accomplish what you want, in earlier versions you have to do two queries and do some application-side work or if you want the ORDER BY done by the database, you have to use a TEMPORARY TABLE. I think the problem could be avoided if the database design would be normalized further. Considering your select, it could be that a intermediate table, containing reference to type, name and id (and maybe others) would help and the both and tradeshow tables would only contain the information unique to them. Greetings, Benjamin. SELECT customer_id, customer_name, customer_link_type, customer_link_table_id, tradeshow_name, booth_name FROM Customer_Table, TradeShow_Table, Booth_Table WHERE ((customer_link_table_id = tradeshow_id AND customer_link_type = 'tradeshow') OR (customer_link_table_id = booth_id AND customer_link_type = 'booth')) AND customer_mail_list = 1 ORDER BY customer_date DESC, customer_link_type Here are the three relevant tables and fields (some removed for space saving): CREATE TABLE Customer_Table ( customer_id INT(10) UNSIGNED DEFAULT '0' NOT NULL PRIMARY KEY auto_increment, customer_link_table_id INT(10) UNSIGNED DEFAULT '0' NOT NULL, customer_link_type enum(booth, tradeshow) NOT NULL, customer_name varchar(30), customer_mail_list tinyint(1) UNSIGNED DEFAULT '0', ); CREATE TABLE TradeShow_Table ( tradeshow_id int(10) unsigned NOT NULL auto_increment, tradeshow_name varchar(100) NOT NULL default '' ) CREATE TABLE Booth_Table ( booth_id int(10) UNSIGNED DEFAULT '0' NOT NULL PRIMARY KEY auto_increment, booth_name varchar(30) NOT NULL ); -- [EMAIL PROTECTED] - 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
Conditional join SQL syntax help?
I'm trying to get the name of a booth or tradeshow depending on the customer_link_type (which is an ENUM) combined with the customer_link_table_id which tells me the index/id of the correct table to look in. I've tried this SQL command, but it doesn't work right. I get multiple permutations still. Is this even possible to do with mySQL? Or do I have to make two queries (one for 'booth' and one for 'tradeshow' and store them in a PHP array or something) SELECT customer_id, customer_name, customer_link_type, customer_link_table_id, tradeshow_name, booth_name FROM Customer_Table, TradeShow_Table, Booth_Table WHERE ((customer_link_table_id = tradeshow_id AND customer_link_type = 'tradeshow') OR (customer_link_table_id = booth_id AND customer_link_type = 'booth')) AND customer_mail_list = 1 ORDER BY customer_date DESC, customer_link_type Here are the three relevant tables and fields (some removed for space saving): CREATE TABLE Customer_Table ( customer_id INT(10) UNSIGNED DEFAULT '0' NOT NULL PRIMARY KEY auto_increment, customer_link_table_id INT(10) UNSIGNED DEFAULT '0' NOT NULL, customer_link_type enum(booth, tradeshow) NOT NULL, customer_name varchar(30), customer_mail_list tinyint(1) UNSIGNED DEFAULT '0', ); CREATE TABLE TradeShow_Table ( tradeshow_id int(10) unsigned NOT NULL auto_increment, tradeshow_name varchar(100) NOT NULL default '' ) CREATE TABLE Booth_Table ( booth_id int(10) UNSIGNED DEFAULT '0' NOT NULL PRIMARY KEY auto_increment, booth_name varchar(30) NOT NULL ); - 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
Query syntax help
I'm trying to figure out a query that will tell me the total number of people in our house email file that physically opted out in the last week. I'm a bit of a mysql newbie as you can tell... This is the general concept, though it doesn't seem to work: select count(id) from contact # my data table where optin='no'# shows they are an opt-out AND bad_email IS NULL # is ticked if it was a bounceback opt-out AND email IS NOT NULL # show only for records that have emails AND date = 2002-03-17; # show data only since last sunday I appear to be getting hung up on the date part. I'm not sure if I can use that kind of operator on a date with that format. Any help is appreciated! Thanks, Rory - 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
RE: Query syntax help
AND date = 2002-03-17; -Original Message- From: rory oconnor [mailto:[EMAIL PROTECTED]] Sent: Thursday, March 21, 2002 10:49 AM To: mysql list (choose midget) Subject: Query syntax help I'm trying to figure out a query that will tell me the total number of people in our house email file that physically opted out in the last week. I'm a bit of a mysql newbie as you can tell... This is the general concept, though it doesn't seem to work: select count(id) from contact # my data table where optin='no'# shows they are an opt-out AND bad_email IS NULL # is ticked if it was a bounceback opt-out AND email IS NOT NULL # show only for records that have emails AND date = 2002-03-17; # show data only since last sunday I appear to be getting hung up on the date part. I'm not sure if I can use that kind of operator on a date with that format. Any help is appreciated! Thanks, Rory - 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
RE: Query syntax help
I'm trying to figure out a query that will tell me the total number of people in our house email file that physically opted out in the last week. I'm a bit of a mysql newbie as you can tell... This is the general concept, though it doesn't seem to work: select count(id) from contact # my data table where optin='no' # shows they are an opt-out AND bad_email IS NULL # is ticked if it was a bounceback opt-out AND email IS NOT NULL # show only for records that have emails AND date = 2002-03-17; # show data only since last sunday I appear to be getting hung up on the date part. I'm not sure if I can use that kind of operator on a date with that format. Any help is appreciated! You need to put the date constant in quotes: ... AND date = '2002-03-17'; -- Roger - 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
RE: Query syntax help
What you had looks fine except the date...change what you had to: AND date = '2002-03-17'; # date needs quotes around it Should work. -Original Message- From: rory oconnor [mailto:[EMAIL PROTECTED]] Sent: Thursday, March 21, 2002 8:49 AM To: mysql list (choose midget) Subject: Query syntax help I'm trying to figure out a query that will tell me the total number of people in our house email file that physically opted out in the last week. I'm a bit of a mysql newbie as you can tell... This is the general concept, though it doesn't seem to work: select count(id) from contact # my data table where optin='no'# shows they are an opt-out AND bad_email IS NULL # is ticked if it was a bounceback opt-out AND email IS NOT NULL # show only for records that have emails AND date = 2002-03-17; # show data only since last sunday I appear to be getting hung up on the date part. I'm not sure if I can use that kind of operator on a date with that format. Any help is appreciated! Thanks, Rory - 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
Syntax Help
I've been trying to figure out where I might be going wrong with this SQL syntax: select format(sum(p.badcheck)+ (count(p.badcheck)*-15)) as Badcheck from payout p,payhistory ph where ph.paydate between '2001-09-20' and '2001-09-30' and (p.payid = ph.payid and p.badcheck != '0.00') MySQL said: You have an error in your SQL syntax near ') as Badcheck from payout p,payhistory ph where ph.paydate between '2001-09-2' at line 1 If I remove the format() from the query, it works prefectly! I'm trying to format the result. Any help, appreciated! ;) mysql database. Mike(mickalo)Blezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://www.thunder-rain.com Tel: 1(225)686-2002 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= - 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
Syntax Help - CANCEL
Cancel this, I spotted the problem, forgot the format(..,2); This query works as expected: select format(sum(p.badcheck)+ (count(p.badcheck)*-15),2) as Badcheck from payout p,payhistory ph where ph.paydate between '2001-09-20' and '2001-09-30' and (p.payid = ph.payid and p.badcheck != '0.00') I've been trying to figure out where I might be going wrong with this SQL syntax: select format(sum(p.badcheck)+ (count(p.badcheck)*-15)) as Badcheck from payout p,payhistory ph where ph.paydate between '2001-09-20' and '2001-09-30' and (p.payid = ph.payid and p.badcheck != '0.00') MySQL said: You have an error in your SQL syntax near ') as Badcheck from payout p,payhistory ph where ph.paydate between '2001-09-2' at line 1 If I remove the format() from the query, it works prefectly! I'm trying to format the result. Any help, appreciated! ;) mysql database. Mike(mickalo)Blezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://www.thunder-rain.com Tel: 1(225)686-2002 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= - 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
