Query inside a query in mysql

Question

update tbl_user set tbl_user.Zoneid='(Select tbl_timezone.Zoneid from tbl_timezone where tbl_timezone.timezone='[UTC - 4:30] Venezuelan Standard Time')' Where tbl_user.userid='1'

I get this error when i run the above query

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 '[UTC - 4:30] Venezuelan Standard Time')' Where tbl_user.userid='1'' at line 1

but when I execute

 update tbl_user set tbl_user.Zoneid='10' Where tbl_user.userid='1';

then it works fine

the result of following query is 10.

Select tbl_timezone.Zoneid from tbl_timezone where tbl_timezone.timezone='[UTC - 4:30] Venezuelan Standard Time'

whats wrong with my first query. why am I getting error

Molochdaa · Accepted Answer · 2011-11-15 10:02:00Z

1

You have to remove quotes :

update tbl_user set tbl_user.Zoneid=(Select tbl_timezone.Zoneid from tbl_timezone where tbl_timezone.timezone='[UTC - 4:30] Venezuelan Standard Time') Where tbl_user.userid='1'

answered Nov 15, 2011 at 10:02

Molochdaa

2,2281 gold badge18 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Álvaro González · Accepted Answer · 2011-11-15 10:08:36Z

1

In all SQL dialects, single quotes are used to delimiter strings:

SELECT this_is_a_column, 'This is a literal string'
FROM table

Additionally, there're escaping mechanism so you can write a string that contains single quotes

SELECT 'This string contains \'single\' quotes'

In your case:

You pretend to write code inside a string, thus is will never be executed as such.
You write unquoted single quotes inside a string, thus you get a syntax error.

answered Nov 15, 2011 at 10:08

Álvaro González

147k45 gold badges282 silver badges378 bronze badges

Collectives™ on Stack Overflow

Query inside a query in mysql

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related