Tag Info

New answers tagged

0

The question is not very clear. If what you want is to have as result **every object that is related to name 'orange' but not to name 'banana', here is one way: SELECT o.* -- Change this to only the columns needed. FROM objects AS o -- If there are data needed from other tables, -- join here. WHERE ...


2

There is essentially no difference between table and db.table. Once the tables (for SELECT or for INSERT) is opened, it is just a table, regardless of what database it is in.


3

May I suggest doing it in a couple of passes: SQL> select * from records; SUBJ_NO -------------------------------------------------------------------------------- CBS405/505 Fourth Week(Wtr Qtr) BR 404-504-604 Fifth Week(Fall Qtr) AP490-QB-09 Weekly Sessions(Spr Qtr) APR501 Fourth Week(Wtr Qtr) MP 300 Fourth Week(Wtr Qtr) ACX/501 Fifth Week(Sum Qtr) ACX ...


1

You must be using something to insert these records into your MSN_Capture table from the front end data collection software. Why not just update the insert statement with a case statement?


-1

Try wrapping it inside another select. SELECT SUM(amount), contractor_id FROM ( SELECT SUM(extras.amount) as amount, appointments.contractor_id FROM extras INNER JOIN appointments ON extras.appt_id = appointments.appt_id WHERE appointments.time > CURRENT_TIMESTAMP - interval '8 days' AND appointments.time < CURRENT_TIMESTAMP AND ...


0

Consider turning product_id_CSV into IN ( '...', '...', ... ) and then batching the work -- instead of looping.


-1

seems to be god you can try WHERE appointments.time > CURRENT_TIMESTAMP - interval '8 days' AND appointments\ .time < CURRENT_TIMESTAMP AND appointments.confirmed = 1 AND (appointments.status = 10 OR appointments.status=12) after the group by like ... SELECT SUM(extras.amount), appointments.contractor_id FROM extras INNER JOIN appointments ON ...


4

when I try to use Management Studio to attach to the .mdf file There's not really a gentle way to say this: Stop doing that. The GUI is full of bugs and bad defaults; more importantly, unless you take a screencast, we have no way to identify exactly what steps you're taking as you point and click your way through the GUI. Log in as a system ...


2

Shift+Del Works for me in SSMS 2014.


0

table2, probably, will have more rows than table1, so partition for table2 is even more required than for table1. on the other side table2 does not have partition-key (datetime), so this "construction" is looks useless. You need same partition key for both tables. You may add datetime column to table2 and add it to PK - as result you will have duplicate ...


0

I am generally opposed to triggers. They have their place but surprising behaviour, when you do one simple thing and the change ripples out across the world, will give you headaches later. Instead of having storage_size and memory_usage on the users table, how about creating a view that displays the data you are looking for? Then you don't need triggers ...


1

Please check out some interesting puzzles @ http://msbiskills.com/tsql-puzzles-asked-in-interview-over-the-years/


1

Put EXECUTE AS ... at the start of your script, to impersonate the appropriate login.


3

There is no Run As proxy for T-SQL Job steps in SQL Server Agent. You could upvote this with the chance that one day it will be filled: https://connect.microsoft.com/SQLServer/feedback/details/632955/enable-sql-agent-proxies-to-run-t-sql-job-steps The current way to get a T-SQL command to run with a proxy is to run it as an Operating System Command ...


1

Let's try : CREATE OR REPLACE FUNCTION public.sp_get_user() RETURNS json LANGUAGE plpgsql AS $function$BEGIN INSERT INTO users (name, password) VALUES ('deadeye', 'test'); RETURN row_to_json(row) FROM (SELECT name, password FROM users WHERE name = 'deadeye') row; END;$function$ Now, let's test : SELECT sp_get_user(); ...


2

Yes. Everything you did inside the same transaction is visible to later commands inside the same transaction. Just not to other transactions until committed. This is true for all isolation levels except Read uncommitted where "dirty reads" are possible (but that does not affect your question per se). It's implemented with the MVCC model (Multiversion ...


3

You half miss the connection between the two tables. The ...relid columns must match, too: SELECT attname, c.* FROM pg_attribute a JOIN pg_constraint c ON attrelid = conrelid -- this was missing AND attnum = ANY (conkey) WHERE attrelid = 'test_table'::regclass::oid;


0

ypercube is correct in pointing that the output which you claim would be different than the actual output. As the subquery is going to display the total value unless add another filter condition on fk_id_anexa. SELECT a2a.ID, a2a.fk_id_anexa, a2a.PET1, a2a.PET2, a2a.ALT1, a2a.ALT2, (SELECT SUM(CANT) FROM ANEXA2A_TABEL2_ROWS tab2 where TIP = 'PET' and ...


3

There are several ways to do this. Either remove the join and convert the subqueries to correlated: SELECT a.ID, a.FK_ID_ANEXA, a.PET1, a.PET2, a.ALT1, a.ALT2, (SELECT SUM(b.CANT) FROM ANEXA2A_TABEL2_ROWS b WHERE b.TIP = 'PET' AND b.FK_ID_ANEXA = a.FK_ID_ANEXA ) AS PET3, (SELECT SUM(b.CANT) FROM ANEXA2A_TABEL2_ROWS b WHERE b.TIP = ...


0

PRIMARY KEY (`STUDENT_ID`, `ANNUAL_FEES_ANNUAL_FEE_ID`, `PARENTS_PARENTS_ID`, `PARENTS_ACCOUNTS_ACCOUNT_ID`), It seems like Students would have student_id as its PRIMARY KEY. DECIMAL(4,2) -- do you want to limit things to $99.99? This is what I mean about many-to-many relation table: CREATE TABLE XtoY ( # No surrogate id for this table x_id ...


0

You need Dynamic SQL SET @country1=''; SET @whereclause = IF(@country1='','',CONCAT('WHERE country=',QUOTE(@country1))); SET @sql = CONCAT('SELECT country_id, country FROM country ',@whereclause); PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s; You can simply set @country1 to whatever country code you need. If it is blank, you get all rows.


1

If you're looking for a Set Theory based equivalency test, your best bet is to convert any WHERE conditions that can be converted into a type of JOIN (inner or outer) and have the statement refactored. This includes IN subselect and EXISTS subselect and any other conditions in the WHERE clause that contains the word SELECT. If you perform this on both SQL ...


0

I would also say bang it out in your test environment as well. Make sure you have Ip connectivity as well as name resolution too.


0

you can try it. DECLARE @country1 varchar(30) SET @country1 = 'asd' SELECT country_id, country FROM country WHERE country.country LIKE IF(@country Is Null, '%', @country); Is Null used when not insert data in country field IF @country Is Null Begin Select country_id,country from country End ELSE IF @country Is Not Null Begin Select country_id,country ...


1

Why not use a Common Table Expression aka with clause? It's designed for exactly this purpose (among others). with orderIds as ( select orderId from ... ) select * from Customers where orderId in (select orderId from orderIds) or secondaryOrderId in (select orderId from orderIds); See ...


1

You query should probably be rewritten as an exists instead of an in See this link for more examples. Your query would then look something along the lines of select * from Customers C where exists (select 'x' from ordertable o where c.orderid = o.orderid) or exists (select 'x' from ordertable o where c.secondaryOrderId = o.orderid) If both subqueries ...


7

Try: where exists (select * .... where Customers.orderId = ... or Customers.secondaryId = ... ) Eg, if you were planning on: where orderId in (select value from ...) or secondaryorderid in (select value from ...) Then you make it so that you only call your subquery once, and build your OR clause into it. where exists (select * ...


1

This should do the job. DECLARE @country1 varchar(30) SET @country1 = 'asd' SELECT country_id, country FROM country WHERE country.country LIKE IF(@country = '', '%', @country); The IF statement works like this : IF(condition, ifverifiedcondition, else);


-2

select distinct City, count(City) OVER(PARTITION BY City) as countCity ,Country, count(Country) OVER(PARTITION BY Country) as countCountry from address City countCity Country countCountry Boston | 1 | US | 2 Chicago | 1 | US | 2 London | 4 | UK | 4 Paris | 1 | FR | 1


0

Here is one way - I'm not sure if I'm understanding the question though: select b.city ,b.citycount ,c.country ,c.countrycount from (select row_number() over (order by city) as no ,city ,count(city) as citycount from address group by city) as b left outer join ( select row_number() over (order by country) as no ,country ...


1

You are getting that error because you have a row in patient_info where either charges or payment (or both) are NULL. To just make the error go away, you could replace ' ' || c.charges - c.payment || ' ' with ' ' || (c.charges - c.payment) || ' ' but I suppose you would want to handle the case where charges or payment are NULL in some meaningful way, ...


1

I'll bet that you are currently I/O bound. This means that nothing can speed it up. (And Rolando's suggestions may be futile.) Let's look deeper. Is this LOAD a recurring task? If so, how often? Is everything blocked waiting for table to be reloaded? Simple solution: Load into a different table, then do a double RENAME TABLE to swap it in. Only ...


-1

Although LOAD DATA INFILE can work against InnoDB, there are too many ways InnoDB gets tapped to its limits before swapping and bottlenecks takeover. Here is a Pictorial Representation of InnoDB (from Percona CTO Vadim Tkachenko) The bottlenecks would be goring through the following structures InnoDB Buffer Pool Transaction Logs (ib_lofile0, ...


1

Your Snapshot Agent process account does not have sufficient permissions to access the snapshot folder. In the section Permissions That Are Required by Agents in Replication Agent Security Model, the Snapshot Agent process account requires db_owner in the distribution and publication databases and needs read, write, and modify permissions on the snapshot ...


1

You may add the WHERE clause in the LEFT JOIN so not joining rows are still in the result set. SELECT `performers`.`hash`, `performers`.`alias`, `performers`.`date_updated`, `performers`.`status`, IF(`performers`.`status` = 'active', 'deleted','active') AS `statususe`, `images`.`image_hash_file` FROM `performers` LEFT JOIN `images` ON ...


1

This is normal. Max server memory means that your server cannot use more than that amount, not that it must use that amount. Min server memory doesn't mean it must use that amount, either; it means that once the server uses that much it doesn't surrender memory to below that amount. Basically, SQL Server uses the amount of memory it needs until it ...


1

Unless there is a security problem (you want someone to view some information, but not the other), there is no need to; however in many sql platforms you can deny access to specific fields in security or just create a view. The only other reason you would want to have two tables is if you have a business reason to segregate the data to two separate tables. ...


-1

Leave 2GB for the OS then you can split everything to remaining components you can follow this link for more details https://msdn.microsoft.com/en-us/library/ms178067.aspx. You can specify a min and a max by going (from SQL Management Studio to properties).


1

In your question you are using "SQL" and "relational" to mean the same thing. In the same way you can think of "NoSQL" as meaning "not relational" i.e. there is no primary key / foreign key concept; there is no "normalisation" enforced or implied; there is no real meaning of the words "table" or "column". To a key-value store all it knows about is a big ...


0

TEXT (max of 64K bytes) or MEDIUMTEXT (max 16M bytes) is typically used. There is no 'max' after them. VARCHAR(1111) is like NVARCHAR; the 1111 is the limit on characters (not bytes). VARCHAR is limited to 64K characters.


1

According to this MySQL document the LONGTEXT datatype in MySQL is 4 Gigabytes. http://wiki.ispirer.com/sqlways/mysql/data-types/longtext Note that the number of characters depends on the encoding.


2

To get all unique pairs of elements from an array of arbitrary length: WITH a(a) AS (SELECT '{A,B,C,D}'::text[]) -- provide array here , i(i) AS (SELECT i FROM a, generate_series(1, array_upper(a.a,1)) i) SELECT ARRAY[a[i1.i], a[i2.i]] AS pair FROM i i1 JOIN i i2 ON i2 > i1 , a; You can then join to the message table. Without knowing any ...


1

Query You UPDATE statement looks good except for one major problem. Fixed and re-formatted with some other minor improvements: UPDATE line_items li SET product_id = d.latest_product_id FROM products p JOIN vendors v ON v.id = p.vendor_id JOIN vendorgroups vg ON vg.id = v.vendorgroup_id JOIN ...


1

Wrap the selects into parentheses (SELECT DISTINCT CONCAT (MONTH(`Date`),'-',YEAR(`Date`)) from wp_result_plugin WHERE Bet=2 AND month(`Date`) = EXTRACT(month FROM (NOW())) ORDER BY MONTH(`Date`) ASC ) UNION (SELECT DISTINCT CONCAT (MONTH(`Date`),'-',YEAR(`Date`)) from wp_result_plugin WHERE Bet=2 AND month(`Date`) ORDER BY MONTH(`Date`) ASC)


0

NoSQL databases have faster writes mostly because they do not support cross-document transactions, ACID, and lack various data integrity constraint usually found in RDBMS. Neither of those things are trivial to support, especially in an environment where the database serves read/write requests from many users concurrently. Think of a typical table in a ...


1

Merge them. Sometimes it's worthwhile to have a separate Addresses table, but generally it's simplest to make those fields of a single Customers dimension. Because addresses are nearly unique, you're not going to save many records by hiving them out to their own table. If your Customers table is really large, and addresses change relatively often then ...


3

The problem is that you are missing a way to tie the outer table to the data inside of the STUFF. You can do this by adding a WHERE clause to the subquery: SELECT x.SurveyQuestionID, PossibleAnswers = STUFF(( SELECT ', ' + Answer FROM SurveyQuestionPossibleAnswer sqpa INNER JOIN SurveyQuestion sq ...


1

If you look at the standard SQL way of constructing a recursive query, you would see something like this: with t (num) as ( select rownum-1 num from dual union all select num+1 from t where num+1 < 10 ) select * from t; SQLFiddle: http://sqlfiddle.com/#!4/9eecb/4371/0 As you can see, it starts with selecting one row from the DUAL table, then ...


0

Looks fairly ominous for you I'm afraid. Likely an issue with data integrity or the validity of your drive. http://www.sqlserverf1.com/tag/restore-database-is-terminating-abnormally/


2

I would suggest NOT to use Designer in creating key constraints or doing any DDL / DML operations. Best is to use T-SQL - much flexible, more options, more powerful and you can automate many things with it. There is a learning curve, but its worth learning TSQL rather than keep using GUI. Also, make sure you are using the latest version of SSMS - sql ...



Top 50 recent answers are included