I want insert all rows of a table into another table, and I also want convert a nvarchar field into bigint, but when I use convert(bigint, col1) SQL Server shows an error: Error converting data type nvarchar to bigint How can I fix this problem? Answer You could try to use ISNUMERIC to determine those … Read more
Moving forwards, am I going to have a problem at some point if I store Tweet IDs from the Twitter API as an unsigned BIGINT in MySQL? Am I better off using a CHAR(25) or something? The Tweet IDs are only going to get bigger and bigger over time. Answer an unsigned bigint can hold … Read more
I’ve been trying to re-do this statement but to no avail. SELECT b.Program AS Program, SUM(CASE WHEN a.Gender LIKE ‘M%’ THEN 1 ELSE NULL END) AS Males, SUM(CASE WHEN a.Gender LIKE ‘F%’ THEN 1 ELSE NULL END) AS Females, SUM(CASE WHEN e.Activity LIKE ‘Arts’ THEN 1 ELSE NULL END) AS Arts, AVG(CASE WHEN a.Gender LIKE … Read more
MySQL 5.1.59 throws an error with this create table: CREATE TABLE IF NOT EXISTS `genre` ( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `abv` CHAR(3) CHARACTER SET ‘latin1’ COLLATE ‘latin1_bin’ NULL DEFAULT NULL , `name` VARCHAR(80) NOT NULL DEFAULT ” , `parent_id` INT NULL DEFAULT NULL , PRIMARY KEY (`id`) , INDEX `fk_genre_genre1` (`parent_id` ASC) , … Read more
This question has been covered for MSSQL here: How do I perform an insert and return inserted identity with Dapper? but this solution does not work with mysql. To cast the LAST_INSERT_ID() to integer with mysql you have to do this: SELECT CAST(LAST_INSERT_ID() AS UNSIGNED INTEGER); The stack trace is: Dapper.<QueryInternal>d__13`1.MoveNext() in sqlMapper.cs:607 System.Collections.Generic.List`1..ctor(IEnumerable`1 collection) … Read more
This question already has answers here: SQL query for a carriage return in a string and ultimately removing carriage return (10 answers) Closed 7 years ago. Facing a problem, it seems my data stored in SQL Server does not stored correctly, simply put, how to verify that a varchar data has carriage return and line … Read more
I’m using the following INSERT statement: INSERT INTO messages SET `to` = ‘”.$to.”‘, `from` = ‘”.$this->userid.”‘, `title` = ‘”.$title.”‘, `message` = ‘”.$message.”‘, `created` = NOW() However, it uses my server time (America/Montreal). I want time zone of Asia (Asia/Calcutta) Is this possible with the same query? Answer Better use the SQL format directly in your … Read more
The code that I have so far is below: I’m trying to find out what to put in place of my ?’s to find the start date of the quarter the date lies in. SELECT QUARTER(r.callDate) AS quar, YEAR(r.callDate) AS ryear, ???????? AS scoreDateStart, (SELECT DATE (DATE_SUB( DATE_ADD( CONCAT( YEAR( r.callDate ), ‘-01-01’), INTERVAL QUARTER(r.callDate) … Read more
I want to query the rows of SQL column for whether it contains any one of multiple values. For instance, return rows of a table where its column A contains any of the following words: (‘cow’,’farmer’,’milk’). Easy enough when you know what the words are, but I want to write a sproc where I can … Read more
here I am trying to remove any users which containt a ” in their email/username. def removeQuote(self, tbl,record): “”” Updates the record “”” statmt=”select id from %s WHERE `email` LIKE ‘%%\”%%'” % (tbl) self.cursor.execute(statmt) rows=list(self.cursor.fetchall()) for idx, val in enumerate(rows): id= val[0] delstatmt = “DELETE FROM `maillist_subscription` WHERE id = ‘%s'” % id print delstatmt … Read more
