sql

Write raw SQL for PostGIS

I'm trying to input some data into a PostgreSQL 8.4 database with a PostGIS template. I'm unable to UPDATE polygons: > UPDATE my_table SET coords = POINT(1, 1) UPDATE 0 1 > UPDATE my_table SET box = POLYGON(((1, 1), (2, 3), (3, 3), (1, 1))) ERROR: function polygon(record) does not exist > UPDATE my_table SET box = POLYGON((1, 1), (2,...

should i write an access front end or c# front end?

I have an Access database currently. There is a backend, and there are multiple users who use this database with their own front end. The size of the data is very small; however there are several forms that are constantly in use. I need to migrate the data to MySQL. Should I continue to use an Access front end or program my own c# front...

Help forming a SQL statement.

I have two tables: attractions and cities. Attractions contains a column called city, which is a reference to the id in the cities table. I want to form a MySQL statement that will show me which cities have the most attractions. I know I can do: SELECT COUNT(*) as `number` FROM `attractions` WHERE `city` = XX ...to get a count o...

Need help variable substitution in SQL

Hi, Is there a way to pass the value of combination variable to another variable? I guess better of with a simple code that I wrote... DECLARE @intFlag INT DECLARE @taxdepn1 varchar(1) = 'A' ,@taxdepn2 varchar(1) = 'B' ,@taxdepn3 varchar(1) = 'C' ,@taxdepn4 varchar(1) = null --'D' ` DECLARE @xxx varchar(1000); SET @i...

Performance of returning entire tables containing blog text as opposed to selecting specific columns

I think this is a pretty common scenario: I have a webpage that's returning links and excerpts to the 10 most recent blog entries. If I just queried the entire table, I could use my ORM mapped object, but I'd be downloading all the blog text. If I restricted the query to just the columns that I need, I'd be defining another class that'...

How to query two different fields from a table, and make it appear in the same row?

I have two tables. Say: This is table 1 +--------+-----------------------+ | stuff | foreing | foreing2 | +--------+-----------------------+ | bazzzz | 555 | 666 | +--------+-----------------------+ This is table 2 +-----------------------+ | id_table | values | +-----------------------+ | 555 | Foo | +-...

Polls database schema

Hello friends, I wanna create a poll system with possibilty to have many answers on my website but im thinking whats the best way. Im not so pro. Im thinking this schema: polls ------------------------------------- id | question | start_time | end_time poll_answers --------------------- id | poll_id | answer poll_votes -------------...

LIKE clause vs. IN clause--which is better?

Which one is better using condition in where statement or in join statement? Can anyone please help? ...

Master database DB STARTUP problem

I have a SQL Server 2008 database and I have a problem with this database that I don't understand. The steps that caused the problems are: I ran a SQL query to update a table called authors from another table called authorAff The authors table is 123,385,300 records and the authorsAff table is 139,036,077 The query took about 7 days e...

MYSQL - Using conditional order bys

Hi, I'm having problems using conditional ORDER BYs. I want to do something like this SELECT promotion_expires,created_at FROM `notes` ORDER BY CASE WHEN (promotion_expires > NOW()) THEN 'promotion_expires DESC,created_at DESC' ELSE 'created_at DESC' END; ie. the result should first have rows where promotion_expires > NOW() o...

In non-procedural languages, what specifies how things are to be done?

If you compare C vs SQL, this is the argument: In contrast to procedural languages such as C, which describe how things should be done, SQL is nonprocedural and describes what should be done. So, the how part for languages like SQL is specified by the language itself, is it? What if I want to change the way some query works. ...

deleting duplicate records

Hi all Is there some way by which i can delete duplicate record that exist in table.I just want one record and rest of them to be deleted by query .Is this can be done through a query . please suggest ...

How to use Case Statement

Using C#.Net and MYSQL I want to use a CASE Statement like SQL Query SELECT vehicleno, brandname, CASE WHEN inexpire < 0 THEN 'Expired' ELSE inexpire END AS inexpire FROM (SELECT Concat(plateno, vehicleno) AS vehicleno, brandname, Datediff(inedate, Curdat...

How Can I merge Duplicate row and Then delete all duplicate after keeping on row

Hello Good Morning. I have Table in that there is 15th column i want to merge duplicate rows in one row and then delete that duplicate row after keeping one row as record My Table like that id name email address city state country salary .... 1 a [email protected] null null a xyx null 2 null [email protected] a a null null 10000 ...

How to enter DOB randomly for each row in a table in in SQL?

Error SQL query: UPDATE `wfg_db`.`employee` SET `DOB` = (DECLARE@mm INT DECLARE@xm INT SET @mm =1 SET @xm =13 DECLARE@md INT DECLARE@xd INT SET @md =1 SET @xd =29 DECLARE@my INT DECLARE@xy INT SET @my =1900 SET @xy =2009 SELECT CAST( CAST( ( @xm - @mm ) * RAND( ) + @mm AS IN...

MySQL: Help with script needed

CREATE TABLE `photos` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `photo` varchar(255) NOT NULL, `hotel_id` bigint(20) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=23061 DEFAULT CHARSET=utf8 So, i have table 'photos.' Now i want ALTER this table and add column 'type'. ALTER TABLE photos ADD COLUMN type varcha...

When to use What Storage Engine in the Real World (MySQL)?

When to use What Storage Engine in the Real World (MySQL)? I Know MyISAM is good for Full Text Searches and InnoDB is good for Transactions/Referential integrity but What do you use When as there are so Many Storage Engines and Why in the real World. I guess what i'm after is a discussion of the various Storage Engines in Mysql so i ca...

In Sqlalchemy, if i add an object using session.add() and flush it, session.query() does not give that object, why?

While using SQLAlchemy, i add a object to a session using session.add(objname), then either explicitly flush it using session.flush or enable autoflush=True while creating the engine itself. Now in the session, if want to return that object via session.query(classname).all(), i cannot retrieve it. Why is that so? or is there a way in w...

how to skip lines of a csv file while using LOAD DATA command?

hi, i'm using sql command load data to insert data in a csv file to mysql database. the problem is that at the end of the file there's a few line like ",,,,,,,,,,,,,,,,,," (the csv file is a conversion of an excel file). so when sql get to those lines he send me : #1366 - Incorrect integer value: '' for column 'Bug_ID' at row 661. the ...

MySQL Error in multiple table UPDATE

Here is my query: UPDATE TBL_LABELS SET TMP.column_value = LBL.label_desc, TMP.info_value=LBL.helptext FROM TBL_LABELS TMP INNER JOIN hh_label_mast LBL ON TMP.column_name=LBL.lbl_id WHERE page_id in(as_page_id ,'HHGENPGID') AND lang_cd= as_langcd; While executing this I am getting an error like "right synta...