Structured Query Language (SQL). For questions about the language, not Microsoft SQL Server (tag sql-server).

learn more… | top users | synonyms (2)

-1
votes
0answers
17 views

Update column with different value for multiple rows [on hold]

I often find myself using excel to create update queries. When I need to update multiple rows with different values for a column, I simply use excel to create the update queries (multiple). Here's ...
5
votes
3answers
352 views

Capitalize only the first letter of each word of each sentence in SQL Server

I want to capitalize only the first letter of each word of each sentence in a SQL column. For example, if the sentence is: 'I like movies' then I need the output: 'I Like Movies' Query: ...
-5
votes
1answer
36 views

Could someone explain how PHP, MySQL, SQL work together?

I feel so confused on what they are and how they work. From what I've read PHP is a server-side language. Therefore, it's a language basically designed to talk with the web server to get it to do ...
0
votes
1answer
49 views

How to handle HUGE 16GB SQL file?

I've abc.sql file exported from MySQL Table. This abc.sql file is 16 GB in size with around 28 million insert statements. I'm looking for option to store it in SQL Server Database. Couple of ...
-1
votes
0answers
40 views

Which columns need index in recursive table and soft delete

I have a Category table like below : I have a recursive relationship on this Category table. I use soft delete in my project. I have 2000 rows in Category table. When I want to show data in a UI ...
0
votes
1answer
147 views

Converting sql into mongodb nosql

I am trying to convert oracle sql into its mongodb equivalent version. create table my_hll as select mod(ora_hash(n), 1024) bucket, max(num_zeroes(trunc(ora_hash(n)/1024)))+1 val from previous_data ...
1
vote
1answer
11 views

Optimize MySQL Query with multiple subqueries

