In a table I have the following schema table1: playerID int primary key, nationalities nvarchar table2: playerID int, pubVisited nvarchar Now, I want to set all the players' playedVisited to null, for player whose nationality is "England", any idea on how to do this? ...
SELECT `name` , COUNT(*) AS `count` FROM `t1`, `t2` WHERE `t2`.`id` = `t1`.`id` GROUP BY `t2`.`id` I want to obtain the name from t1 and the number of rows in t2 where the id is the same as on t1. I've got the above so far, however it won't return any data if there are no rows in t2 that match. I'd prefer count to be 0 (or NULL) if t...
is there a way to have a mysql select statement return fully qualified column names like "table.field" without using AS for every single field? like so: SELECT * FROM table1 LEFT JOIN table2 on table1.f_ID = table2.ID and the result would be: "table1.ID", "table1.name", "table2.ID", "table2.name", ... ...
How to 'group by' a query using an alias, for example: select count(*), (select * from....) as alias_column from table group by alias_column I get 'alias_column' : INVALID_IDENTIFIER error message. Why? How to group this query? ...
I'm trying to do a subselect in pgsql aka postgresql and the example I found doesn't work: SELECT id FROM (SELECT * FROM table); ...
Hi everyone. I've a terrible memory. Whenever I do a CONNECT BY query in Oracle - and I do mean every time - I have to think hard and usually through trial and error work out on which argument the PRIOR should go. I don't know why I don't remember - but I don't. Does anyone have a handy memory mnemonic so I always remember ? For examp...
It appear that SQL Server like most other products Random Function really is not that random. So we have this nice little function to generate a 10 char value. Is there a better way to accomplish what the following does. I am betting there is. DECLARE @SaltCount INT; SELECT @SaltCount = COUNT(*) FROM tmp_NewLogin; PRINT 'Set Salt val...
I am trying to write a tool that can compare a database’s schema to the SQL in an install script. Getting the information from the database is pretty straightforward but I am having a little trouble parsing the install scripts. I have played with a few of the parsers that show up on Google but they seemed somewhat incomplete. Ideall...
I have a website built using Asp.net and LinqToSql for Data Access. In a certain section of the site, LinqToSql produces a query that looks like this (from my dev machine): select ... from table1 left outer join table2 on table1 where ... left outer join table3 on table2 where ... Since the connection between table2 and table1 is not ...
Hi, I've got an MS access database and I would need to create an SQL query that allows me to select all the not distinct entries in one column while still keeping all the values. In this case more than ever an example is worth thousands of words: Table: A B C 1 x q 2 y w 3 y e 4 z r 5 z t 6 z y SQL magic Result: B C y w y e z r z...
I've always done web apps and now I need to do a console app. I need to use both an odbc connection and a regular connection. In the past I would have used: <add name="LinkConnectionString" connectionString="Data Source=SERENITY\SQLEXPRESS;Initial Catalog=Link;Integrated Security=True" providerName="System.Data.SqlClient"/> In the w...
I have to say that I am really missing Query Analyzer now that the functionality has been integrated into the much heavier SQL Management Studio provided with SQL Server 2005/2008. I was looking for suggestions for a cheap/free and lightweight querying IDE with functionality similar to query analyzer so I don't have to wait for this big ...
I have these 3 tables + data: items: itemId, itemName data: 1, my item one categories: catId, catName data: 1, my cat one. 2, my cat two map: mapId, itemId, catId When you include item "my item one" in category "my cat one", you insert [1, 1, 1] into the map. When you add "my item one" to "my cat two", you insert [2, 1, 2] into the m...
I have 4 databases with similar schema's, and I'm trying to create a query to return just the table, column pairs that exist ONLY in database 1 and do not exist in database 2, 3, or 4. Currently I can return the symmetric difference between database 1 and 2 via the following query... select table_name, column_name from ( select ta...
We frequently have users that create multiple accounts and then end up storing the same lesson activity data more than once. Once they realize the error, then they contact us to merge the accounts into a single one that they can use. I've been beating myself to death trying to figure out how to write a query in MySQL that will merge th...
I have two tables (renamed/refactored for illustrative purposes) with a Many-To-Many relationship in an HSQL database. I want everything to be wiped out when I delete from one side of a Many-to-Many relationship (without querying the table; this is performance critical) Here are my main tables: CREATE TABLE PERSON ( PERSON_ID INTE...
If I wanted to replace the * with a column name, what would it be? create type mytable$t as table of number; / declare mytmou mytable$t := myTable$T(); cnt pls_integer ; begin mytmou := myTable$T(1,2,3,4,5,6); SELECT count(*) into cnt From Table (mytmou); dbms_output.put_line(cnt); end; 6 ...
Developing a website and just trying to get back into the swing of (clever) SQL queries etc, my mind had totally gone tonight! There is a website http://www.ufindus.com/ which has a textbox allowing you to enter either a place name or a postcode/zipcode. I am trying to do something similiar but I am rubbish at SQL - so how do you const...
Is it a good idea to store my SQL queries in a global resource file instead of having it in my codebehind? I know stored procedures would be a better solution but I don't have that luxury on this project. I don't want queries all over my pages and thought a central repository would be a better idea. ...
In an effort to get rid of some fragmentation left from rebuilding and defraging we thought that we would drop and create indexes so I went to write a script. It identifies a clustered index that needs work and drops indexes and primary keys and rebuilds the indexes and primary keys for a table. Here is the problem I ran into: SQL Serv...