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

learn more… | top users | synonyms (2)

1
vote
1answer
57 views

Querying dates with different timezones at db and application level

There is a created_at column of datetime type in the order table. In my application I'm storing dates to the db as datetime in UTC. When I bring them back from the db I'm presenting/formating them to ...
1
vote
1answer
40 views

Copying Partitioned table data to another table on different SQL Server

We have a partition table on Production environment with 2338117620 rows and 206 partitions. Now we have to copy only last 2 year data in Test environment. But the problem is that , Import Export ...
-1
votes
0answers
30 views

How does IN and NOT IN work in SQL?

How does IN and NOT IN work in SQL ? For example if a query like as SELECT count(customer_number) Count_Customer FROM customer_master WHERE customer_number IN (SELECT customer_number FROM ...
0
votes
2answers
40 views

SQL Server stop/exit query on error

I have the below 2 commands (insert and drop) in a query. When I run the query I get an error saying: String or binary data would be truncated. When I go to check the source table I find out that ...
-2
votes
1answer
64 views

select from where row x column A = row x+1 column B

Query select from where row x column A = row x+1 column B Data Car ID | Time 1 | Time 2 1 | 5:15 | 4:12 2 | 3:13 | 5:15 3 | 6:12 | 5:15 4 | 4:12 | 2:15 Result It ...
3
votes
1answer
66 views

Importing and exporting data from a database to another using SyBase is taking a long time

In order to update a database(Sybase Adaptive Server Anywhere 8) I'm taking all the data from one database on clients computer and using SELECT * FROM tabelName OUTPUT TO 'C:\path' FORMAT ASCII ...
0
votes
0answers
16 views

How to fetch Records matched with Comma separated value stored in table column

I have data stored in this format. topic_id tags 1 `tag1,tag2,tag3` 2 `tag1,tag4,tag5` 3 `tag2,tag4,tag5` 4 `tag6,tag7,tag2` I want Mysql Query to ...
0
votes
1answer
115 views

Speed up query that have joined tables and function calls

I have a Bootstrap Table that use server side pagination to display results from a query like this: SELECT Item.a, Item.b, Item.c, Info1.Name, Info1.Name, Info2.Name, ...
0
votes
1answer
21 views

Insert data into oracle table with loop?

How to use sql to insert line detail data into header with the output like below. line detail MO_ID SEQ_NO QTY LEVEL 121M 2 3 121M 3 9 125M 2 6 125M 3 7 125M ...
0
votes
2answers
61 views

Oracle EM 11g - query to find out CPU utilization

in OEM 11g, if you see performance, there are graph about cpu utilization, memory utilization, and disk I/O utilization. my question is: what is the script to see CPU-Utilization history ? Thank ...
0
votes
0answers
16 views

MS SQL, MySQL linked server distributed transactions

I have a linked mysql server object that I am accessing through MS SQL. I'm unable to execute any 'OpenQuery' queries to the MYSQL inside a trigger because I get the following error message: Msg ...
0
votes
1answer
55 views

Oracle equivalent of sp_rename in SQL Server

I am trying to rename a column of multiple tables in Oracle using a cursor over a table that has all the information of other table. In SQL Server I was able to do it correctly using sp_rename. I am ...
-1
votes
1answer
29 views

Can update fail if old data are invalid? [closed]

I am just starting to manage the backend for a project and still getting used to the code and database. I had this case when for one user, the value displayed in the user module was UTC+10:00, however ...
0
votes
1answer
27 views

Event to update a column won't run

I have successfully created an event in MySQL to update a column called dob in a table called auth. My problem is that the event won't run. Here is my code: DELIMITER $$ CREATE EVENT ...
1
vote
1answer
17 views

append 0 returned by DATENAME function Sql Server management studio

I want to append 0 to my minute(mi) result returned by DATENAME function of sql server management studio. select datename(mi,GETDATE()) If the current time where 12:06:33.12(hh:mi:ss) then '6' ...
-2
votes
0answers
64 views

Replace string between single quotes

How to replace text between quotes in SQL? I am retrieving the top 50000 expensive queries from the database. In the query output I am seeing the same queries with different parameters populated. ...
0
votes
1answer
37 views

MySql - Get all children in JOIN if one child matches a condition

I have a one-to-many relationship between two tables Parent and Child. I want to get all children if one child matches a condition. Something like below: SELECT * FROM Parent p INNER JOIN Child c ON ...
3
votes
1answer
194 views

Building new version of a table and replacing the old one by rename, is it a good idea?

We have a slightly heavy table which is semi-frequently (every ~5 minute) being recreated by a procedure. I wondered if there was something that could be done in order to minimize the downtime during ...
1
vote
1answer
45 views

SQL log file growing exponentially [duplicate]

We have a SQL database on FULL back up with a client and they have NOT implemented a Log file maintenance and noticed that the log file grows exponentially, filling the 20GB they have allocated to it ...
4
votes
4answers
75 views

Select rows for which at least one row per set meets a condition

I have following table: create table test ( company_id integer not null, client_id integer not null, client_status text, unique (company_id, client_id) ); insert into test values (1, 1, '...
6
votes
1answer
178 views

Create a function to generate a column value in a specific format

I am new to databases or, in fact, programming. I want to create a function or maybe a stored procedure (if it works) to insert/create default values for a column of the users table. In this table, ...
0
votes
1answer
21 views

Get the highest value [duplicate]

I have the following data: account_id note_id size id ---------- ------- ------ -------- 48 1554 196036 28229509 48 1554 18777 28229588 48 1554 4861 28229566 ...
0
votes
1answer
48 views

Insert into multiple tables with one query

I would like to add data to at least three tables with one query. I thought of something like this: WITH ins AS ( INSERT INTO core.adr (street, "number", postal_code, city) VALUES ('test ...
1
vote
0answers
26 views

pub/sub database schema

I am having a hard time creating a pub/sub database schema with stateful metrics. Saying it aloud it sounds easy enough: "There are many organizations that will subscribe to checklists that are ...
0
votes
2answers
36 views

sp_configure: Preserve values of 'xp_cmdshell' and 'Show Advanced Options'

I dont want to disturb the server values of xp_CMDShell & Show Advanced options. The criteria is to use SQL to run a batch file. This is the script i am trying to solve it with: DECLARE @...
0
votes
1answer
35 views

Inserting two tables together with no id or unique anything

Let's assume we have two tables: table1 Name Height Age Mary 1.60 18 Mary 1.62 19 Mary 1.68 20 John 1.80 17 John 1.85 18 John 1.86 19 table2 Name Height Age Mary 1.60 18 Mary 1.62 19 Mary 1.68 20 ...
0
votes
2answers
46 views

Sorting by month by chronological occurrence when the year doesn't matter?

Given a table with a date column, and given a query that selects dates in a given range of the year (like "November 20th through February 20th"), and where the year portion of the date doesn't matter (...
-3
votes
1answer
37 views

How link data - delimiter

How can I make script take mydata from mytable instead of providing values like in an example below? SO instead of set values to Code and Location link these to mycolumns in mytables? with data as ( ...
1
vote
1answer
47 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....
1
vote
2answers
141 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 ...
-5
votes
1answer
55 views

Could someone explain how PHP, MySQL, SQL work together? [closed]

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 ...
2
votes
1answer
100 views

Update column with different value for multiple rows using a csv file

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 ...
1
vote
1answer
40 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 ...
1
vote
1answer
58 views

Syntax Error Help [closed]

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
31 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
23 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 ...
2
votes
2answers
63 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, ...
0
votes
1answer
25 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 ...
5
votes
2answers
79 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
3answers
151 views

When to use DISTINCT [closed]

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 ...
0
votes
0answers
37 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 ...
0
votes
2answers
44 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 [t].[...
3
votes
3answers
78 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
1answer
36 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
1answer
44 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 ...
0
votes
2answers
24 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 ...
4
votes
2answers
42 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
2answers
51 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 ...
1
vote
1answer
17 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 ...
13
votes
7answers
2k 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: ...