I have two columns in my table, and I am trying to update the second column with the result of first col like this.   SET @pg = (SELECT ID FROM wp_posts WHERE post_name = 'y' AND post_status = 'x' LIMIT 1)   Now the above query works the (SELECT ...) as in it returns an ID from the query, so I am trying to use that ID to update another column like this.   UPDATE wp_posts SET post_type = 'foo' WHERE ID = @pg;    But for some reason the above does not work.   and I get the following error.   1 queries executed, 0 success, 1 errors, 0 warnings  Query: set @pg = (SELECT ID FROM wp_posts WHERE post_name = 'y' AND post_status = 'x' LIMIT 1) SELECT * ...  Error Code: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SELECT * FROM wp_posts WHERE id = @pg' at line 3  Execution Time : 0 sec Transfer Time  : 0 sec Total Time     : 0 sec   EDIT/UPDATE:  I am also trying to execute this staetment, which is simpler and does the same thing  UPDATE wp_posts SET post_type = 'x' WHERE ID = (SELECT ID FROM wp_posts WHERE post_name = 'y' AND post_status = 'z' LIMIT 1)   but for some reason, it doesn't work.
3320
I have a table story_category in my database with corrupt entries. The next query returns the corrupt entries:  SELECT *  FROM  story_category  WHERE category_id NOT IN (     SELECT DISTINCT category.id      FROM category INNER JOIN         story_category ON category_id=category.id);   I tried to delete them executing:  DELETE FROM story_category  WHERE category_id NOT IN (     SELECT DISTINCT category.id      FROM category        INNER JOIN story_category ON category_id=category.id);   But I get the next error:      #1093 - You can't specify target table 'story_category' for update in FROM clause    How can I overcome this?
17495
I have the following code:  $countQuery = "SELECT ARTICLE_NO FROM ? WHERE upper(ARTICLE_NAME) LIKE '% ? %'"; if ($numRecords = $con-&gt;prepare($countQuery)) {     $numRecords-&gt;bind_param("ss", $table, $brand);     $numRecords-&gt;execute();     $data = $con-&gt;query($countQuery) or die(print_r($con-&gt;error));     $rowcount = mysql_num_rows($data);     $rows = getRowsByArticleSearch($query, $table, $max);     $last = ceil($rowcount/$page_rows); }   Which should work fine. However I receive the error that :  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 upper(ARTICLE_NAME) LIKE '%?%'' at line 1  If I put  SELECT ARTICLE_NO FROM AUCTIONS WHERE upper(ARTICLE_NAME) LIKE '% o %';   The query works fine. $table is defined above, and query is received from GET, and both are correct valid values. Why is this failing?  edit: changing to:  $countQuery = "SELECT ARTICLE_NO FROM AUCTIONS1 WHERE upper(ARTICLE_NAME) LIKE '% ? %'";  if ($numRecords = $con-&gt;prepare($countQuery)) {      $numRecords-&gt;bind_param("s", $query);   results in the error:   Warning: mysqli_stmt::bind_param() [mysqli-stmt.bind-param]: Number of variables doesn't match number of parameters in prepared statement in C:\Program Files\EasyPHP 3.0\www\prog\get_records.php on line 38  Commands out of sync; you can't run this command now  where as   $countQuery = "SELECT ARTICLE_NO FROM AUCTIONS1 WHERE upper(ARTICLE_NAME) LIKE ?";  if ($numRecords = $con-&gt;prepare($countQuery)) {      $numRecords-&gt;bind_param("s", "%".$query."%");   results in  Fatal error: Cannot pass parameter 2 by reference in C:\Program Files\EasyPHP 3.0\www\prog\get_records.php on line 38  and lastly  $countQuery = "SELECT ARTICLE_NO FROM AUCTIONS1 WHERE upper(ARTICLE_NAME) LIKE ? ";  if ($numRecords = $con-&gt;prepare($countQuery)) {      $numRecords-&gt;bind_param("s", $query);   will give only:  Commands out of sync; you can't run this command now  Is it impossible to use a paramter for a LIKE statament?
50358
I have a Postgres database which contains details on clusters of servers, such as server status ('active', 'standby' etc). Active servers at any time may need to fail over to a standby, and I don't care which standby is used in particular.   I want a database query to change the status of a standby - JUST ONE - and return the server IP that is to be used. The pick can be arbitrary: since the status of the server changes with the query, it doesn't matter which standby is selected.  Is it possible to limit my query to just one update?  Here is what I have so far:  UPDATE server_info SET status = 'active'  WHERE status = 'standby' [[LIMIT 1???]]  RETURNING server_ip;   Postgres doesn't like this. What could I do differently?
15587
I have a query to insert a row into a table, which has a field called ID, which is populated using an AUTO_INCREMENT on the column. I need to get this value for the next bit of functionality, but when I run the following, it always returns 0 even though the actual value is not 0:  MySqlCommand comm = connect.CreateCommand(); comm.CommandText = insertInvoice; comm.CommandText += "\'" + invoiceDate.ToString("yyyy:MM:dd hh:mm:ss") + "\', " + bookFee + ", " + adminFee + ", " + totalFee + ", " + customerID +  ")"; int id = Convert.ToInt32(comm.ExecuteScalar());   According to my understanding, this should return the ID column, but it just returns 0 every time. Any ideas?  EDIT:  When I run:  "INSERT INTO INVOICE (INVOICE_DATE, BOOK_FEE, ADMIN_FEE, TOTAL_FEE, CUSTOMER_ID) VALUES ('2009:01:01 10:21:12', 50, 7, 57, 2134);last_insert_id();"   I get:  {"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 'last_insert_id()' at line 1"}
103971
CREATE TABLE plan    (       id                VARCHAR(30) NOT NULL,       name              VARCHAR(20),       amount            INT,       interval          INT,       currency          CHAR(3),       object            VARCHAR(20),       livemode          BOOLEAN,       interval_count    INT,       trial_period_days INT,       created           TIMESTAMP,       updated           TIMESTAMP,       deleted           TIMESTAMP,       PRIMARY KEY (id)    );    Produces   '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 'interval          INT,       currency          CHAR(3),       object            ' at line 6'   Doesn't work. However if the 'l' at the end of 'interval' is removed. It works.  CREATE TABLE plan    (       id                VARCHAR(30) NOT NULL,       name              VARCHAR(20),       amount            INT,       interva           INT,       currency          CHAR(3),       object            VARCHAR(20),       livemode          BOOLEAN,       interval_count    INT,       trial_period_days INT,       created           TIMESTAMP,       updated           TIMESTAMP,       deleted           TIMESTAMP,       PRIMARY KEY (id)    );    I don't understand if this is a problem with attempting to use 'interval' and 'interval_count'. What's going on here.
21375
I want to write a select statement that with some fixed columns and dynamic columns. here Dynamic Columns names are the rows in another table.  I have tried this but getting error. Please tell me where my error is  set @sql=(Select subject from tbl_subjects where C_Id=22); SELECT      ta.rollno,     ta.StdNm,     tc.C_Name,     @sql FROM     tbl_cmarks tm,     tbl_admission ta,     tbl_classes tc WHERE     tm.Cid = tc.C_Id         AND ta.rollno = tm.rollno         AND tm.Cid = 22 and tm.Examid=9    Mysql showing:     Error Code: 1242. Subquery returns more than 1 row   Could any one tell me how to do this in mysql?
82000
I am trying to create the following trigger:      CREATE TRIGGER update_customers AFTER INSERT ON wp_usermeta     FOR EACH ROW      BEGIN             if new.wp_metakey = first_name then insert into test_customer(first_name) values (new.wp_meta_value);     END   I keep getting a 1064 error that says, "You have an error in your SQL syntax; check the manual that corresopons to your MySQL server version for the right syntaxt to use near " at line 5"  What am I doing wrong?
28286
I used osm2pgsql to import data into my PostgreSQL database and it put gave me the following 4 tables, planet_osm_point, planet_osm_line, planet_osm_polygon and planet_osm_roads.   I have looked at the schema for osm2pgsql and inside the planet_osm_point table there is a column 'way' of type point. I tried to query it as an array like it says in the documentation here ():     It is possible to access the two component numbers of a point as   though the point were an array with indexes 0 and 1. For example, if   t.p is a point column then SELECT p[0] FROM t retrieves the X   coordinate and UPDATE t SET p[1] = ... changes the Y coordinate. In   the same way, a value of type box or lseg can be treated as an array   of two point values.   Anyone know the correct query the planet_osm tables from osm2pgsql based on long/lat? For example if I wanted to retrieve the information for Big Ben (London) I would query along the lines of:      SELECT * FROM planet_osm_point      WHERE ST_Y(ST_Transform(way, 4326)) = '-0.12'        AND ST_X(ST_Transform(way, 4326)) = '51.5';   There is no error message, it comes out with the table names, but no data is output either. (Its an empty table). What am I doing wrong here?  Perhaps it is too precise, i.e. there are no coordinates that match it exactly? Perhaps I need to try being more general, say 51 &lt; long &lt; 52 and -0.5 &lt; lat &lt; 0. Although this would throw back multiple results so would not be quite as accurate.
119372
I am very, very new to MYSQL.I tried to create a table named "option". My SQL Query is :  create table option(   id int not null primary key auto_increment,  choice varchar(30)  )  While executing this query it shows the following error  Error Code : 1064 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 'option( id int not null primary key auto_increment, choice varchar(30) )' at line 1 (0 ms taken)  If I try with the table name as "choice" it is working.  can we have the table name as "option" in mysql?   thanks
8186
is there an alternative for mysql_insert_id() php function for PostgreSQL? Most of the frameworks are solving the problem partially by finding the current value of the sequence used in the ID. However, there are times that the primary key is not a serial column....
3263
It's probably more of a PHP question, but I would like to know if there are any differences in using  global $post; echo $post-&gt;ID;   when compared to  echo $GLOBALS['post']-&gt;ID;   to access the ID of a post in WordPress.  This  on SO suggests that the first method is quicker to process, while the second method is quicker to write.  I also noticed that the first method is often mentioned in the Codex while the second method is mentioned on  page.  Is this only a matter of preference? Or does it come to performance and security too?  Thanks
65216
I am dissecting some code and came across this,   $sql = 'SELECT page.*, author.name AS author, updator.name AS updator '      . 'FROM '.TABLE_PREFIX.'page AS page '      . 'LEFT JOIN '.TABLE_PREFIX.'user AS author ON author.id = page.created_by_id '      . 'LEFT JOIN '.TABLE_PREFIX.'user AS updator ON updator.id = page.updated_by_id '      . 'WHERE slug = ? AND parent_id = ? AND (status_id='.Page::STATUS_REVIEWED.' OR status_id='.Page::STATUS_PUBLISHED.' OR status_id='.Page::STATUS_HIDDEN.')';   I am wondering what the "?" does in the WHERE statement.  Is it some sort of parameter holder?
24372
I have a function that stores the "like" status for a post as post meta. I want to associate that "like" with the user that liked it, so I setup a custom field called "like_status_{user_id}" (where {user_id} is the id of the currently logged in user) which I store as a 0 or 1. So for a post with several "likes" there would be several meta values in the db that are setup like this:  'meta_key' = 'like_status_0' 'meta_value' = 1 'meta_key' = 'like_status_2' 'meta_value' = 1 'meta_key' = 'like_status_34' 'meta_value' = 1   ....and so on.  There are potentially thousands of likes on a specific post. How would I run a query that showed if someone else also liked that post?  I was thinking something like this:  $query = new WP_Query(array(     'meta_key' =&gt; 'like_status_{user_id}',     'meta_value' =&gt; 1, ));   I'm trying to push a notification to everyone who has liked a post when someone else likes that post...something like, "Hey, someone else liked that post you liked. You should go check it out!" But I need a way to find out if anyone else has liked that post and if so, who they would be so I could notify them.  If it's not possible, could you suggest a better way of storing this data as post_meta while still keeping the efficiency of quickly updating a single user's like status on a post?
21299
I would like to add an order column wich will be position of my record. Positions are connected with name. If the name is S1 - order should be 1, for S2 - order should equal 2 etc. How can I do this?  Real state:   id       name     position -------  -------  ------- 1        S4       null 2        S2       null 3        S3       null 4        S1       null     Expected:   id       name     position -------  -------  ------- 1        S4       4 2        S2       2 3        S3       3 4        S1       1   I tried with update:  WITH x AS (   SELECT       id,       name,       ROW_NUMBER() OVER (ORDER BY name) position   FROM       stage ) UPDATE     stage SET     position = x.position FROM     x WHERE     x.id = stage.id;   Stage is the name of the table.
108487
I have the code below (I've included what I believe are all relevant sections):  private String readCommand = "SELECT LEVEL FROM USERS WHERE VAL_1 = ? AND VAL_@ = ?;"; public bool read(string id) {     level = -1;     MySqlCommand m = new MySqlCommand(readCommand);     m.Parameters.Add(new MySqlParameter("", val1));     m.Parameters.Add(new MySqlParameter("", val2));     MySqlDataReader r = m.ExecuteReader();     if (r.HasRows)         level = Convert.ToInt32(r.GetValue(0).ToString());     r.Close();     return true; }   When I run this, I get an IndexOutOfBoundsException on adding the first parameter. What have I done wrong?
46559
One of my columns is called from. I can't change the name because I didn't make it. Am I allowed to do something like SELECT from FROM TableName or is there a special syntax to avoid the SQL Server being confused?
57737
I have PostGIS table with two geometry columns, both defined with SRID 4326. I can insert into the table without problem, using the following INSERT statement (where lng and lat are values passed in programmatically):  INSERT INTO pad_meta (     uuid, created, updated, name, origin, radius, area, expiry, creator ) VALUES (     $1, now(), now(), $2, ST_GeomFromText('POINT(lng, lat)', 4326), $3,      ST_Buffer(ST_GeomFromText('POINT(lng, lat)', 4326), $4), $5, $6 )   But when i query for an intersection using ST_Intersects, depending on the value of the point I get ERROR: Operation on mixed SRID geometries.   For example, this query works:  SELECT * FROM pad_meta where ST_Intersects(     'POINT(-122.334172173172 46.602634395263560)'::geometry, area::geometry  ) ORDER BY created DESC;   And this errors out:  SELECT * FROM pad_meta where ST_Intersects(     'POINT(-122.334172173172 47.602634395263560)'::geometry, area::geometry  ) ORDER BY created DESC;   Note, they are identical queries except the value of the longitude. I have experimented with different values, but not identified a clear transition point between the queries that work and don't.   I think I am fundamentally misunderstanding something. For the moment, I have solved/corrected/worked around the problem by re-formatting the query to use ST_GeomFromText and explicitly specifying the SRID:  SELECT * FROM pad_meta where ST_Intersects(     ST_GeomFromText('POINT(-122.334172173172 47.602634395263560)', 4326), area ) ORDER BY created DESC;   But I honestly don't really understand what the difference is, or if this truly "the" solution.    Why am I getting an error only for specific values, and what is the proper way to format this query?  Here is my table definition for reference:  CREATE TABLE IF NOT EXISTS pad_meta (   uuid CHAR(32),   created TIMESTAMP,   updated TIMESTAMP,   name VARCHAR(128),   origin GEOMETRY(Point, 4326),   radius INTEGER,   area GEOMETRY(Polygon, 4326),   expiry TIMESTAMP,   creator CHAR(32),   PRIMARY KEY (uuid) );   I have also verified that there is only one type of SRID in the geometry_columns:   SELECT f_table_name, f_geometry_column, srid FROM geometry_columns; f_table_name | f_geometry_column | srid --------------+-------------------+------  pad_meta     | origin            | 4326  pad_meta     | area              | 4326   I have also seen , but since I am already explicitly defining my geometry SRIDs when inserting into the table, it seems like that is not what is happening.