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

learn more… | top users | synonyms (2)

0
votes
0answers
42 views

How do I clear Oracle SQL history list?

I am using Oracle Database Express Edition 10g. I want to clear the history list as shown in the image below. Is there a SQL command that clears the history that saves the SQL queries. Thanks
1
vote
1answer
39 views

Exclude All Matching Rows

I have a table that has a unique ID, name, email, and some other fields. I want to match this table against another table on email, but sometimes emails are shared. The unique ID is not shared among ...
0
votes
0answers
40 views

Is there trade off for using FORCESEEK in SQL query?

What is the trade off for using Forceseek vs naturally letting SQL Server decide the execution plan? Is there a possibility that using forceseek invalidates the original query by returning a different ...
0
votes
0answers
11 views

Find missing associations in many to many relationship [duplicate]

I have client and category tables with a many to many relationship via client_category_association. table: client id name 1 ek 2 do table: category id name 1 un 2 dos 3 tres table: ...
0
votes
1answer
57 views

SQL Server Trigger When New Value <> Old Value

I need a trigger that update to Null a column averytime another column is changed. This is what I have now: ALTER TRIGGER [dbo].[ERROR_CLEAR] ON [dbo].[contact] AFTER UPDATE AS BEGIN SET NOCOUNT ON; ...
0
votes
1answer
47 views

using triggers to synchronize data

i've two database, Database1 and Database2! i'd like to synchronize data from the specific table of Database1 to Database2 whenever Database1 updates. I've used triggers to synchronise data and it ...
1
vote
1answer
38 views

SQL Error: OA-12991 - Unable to Drop a Column after removing all referenced Multi-Column constraints

I am new to Oracle DB and am in need of some help. I would like to drop a specific column but i am getting a specific error. The issue specifically is: SQL Error: ORA-12991: column is reference ...
0
votes
2answers
112 views

How to optimize this SQL Server query?

Anyone have any suggestions for how to optimize this query? Running SQL Server 2008.. The object is to look at accounts that were created yesterday, compare their email addresses and phone numbers ...
1
vote
1answer
18 views

Selecting a id from a pivot table

Trying to make a messaging system, similar to FB, people can message more than one person, when they do it creates a row for the user with the conversation id and user_id in conversation_user. ...
1
vote
0answers
26 views

MySql Error 1451 [closed]

I am by no means a database professional, but I've come here to get some help from people who may understand what's going wrong with my database. The error occurs on this line: update team set ...
0
votes
1answer
23 views

mySQL Event Help - Update colum value for each row every 15 minutes

Okay so if I have a login database: id | email | pass | attempts ---------------------------- 1 | "" | "" | 4 2 | "" | "" | 2 Attempts marks the number of attempts made at logging ...
3
votes
0answers
52 views

What is the difference between a field and a column? [duplicate]

Although I have always regarded the terms as interchangeable, I have read somewhere that they’re not the same. I suppose this means that a record is not exactly the same as a row either. I teach that ...
-1
votes
1answer
34 views

I run the script and it freezes me but varchar(max) and nvarchar(max) -1 always the ones with throws. How max turns -1 [closed]

ALTER FUNCTION [dbo].[get_logtable_script] (@table_name varchar(max)) RETURNS varchar(max) AS BEGIN declare @sql varchar(5000) declare @column_name varchar(max), @data_type varchar(max), ...
0
votes
1answer
25 views

Performing group by, Order by on a table starting from particular date to recent last date [closed]

Am sorry if this question sounds so dumb, please help. I currently use the below MS SQL query to get the list of volunteers on each date and it works perfectly fine, select Date, count(id) as co ...
1
vote
2answers
64 views

Delete rows from a table with inner join conditions

I have a query that selects data from a table based on some inner joins: select * from table1 p inner join table2 e on e.col1='YU' and e.username=p.username inner join table3 d on p.col2=d.col3 and ...
0
votes
1answer
51 views

Duplicate all stored procedures with a new name

I want to take all stored procedures and create them under a new name. My problem is with procedures that are called from other procedures . To rename a procedure is no problem for me. But to ...
2
votes
2answers
69 views

Deleting multiple rows based on the first instance of the value