I have the following Query: SELECT p.id AS post_id, p.status, p.user_id AS post_user_id, (SELECT pl.id FROM postlocks AS pl WHERE pl.post_id = p.id) AS postlock_id, (SELECT COUNT(rn.id) FROM ...
0
votes
1answer
129 views

Error code 1064 when trying to add anything manually

So i tried adding via php and i noticed i got a error , so i went into phpmyadmin and tried adding it manually by clicking the SQL in phpmyadmin and write in INSERT INTO `test`(`IDtest`, ...
1
vote
1answer
255 views

Handling SQL updates for production deployments

I'm researching for quite a long time now (about two months) and haven't found a good approach for this, so I'll ask you experts out there in the hope of some enlightenment. I run a pretty ...
1
vote
1answer
22 views

Why doesn't Oracle show my privileges?

I have a user that is able to drop, alter, create tables and indexes. But when I list the privileges for my Oracle user it does not show any CREATE TABLE or other such privileges. How am I able to do ...
3
votes
3answers
65 views

How to format a phone number in a SELECT statement?

I have a database with table name student. I would like to display the register_number and phone_number of the student. The phone_number should be in the following format: +91-123-456-7890 and if ...
-1
votes
0answers
15 views

Grant access schema to user [on hold]

Getting error while writing the following query SQL>grant select, insert, update, alter, delete on schema::schema_name to user; showing as missing keyword –
4
votes
1answer
42 views

QUERY - join two tables based on date and filter results

My apologies for the cryptic title. I guess my inability to condense my question into a title is indicative of my inability to come up with an answer to the following: I have data in two tables (MS ...
1
vote
2answers
53 views

How can I represent a list of words and related words in a relational structure?

I have a list of header words and words as below and I am trying to understand how best to represent these in a relational table: Header Other Words Words abandon abandoned, ...
1
vote
1answer
46 views

Syntax Error Help [on hold]

I have this script that works just fine but when I change the directory paths it seems to get this syntax error "unclosed quotation mark after the character string" I have looked over this for hours ...
1
vote
1answer
16 views

MySQL trigger to update a key if record exists

I'm trying to build a trigger in MariaDB to follow this logic: 1. If the primary key val exists, increment the counter column 2. Else, insert the primary key into the table Here is my prosed ...
0
votes
1answer
15 views

How to create tables with geometry in Postgres?

I need to create 2 tables with distinct geometry. One needs to be a table that has point information. The other needs to be a table that has line information. They do not need to be populated as ...
0
votes
1answer
20 views

How to create a table with data from other tables in Postgres?

How to create a table that with data from 6 other tables that have the same columns? The table names are: ab1_2, ab3, ab4, ab5 and ab6. This is a spatial data with the Postgis extension enabled so ...
3
votes
3answers
64 views

Get last two IDs from rows for last two days

I have a table that looks like this: +------------+------------+--------+----------+--------------+ | terminalid | date | toolid | toolname | workcenterid | ...
0
votes
0answers
27 views

Can I rely purely on an auto-generated unique primary key for CRUD? [on hold]

I am in the process of developing a large web-based application that uses a SQL server database for data storage. There will be multiple accounts all sharing one database for generic account related ...
1
vote
1answer
182 views

Update Table Query: Automatically handle “Enter Parameter Value” prompts?

EDIT: Here are two database Files that demonstrate an applied example of the problem: Example_DBs.zip (Zipped due to 2 link limitation.) Note: The missing Field in "Not Working.mdb" is ...
1
vote
3answers
128 views

When to use DISTINCT [on hold]

When is it good to use SELECT DISTINCT .. and when not? Basically whenever I see DISTINCT keyword I start to get cautious as most of the cases it is ends up finding that actual query has some ...
-1
votes
0answers
23 views

Decomposition of complex queries [on hold]

select c.ssn,c.cname,c.address,c.ph,c.age from customer c,account a,acc_cust ac,loan_cust lc where c.ssn=ac.ssn and a.accno=ac.accno and c.ssn=lc.ssn and a.atype='joint' group by ...
0
votes
1answer
611 views

PostgreSQL: Merge rows with the same date and time but different numeric values in one row with average of the values

I am working on a financial software where we have huge table of ticks with more than 800M rows. A single row: symbol_id, broker_id, date, time, bid, ask The problem is the table has many ticks ...
5
votes
1answer
142 views

Find Shoe Size Selection

I'm developing a feature that allows users to input their foot dimensions and have the proper shoe size returned to them. I'm having difficulty deciding on the best way to implement this feature. The ...
0
votes
2answers
40 views

Update Grouped records

I have the following query that returns duplicate values: SELECT [t].[Field1], [t].[Field2], COUNT([t].[Field2]) FROM [dbo].[Table1] AS [t] WHERE [t].[Field2] <> '' GROUP BY ...
0
votes
0answers
32 views

Deadlocking on two update statements

Server Info: Microsoft SQL Server 2005 - 9.00.5057.00 (X64) Standard Edition (Build 7601: Service Pack 1) I have a table called LAB it has about 38 million rows in it. The primary key is a single ...
5
votes
4answers
54k views

How to write a query to find all tables in a db that have a specific column name

I've got a database with about 100 tables and I need to build a join query to get specific data from two of them. I know one but not the other. Basically I need something like: select ...
1
vote
2answers
30 views

Dynamic script to Drop Indices of a specific column

I´m currently writing an SQL script to update our database and for that I need a script to drop indices that rely on a specific column (because I also want to drop the column). In my script I have ...
0
votes
1answer
34 views

Accessing computed column in a nested query

I am using nested sets to represent tree-type date in SQL-Server and basing what I'm doing on this article: http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ by Mike Hillyer. In ...
0
votes
0answers
22 views

Getting count mismatch for output records [on hold]

I want count of incorreclty routed task .first query count is correct 1. select * from statuschanges where incorrectlyrouted='t' count=388 and when I modify it according to requirement as ...
0
votes
1answer
23 views

SQL Server Msg 2812, level 16 state 62 line 30

i want to make automatic backup and restore my database on SQL Server 2012. i have made automatic backup with script in my windows server. now, i want to make auto restore with script, i have found ...
1
vote
1answer
23 views

How to handle id's referring to multiple tables in an SQL database

I'm working on an MySQL Database for my website but I'm not sure on how to proceed now. I get to the point where actions have requirements in multiple other tables, but also have influences in ...
1
vote
2answers
64 views

How to get the DDL for Oracle object without extra characters

When using Oracle's dbms_metadata.get_ddl for getting DDL I see some extra characters, especially there are whitespace characters at very beginning of DDL script. For example, if I create this sample ...
0
votes
2answers
19 views

Query Amount of entries per column from multiple tables + provide table name

I have five tables with all having the same column (objart): I queried for the number of entries and combined the result with UNION, which worked well. Two questions: 1) Is there a shorter/ better ...
5
votes
1answer
87 views

Oracle - physical writes to an index

We have a large UPDATE (affects about 200K records) on a huge table (80M records). Due to a coding mistake, the UPDATE updates the same set of rows (200K) all over again with the exact same value. ...
-1
votes
2answers
121 views

Retrieve data from two tables where table 2 has multiple entries for one value

I am looking for the best way to create a SELECT statement to pull data from 2 tables. Example below is the table structures: Table 1 | Table 2 ...
3
votes
2answers
35 views

How to assign a constant to a value on all rows, if that value is adjacent to some value on any row

I have data like this (STATUS, and NAME are the column headers): STATUS NAME ACTIVE A1 INACTIVE A1 INACTIVE B1 INACTIVE B1 I want to include a CUSTOM field that returns a ...
2
votes
1answer
50 views

Bulk Insert into view and find missing values

I have 2 tables, Categories, and SubCategories: CREATE TABLE [dbo].[tbCategories] ( [CategoryID] [int] IDENTITY(1,1) NOT NULL, [CategoryName] [varchar](250) NULL, ) CREATE TABLE ...
2
votes
1answer
22 views

Postgres - Regroup rows with at least one common attribute

I have the following table : Letter | Figure -------|------- A | 1 B | 1 B | 2 C | 2 C | 3 D | 3 E | 4 I would like to group rows that share ...
2
votes
1answer
443 views

How to include concatenated values in a dynamic pivot column value?

I am extending a dynamic pivot query. The original query works fine. In the second query, instead of displaying the date as a value for the pivot column, I would like to display a concatenated column. ...
2
votes
1answer
42 views

Prevent duplicate entries

I have log in form with Username and Password. $con=mysqli_connect(LOGINDETAILS); /*if (!$con) { die("Can not connect: " . mysql_error()); } */ $Username = $_POST['Username']; $pswd = ...
1
vote
1answer
127 views

Oracle connection from Squirrel SQL

I am trying to set up Squirrel SQL for the first time on my Ubuntu Desktop. Here is the SQLPlus command I use to connect to my DB: sqlplus64 "user/pw@pashratgdb01.lex.bb.com:1876/atgpen1.lex.bb.com" ...
2
votes
1answer
3k views

Sybase SQL Anywhere 12 - Get all indexes which are unique -> ambigious sysindexes error

we are using a Sybase SQL Anywhere 12 db. In the db there are indices, which are unique, but shouldn't be unique. Therefore I search for a quick way to list all tables with unique primary keys. I ...
1
vote
1answer
15 views

What run-time configuration parameters are used for concurrent index creation

Suppose I were to run the following. The first line shows the server's default maintenance_work_mem value. mhildreth=# show maintenance_work_mem; maintenance_work_mem ---------------------- 16MB (1 ...
1
vote
2answers
174 views

Which data type should I use to store a 11 digit number [closed]

Which data type I should use to store an 11 digit number? It must store '0' as the first digit. For example, if I store 02421402015, it must show 02421402015. I tried using varchar and it shows ...
3
votes
3answers
7k views

Select columns inside json_agg

I have a query like: SELECT a.id, a.name, json_agg(b.*) as "item" FROM a JOIN b ON b.item_id = a.id GROUP BY a.id, a.name; How can I select the columns in b so I don't have b.item_id in the ...
4
votes
1answer
244 views

SQL query to compute longest travel time for each id number

I am a novice in SQL and I have a problem as follows. There are two tables TRNSCTN_TAB: CREATE TABLE TRNSCTN_TAB( `transactionId` int , `UniqueId` int , `transactionTimeStamp` ...
2
votes
1answer
51 views

Calculate path to node in a tree

The following is part of a bigger system. The tree data table and temporary table cannot be changed easily so I need to work with those. The tree structure is more complex (deeper and wider) but the ...
-4
votes
0answers
30 views

if increase table in a database (20 to 20000) on there database performance is effected or not in sql [closed]

in that time in my database having only 20 table but now our business model change and there need 2000 table in future. so i cant understand that database performance is effected or not.if effected ...