Sub query?

trooper

Hi peeps

I'm trying to use a sub select (as far as i'm aware it is supported from v 4.1 of mysql)

Can someone tell me what is wrong with this

INSERT INTO 
messages (id,msg_date,read_state,message,message_id,sender_id,sender_type,recipient_id,recipient_type,read_date) 
VALUES ('',NOW(),'f','','13','1','X','XX','') 
WHERE 
recipient_id IN(SELECT guest_id FROM guest_lists WHERE wedding_id ='1'))

cheers

stolzyboy

are you getting any errors with a mysql_error()

trooper

grrrrr yeah the error is this

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 'WHERE recipient_id IN(SELECT guest_id FROM guest_lists WHERE we
[/php

helpful or what!!!

:D


GM

stolzyboy

i think your syntax is correct, however, you have ten fieldnames to insert into, but only 9 values for those ten fieldnames

is that your problem

trooper

well spotted - but alas no thats not the reason

Client categorically ensured me it was mysql 4.1 its not its 4.08b grrrr

damn those clients

Thanks

stolzyboy

aaahhhh crap, that always happens doesn't it

[off topic]
hows it coming with the ookbay
[/off topic]

gyoung

Try this:

INSERT INTO messages (id,msg_date,read_state,message,message_id,sender_id,sender_type,recipient_id,recipient_type,read_date)

SELECT '',DATE(),'f','','13','1','X', guest_id, 'XX',''
FROM guest_lists
GROUP BY guest_id
HAVING wedding_id ='1'

stolzyboy

read the thread, he can't use subselects, he doesn't have a new enough version of MySQL

trooper

grrrrrrr 🙁

how many times have i clicked resolve thread ????????

What gives?

stolzyboy

i tried it too, sometimes the board freaks out and the resolve doesn't work

trooper

don't s'pose the fact i'm using mozilla firebird has anything to do with it ?

LOL 😃

barand

Originally posted by stolzyboy
read the thread, he can't use subselects, he doesn't have a new enough version of MySQL

Where's the subquery - all I can see is an insert statement wich takes its values from another table - standard stuff.

stolzyboy

insert into blah blah where (select blah where blah......)

is a subquery/subselect

notice the select query inside the where clause of the insert query

barand

Are we talking about the same query? I was referring to your comment following this

Originally posted by gyoung
Try this:

INSERT INTO messages (id,msg_date,read_state,message,message_id,sender_id,sender_type,recipient_id,recipient_type,read_date)

SELECT '',DATE(),'f','','13','1','X', guest_id, 'XX',''
FROM guest_lists
GROUP BY guest_id
HAVING wedding_id ='1'

stolzyboy

didn't look too close at that one, but not sure what he was trying to accomplish with the one he tried either, either way, he has an insert and a select all bundled in one glob of code there, which likely wouldn't work either

barand

This works OK with mySQL 3.23.47

insert into table2 (lastname) select lastname from table1

trooper

barand i'm afraid its a tad more complex (what i need to do)

hence the subselect

thanks anyway

stolzyboy

he needs to insert something into something where something is in (another select statement)

your solution does nothing of the sort, it merely is doing an insert, then doing a select, they are not sub's of each other

this is a resolved thread, however the resolve feature isn't working

barand

I know, lack of subquery support in mySQL has been a pain. I as just taking issue with this statement

Originally posted by stolzyboy
he has an insert and a select all bundled in one glob of code there, which likely wouldn't work either

trooper

Well the answer is to upgrade to 4.1

Alas i'm not quiiiiiieeet there 4.08

Tomorrow (always tomorrow LOL)