embed a query in a query? 1

[edpatterson]

I have these tables, sorry, old joke.

Is something like the following possible:

[tt]
update table requests set listid = {output of "select listid from lists where listname = 'blacklisted'") and requestid = 89;
[/tt]
or do I have to make 2 separate queries?

Ed

 

[AdaHacker]

Yes, you can do exactly that (though I think you wanted a WHERE instead of an AND in there):

update table requests set listid = (select listid from lists where listname = 'blacklisted') WHERE requestid = 89;

I'm assuming here that the sub-select will only ever return a single row. You can't assign a multi-row subquery to a single column like this, for obvious reasons.

 

[edpatterson]

Thanks, perhaps where != and is why it failed when I tried it. I swear the mysql console has the most obnoxious beep in the world.

Ed

 

[ingresman]

sure does type -b on the command line to stop it
Have a look at the downloads on mysql and get the mysql query browser, much easier to use than the command line tool (and it's free)

 

[edpatterson]

Thanks, I'll look at the browser right after I get this working .
[tt]
mysql> update table requests set listid = {select listid from lists where listname = 'Blacklist'} where urlid=89;
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 'table requests set listid = {select listid from lists where listname = 'Blacklis' at line 1

[/tt]
results of the embedded query, "select listid from lists where listname = 'blacklist'"
[tt]
+--------+
| listid |
+--------+
| 1 |
+--------+
[/tt]
results of "select listid from requests where urlid = 89"
[tt]
+--------+
| listid |
+--------+
| 3 |
+--------+
[/tt]

Any ideas why the combined query does not work? I changed the {'s to ('s same results.

 

[ethorn10]

Try removing the word 'table' from your query...

update requests set listid = (select listid from lists where listname = 'blacklisted') WHERE requestid = 89;

 

[edpatterson]

Thanks, that did it!

I did take a quick look at the sql browser, so far it is more confusing then the command line

Ed