I have a table with the below structure. I would like to delete all of the records that records that match ColA for any of the ColB's that have a ColC that has an even-numbered value. But only for the ...
2
votes
1answer
43 views

How to join at least two tables with different column names but same data, and only show the matching data

I am trying to combine two (possibly more) tables that has different column names but the same data within the columns I am trying to line up. For example, I have a table called dbo.member and within ...
1
vote
2answers
43 views

UPDATE from subquery updates rows that shouldn't be updated

Using PostgreSQL, I want to update a column. The information how to update is in another table. My try was this, but it also updates rows I don't want to update: update table_1 set column_x = ( ...
-1
votes
1answer
69 views

How to search a given date value in all tables of present in database

I need a query to search for a particular date 2017-04-01 in every table in my database. My database has many tables and I don't know in which table this value 2017-04-01 exists. Please suggest a ...
0
votes
2answers
47 views

need help joining two tables with a having count clause

I am a network guy so I apologize for this.... I have two tables I need to join and pull names out of. pt_basic has the patient names and codes I need, ptc_diagnosis has their diagnosis. I need to ...
0
votes
1answer
31 views

TSQL LDAP query via Linked Server

I have strange problem, I have 3 SQL Server 2012 with Sp1 instances, and on all of them, the versions are identical. I am trying to query Active Directory services, on 2 of them this is working ...
3
votes
2answers
82 views

UPSERT with ON CONFLICT using values from source table in the UPDATE part

Given: CREATE TABLE A ( PK_A INT8 NOT NULL, A INT8, PRIMARY KEY (PK_A) ); CREATE TABLE B ( PK_B INT8 NOT NULL, B INT8, PRIMARY KEY (PK_B) ); This query: insert into table_b (pk_b, b) select ...
1
vote
1answer
48 views

How do I calculate a field based on multiple non-numeric records

I have a several tables that I have joined together to get raw data. Some information about my query in the most basic form: Table1 has a column [FacilityID] that is a unique identifier in that ...
1
vote
1answer
46 views

Relationships mess

Say I have a table ads, and each ad has several medias - held in a medias table. Each media can be one of several types. Types are in a media_types table. So, for example, an ad has two medias: one ...
1
vote
1answer
31 views

MySQL: How to perform aggregate functions on rows where a column is duplicate, for all values of said column

I have a table where one of the columns may have duplicate entries, and I want to condense each set of duplicates into a single row, with an aggregate function applied to the other columns. I cannot ...
0
votes
0answers
11 views

Send SQL Server Snapshot to External FTP Server

I am trying to set up Snapshot-Replication for SQL Server 2014. I need Server 1, the publisher and distributor, to create a Snapshot and FTP to an external FTP server i.e. FTP server is not hosted on ...
2
votes
1answer
55 views

Grouping data by year based on two timestamp columns

