CODEWITHSUNDEEP
mysqlselectinsertphpmyadminleft-join
Wiglaf
Wiglaf

Reputation: 1

MySQL query not working in phpmyadmin

I do not understand why my query is not working:

INSERT INTO `jos_acymailing_subscriber`(`sleep_breathe`)
  VALUE (`1`)

SELECT `subid` 
  FROM `jos_acymailing_listsub` 
  WHERE `listid` = `8`
   LEFT JOIN `jos_acymailing_subscriber`
  ON `jos_acymailing_listsub`.`subid` = `jos_acymailing_subscriber`.`sleep_breathe`

thx

I have a table of users that are subscribed to a mailing lists (the id of the list I am interested in is 8) . The subscription table is a simple intermediary table in a "many to many" relation

In the user table I want to add in a field an integer with 1 for the users that are subscribed to the list 8

So I need to first select the users that are in the intermediary table where the listid is 8 then add 1 in the field sleep_breathe that is in the user table.

The user table structure: name: jos_acymailing_subscriber

Field Type Null Default Comments subid int(10) No
email varchar(200) No
userid int(10) Yes NULL
name varchar(250) No
created int(10) Yes NULL
confirmed tinyint(4) No 0
enabled tinyint(4) No 1
accept tinyint(4) No 1
ip varchar(100) Yes NULL
html tinyint(4) No 1
key varchar(250) Yes NULL
ersmembershipnumber varchar(250) Yes NULL
first varchar(250) Yes NULL
title varchar(250) Yes NULL
erj varchar(250) Yes NULL
err varchar(250) Yes NULL
monograph varchar(250) Yes NULL
breathe varchar(250) Yes NULL
membershipcategory varchar(250) Yes NULL
mship_status varchar(250) Yes NULL
copd varchar(250) Yes NULL
sleep_breathe varchar(250) Yes NULL
pro varchar(250) Yes NULL

The intermediary table structure is: name: jos_acymailing_listsub

Field Type Null Default Comments listid smallint(11) No
subid int(11) No
subdate int(11) Yes NULL
unsubdate int(11) Yes NULL
status tinyint(4) No

Upvotes: 0

Views: 2264

Answers (4)

user330315
user330315

Reputation:

There are several errors in that statement.

  • If you want to insert values retrieved from a SELECT statement, the VALUES clause is not needed (in fact it's an error).
  • Numbers may not be put into quotes or even backticks.
  • The order of the FROM and LEFT JOIN keywords mixed up.

The query to insert values retrieved from a SELECT would be: 

INSERT INTO jos_acymailing_subscriber (sleep_breathe)
SELECT 1
FROM jos_acymailing_listsub
  LEFT JOIN jos_acymailing_subscriber
     ON jos_acymailing_listsub.subid = jos_acymailing_subscriber.sleep_breathe
WHERE listid = 8

One tip: forget the dreaded backticks alltogether so you won't get confused where and for what you need them.

Upvotes: 0

Richard Schneider
Richard Schneider

Reputation: 35464

(1) It should be VALUES not VALUE in the insert. (2) The left join is part of the from clause. Move the where clause to the end.

Upvotes: 0

josh.trow
josh.trow

Reputation: 4901

As others have said, that is two queries. Also, VALUES not VALUE. Thirdly, I'm not sure MySQL allows a WHERE before the JOIN. Your queries may have to be

INSERT INTO `jos_acymailing_subscriber`(`sleep_breathe`) VALUES (`1`);

SELECT `subid` 
FROM `jos_acymailing_listsub` 
LEFT JOIN `jos_acymailing_subscriber`
ON `jos_acymailing_listsub`.`subid` = `jos_acymailing_subscriber`.`sleep_breathe`
WHERE `listid` = `8;

Upvotes: 0

mystery
mystery

Reputation: 19523

I think the problem is you have put VALUE instead of VALUES.

It remains VALUES even if there is only one value to insert.

Upvotes: 3

Related Questions