My data table has the following columns: id INTEGER, name TEXT, created TIMESTAMP, deleted TIMESTAMP I want to product a report of the count of each name (which can appear several times in the ...
0
votes
1answer
42 views

One table with 2 foreign key mapping same primary key

SELECT status, schedule_switch.Switch_Employee_ID, schedule_switch.Requesting_Employee_ID, employee_info.FirstName, DATE_FORMAT(schedule_switch.date, '%m-%d-%Y') AS switchdate, ...
2
votes
1answer
90 views

Joining 2 queries to match Months side by side

I have 2 SQL statements that I want to join up. The first gets the actual data per month for Turnover accounts. The second query gets budgets (if any) for the same Turnover accounts. Query 1 Actuals ...
1
vote
2answers
33 views

Microsoft SQL Replication

Good day, I know very little about this topic and need some advice on where to start. I have two Microsoft SQL production databases that I would like to replicate so that I can run my reporting on ...
2
votes
2answers
229 views

Incorrect syntax generating XML

I am trying to run below code in SQL server 2000 SP4 but getting error. Declare @Body varchar(8000); Declare @TableHead varchar(8000); Declare @TableTail varchar(8000); Set NoCount On; Set ...
0
votes
2answers
61 views

How to create an insert trigger?

This is 1/4 of an overall assignment and I need help with this part. The assignment question: Create a trigger on the Rental table that will check when a new rental is to be inserted, that the ...
0
votes
0answers
42 views

VSS Snapshot backups vs 'proper' SQL backups

Our web application uses 8 databases hosted on SQL 2008 R2. All the databases run in the full recovery mode. We currently have two methods of backup: Weekly full backup with bi-hourly transaction ...
4
votes
2answers
64 views

How to script permissions for all logins/users/tables/schemas

Over the years, I have used countless scripts and applications to generate all permissions for SQL Server databases. I have used both paid and free tools. I feel that not one of them seems to be ...
1
vote
1answer
42 views

How to find out the models and prices for all the products (of any type) produced by maker B

The database scheme consists of four tables: Product (maker, model, type) PC (code, model, speed, ram, hd, cd, price) Laptop (code, model, speed, ram, hd, screen, price) Printer (code, model, color, ...
0
votes
1answer
87 views

SQL Server 2014 Buffer Pool Extension - There is insufficient memory available in the buffer pool

I am running Enterprise Microsoft SQL Server 2014 - 12.0.4213.0 (X64), with a 288GB of Ram, with Sql max memory set to 280. Twice in the last month I have received the There is insufficient memory ...
2
votes
1answer
71 views

Problem using pivot to display data

I currently have a table called sample, the structure of my attendance table is: create table sample ( Student_ID VARCHAR2 (10), Late_Attendance number (10) Default 3, Attendance_Date Date, ...
3
votes
2answers
74 views

Inserting rows with WHERE condition

Structure 1 table named: scan 2 columns named: id, and date Problem I have two working queries, a SELECT and then an INSERT which I would like to merge together creating a INSERT where the ...
1
vote
1answer
57 views

Updlock hint in stored procedure

If someone runs stored procedure below very fast (few thousand execution a second), can variable @amount change while executing and execute update more than one time? CREATE PROCEDURE ...
3
votes
0answers
44 views

Class-table inheritance without flag column

I am trying to implement proper Class Table Inheritance (aka Table Per Type Inheritance) in mySQL. AFAIK it's not offered out of the box so some hacking around is needed to make it tick. Current ...
-4
votes
1answer
28 views

SQL Server Table Creation Help [closed]

I'm a newbie, taking University course on DBA with Microsoft SQL Server and trying to write my first manual create table table but I keep getting errors in query or in using SQL Fiddle My attempt is ...
3
votes
1answer
77 views

DELETE rows which are not referenced in other table

I have two tables in a PostgreSQL 9.3 database: Table link_reply has a foreign key named which_group pointing to table link_group. I want to delete all rows from link_group where no related row in ...
0
votes
0answers
40 views

Cannot open InnoDB table though the .frm file for the table exists

tonight, my local Server has crashed because the power went out. I do not have backups because this was only experimental. And for the sake of learning, I'd like to know what to do in this case. ...
0
votes
1answer
19 views

sql update query for two connected tables [closed]

UPDATE rooms set r.vacant=true FROM reservation s,rooms r WHERE r.room_no = s.room_no AND s.cus_id=1 I created above sql queryto update room status.I want to set vacant as true when customer_id ...
0
votes
1answer
43 views

Multiple counts in a SQL statement

Is it possible to have more than one count in a SQL statement such as: SELECT student_id, ( COUNT (ATTENDANCE_ID) from attendance where attendance_id IN ('ABSENT') ...
1
vote
1answer
47 views

regexp_replace() vs. replace() to replace all matches in a string

By default, in PostgreSQL, regexp_replace() replaces the first occurrence of a substring in a string, while replace() replaces all occurrences of a substring in a string. Why is the default behavior ...
4
votes
3answers
96 views

Overloaded Constructor for SQL Server stored procedure?

I'm making a stored procedure and I want it to accept a different number of parameters and have different behavior based on the number of parameters supplied. So let's say if I supply one parameter ...
3
votes
2answers
47 views

SQL Server - What access do I have during a transaction rollback?

As the title says, do I have read/write access to my entire database while it is rolling back a transaction? Or read/write access to only the tables that the transaction did not write to? What is the ...
2
votes
2answers
95 views

Eliminate duplicates and optimize performance for big count and small LIMIT

I have a big query in my Postgres 9.1 database: SELECT *, count(*) OVER () AS full_count FROM ( SELECT DISTINCT ON(message.messagetime,message.messageid) message.messagetime, ...