I want to run a set of queries to insert some data into an SQL table but only if the record satisfying certain criteria are met. The table has 4 fields: id (primary), fund_id, date and price  I have 3 fields in the query: fund_id, date and price.  So my query would go something like this:  INSERT INTO funds (fund_id, date, price)     VALUES (23, '2013-02-12', 22.43)     WHERE NOT EXISTS (        SELECT *         FROM funds         WHERE fund_id = 23          AND date = '2013-02-12'     );   So I only want to insert the data if a record matching the fund_id and date does not already exist. If the above is correct it strikes me as quite an inefficient way of achieving this as an additional select statement must be run each time.  Is there a better way of achieving the above?  Edit: For clarification neither fund_id nor date are unique fields; records sharing the same fund_id or date will exist but no record should have both the same fund_id and date as another.
120454
I need to write a T-SQL stored procedure that updates a row in a table. If the row doesn't exist, insert it. All this steps wrapped by a transaction.   This is for a booking system, so it must be atomic and reliable. It must return true if the transaction was committed and the flight booked.  I'm new to T-SQL, and not sure on how to use @@rowcount. This is what I've written until now. Am I on the right road? I'm sure is an easy problem for you.  -- BEGIN TRANSACTION (HOW TO DO?)  UPDATE Bookings  SET TicketsBooked = TicketsBooked + @TicketsToBook  WHERE FlightId = @Id AND TicketsMax &lt; (TicketsBooked + @TicketsToBook)  -- Here I need to insert only if the row doesn't exists. -- If the row exists but the condition TicketsMax is violated, I must not insert  -- the row and return FALSE  IF @@ROWCOUNT = 0  BEGIN   INSERT INTO Bookings ... (omitted)  END  -- END TRANSACTION (HOW TO DO?)  -- Return TRUE (How to do?)
6749
What are the best workarounds for using a SQL IN clause with instances of java.sql.PreparedStatement, which is not supported for multiple values due to SQL injection attack security issues: One ? placeholder represents one value, rather than a list of values.  Consider the following SQL statement:  SELECT my_column FROM my_table where search_column IN (?)   Using preparedStatement.setString( 1, "'A', 'B', 'C'" ); is essentially a non-working attempt at a workaround of the reasons for using ? in the first place.    What workarounds are available?
76368
How do you stop race conditions in MySQL? the problem at hand is caused by a simple algorithm:   select a row from table if it doesn't exist, insert it   and then either you get a duplicate row, or if you prevent it via unique/primary keys, an error.  Now normally I'd think transactions help here, but because the row doesn't exist, the transaction don't actually help (or am I missing something?).  LOCK TABLE sounds like an overkill, especially if the table is updated multiple times per second.  The only other solution I can think of is GET_LOCK() for every different id, but isn't there a better way? Are there no scalability issues here as well? And also, doing it for every table sounds a bit unnatural, as it sounds like a very common problem in high-concurrency databases to me.
42591
I have a table like this:  create table foo (foo_label text, foo_price int, foo_date date);  insert into foo (           values           ('aaa', 100,  '2017-01-01'),           ('aaa', NULL, '2017-02-01'),           ('aaa', NULL, '2017-03-01'),           ('aaa', NULL, '2017-04-01'),           ('aaa', 140,  '2017-05-01'),           ('aaa', NULL, '2017-06-01'),           ('aaa', 180,  '2017-07-01')         );   As you can see a few values on the foo_price column are missing.  What I need is that missing values get filled up with the "previous" available value in this way:   foo_label | fixed_foo_price | foo_date -----------+-----------------+------------  aaa       | 100             | 2017-01-01  aaa       | 100             | 2017-02-01  aaa       | 100             | 2017-03-01  aaa       | 100             | 2017-04-01  aaa       | 140             | 2017-05-01  aaa       | 140             | 2017-06-01  aaa       | 180             | 2017-07-01   My attempt:  select      foo_label,      (case when foo_price is null then previous_foo_price else foo_price end) as fixed_foo_price,     foo_date from (   select        foo_label,        lag(foo_price) OVER (PARTITION BY foo_label order by foo_date::date) as previous_foo_price,        foo_price,       foo_date       from foo ) T;   As you can see from here:    It doesn't fill completely the '100' series.  Any idea how can I get the wanted result?
25718
I'm looking for duplicate records in dbf files based upon the attribute called 'ID'. I have various dbf files from 500,000 records to 1.5 million and I know there are a host of duplicates.   I would like to add a field 'Duplicate' that says Yes or No (or 1 or 0 is fine) when the ID attribute is present elsewhere. Using the following python script in Field Calculator returns 1 for a duplicate entry and 0 for unique entry;  uniqueList = [] def isDuplicate(inValue):   if inValue in uniqueList:     return 1   else:     uniqueList.append(inValue)     return 0 isDuplicate(!FIELD_NAME!)   However, the 1st record of, for example, 5 duplicate IDs will also be returned as a 0 (the subsequent 4 are considered the duplicates). I would need all 5 to be marked as duplicate as the ID exists elsewhere.  Using the following code will give you an incremental count of how many times that ID occurs with 1 meaning the 1st occasion and so forth;  UniqueDict = {} def isDuplicateIndex(inValue):     UniqueDict.setdefault(inValue,0)     UniqueDict[inValue] += 1     return UniqueDict[inValue]  isDuplicateIndex( !YOUR_FIELD! )   I just want a 1 (or Yes) if the ID of that record exists elsewhere! (ArcGIS version 10.1)  I have seen other answers such as  but it doesn't quite work.
15587
I have a query to insert a row into a table, which has a field called ID, which is populated using an AUTO_INCREMENT on the column. I need to get this value for the next bit of functionality, but when I run the following, it always returns 0 even though the actual value is not 0:  MySqlCommand comm = connect.CreateCommand(); comm.CommandText = insertInvoice; comm.CommandText += "\'" + invoiceDate.ToString("yyyy:MM:dd hh:mm:ss") + "\', " + bookFee + ", " + adminFee + ", " + totalFee + ", " + customerID +  ")"; int id = Convert.ToInt32(comm.ExecuteScalar());   According to my understanding, this should return the ID column, but it just returns 0 every time. Any ideas?  EDIT:  When I run:  "INSERT INTO INVOICE (INVOICE_DATE, BOOK_FEE, ADMIN_FEE, TOTAL_FEE, CUSTOMER_ID) VALUES ('2009:01:01 10:21:12', 50, 7, 57, 2134);last_insert_id();"   I get:  {"You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'last_insert_id()' at line 1"}
1458
I tried running the following statement:  INSERT INTO VOUCHER (VOUCHER_NUMBER, BOOK_ID, DENOMINATION) SELECT (a.number, b.ID, b.DENOMINATION)  FROM temp_cheques a, BOOK b WHERE a.number BETWEEN b.START_NUMBER AND b.START_NUMBER+b.UNITS-1;   which, as I understand it, should insert into VOUCHER each record from temp_cheques with the ID and DENOMINATION fields corresponding to entries in the BOOK table (temp_cheques comes from a database backup, which I'm trying to recreate in a different format). However, when I run it, I get an error:  Error: Operand should contain 1 column(s) SQLState:  21000 ErrorCode: 1241   I'm running this in SQuirrel and have not had issues with any other queries. Is there something wrong with the syntax of my query?  EDIT:  The structure of BOOK is:  ID  int(11) START_NUMBER    int(11) UNITS   int(11) DENOMINATION    double(5,2)   The structure of temp_cheques is:  ID  int(11) number  varchar(20)
110185
I have recently inherited a codebase with a large amount of Stored Procedures.  The system they are supporting is encountering numerous performance problems which I am looking in to.  A number of the Stored Procedures have a pattern like this:   Create Temp table Build up dynamic SQL query to insert a bunch of records, e.g.  DECLARE @sql VARCHAR(MAX) SET @sql = 'INSERT INTO @tempTable SELECT SomeColumn, SomeColumn2, SomeColumn3, etc FROM MyTable'  IF @someParam = [SomeValue]     SET @sql = @sql + 'WHERE SomeColumn = [SomeValue]';  IF @someOtherParam = [SomeOtherValue]     SET @sql = @sql + 'WHERE SomeOtherColum = [SomeOtherValue]';  Execute this dynamic sql  EXEC(@sql);  Select from the temp table and bring in a bunch of additional information to return to the client.  SELECT     ...  FROM     @tempTable    INNER JOIN ...    My immediate thoughts are:   There is dynamic SQL, so no cache plans, meaning plans generated every time. There is an INSERT SELECT pattern, so table locking is more likely to be an issue.   I have re-written some of the Stored Procedures in this way instead:      SELECT          ...      FROM          MyTable          INNER JOIN ...      WHERE      (         @someParam != SomeValue         OR         SomeColumn = SomeValue     )     AND     (         @someOtherParam != SomeOtherValue         OR         SomeOtherColumn = SomeOtherValue     )           From comparing execution plans and client statistics in SQL Management Studio, I have not sped the Stored Procedures up so I am apprehensive about suggesting wholesale re-writes of all Stored Procedures.  I am trying to set up some profiling of a live customer scenario, but as yet have been unable to prove my thoughts.  Can anyone offer any confirmation of the theory behind my thoughts, or any better ways of proving my suspicions?  The problem is I have read that dynamic SQL is not always a closed case - i.e. it depends on how it is used.  My understanding of locking also falls down at the fact that nowhere can I get 100% confirmation of how this type of INSERT SELECT will lock tables.
51458
If I have a batch of two different SELECT statements, is it possible for them to be executed in parallel (if the SQL Optimizer deems it to be the most efficient execution method)?  If the first SELECT statement is selecting into a temp table and the second SELECT statement is then inserting into that same temp table, does that prevent it from being possible for the two statements to be ran in parallel?  (I'm guessing the answers are yes and yes :).
83102
Let's examine these two statements:  IF (CONDITION 1) OR (CONDITION 2) ...  IF (CONDITION 3) AND (CONDITION 4) ...   If CONDITION 1 is TRUE, will CONDITION 2 be checked? If CONDITION 3 is FALSE, will CONDITION 4 be checked?  What about conditions on WHERE: does the SQL Server engine optimize all conditions in a WHERE clause? Should programmers place conditions in the right order to be sure that the SQL Server optimizer resolves it in the right manner?     ADDED:  Thank to Jack for link, surprise from t-sql code:  IF  1/0 = 1 OR 1 = 1       SELECT 'True' AS result ELSE       SELECT 'False' AS result   IF  1/0 = 1 AND 1 = 0       SELECT 'True' AS result ELSE       SELECT 'False' AS result   There is not raise a Divide by zero exception in this case.  CONCLUSION:  If C++/C#/VB has short-circuiting why can't SQL Server have it?     To truly answer this let's take a look at how both work with   conditions. C++/C#/VB all have short circuiting defined in the   language specifications to speed up code execution. Why bother   evaluating  N OR conditions when the first one is already true or M   AND conditions when the first one is already false.      We as developers have to be aware that SQL Server works differently.   It is a cost based system. To get the optimal execution plan for our   query the query processor has to evaluate every where condition and   assign it a cost. These costs are then evaluated as a whole to form a   threshold that must be lower than the defined threshold SQL Server has   for a good plan. If the cost is lower than the defined threshold the   plan is used, if not the whole process is repeated again with a   different mix of condition costs. Cost here is either a scan or a seek   or a merge join or a hash join etc... Because of this the   short-circuiting as is available in C++/C#/VB simply isn't possible.   You might think that forcing use of index on a column counts as short   circuiting but it doesn't. It only forces the use of that index and   with that shortens the list of possible execution plans. The system is   still cost based.      As a developer you must be aware that SQL Server does not do   short-circuiting like it is done in other programming languages and   there's nothing you can do to force it to.
18391
I'm trying to write a query that extracts and transforms data from a table and then insert those data into another table. Yes, this is a data warehousing query and I'm doing it in MS Access. So basically I want some query like this:  INSERT INTO Table2(LongIntColumn2, CurrencyColumn2) VALUES   (SELECT LongIntColumn1, Avg(CurrencyColumn) as CurrencyColumn1 FROM Table1 GROUP BY LongIntColumn1);   I tried but get a syntax error message.  What would you do if you want to do this?
64634
I have a table that has a schema like this:  create_table "questions_tags", :id =&gt; false, :force =&gt; true do |t|         t.integer "question_id"         t.integer "tag_id"       end        add_index "questions_tags", ["question_id"], :name =&gt; "index_questions_tags_on_question_id"       add_index "questions_tags", ["tag_id"], :name =&gt; "index_questions_tags_on_tag_id"   I would like to remove records that are duplicates, i.e. they have both the same tag_id and question_id as another record.  What does the SQL look like for that?
50568
Suppose I have a table with an auto_increment column, and then insert multiple values in a single statement:  insert into foo (bar) values ('a'), ('b'), ('c');  From the documentation, last_insert_id() returns the first generated ID for the query, the ID for a in this case.  Does mysql give any guarantees that a b and c will then be assigned sequential values?  In other words, if last_insert_id() returns 1 for a, is there any guarantee that b = 2 and c = 3? Or could there be a scenario where due to multiple concurrent inserts, I could end up with something like "a = 1, b = 5, c = 9"?
80061
I'm using a flow to set a field to a certain value (01, 02, 03) depending on the value of another field on the same record. I initially wanted to do it with process builder but because of the mapping it seems I would need three 'criteria nodes' or a formula field. Furthermore, it seems Salesforce is recommending using flows instead of process builder for before update actions as it is faster.  The problem with Flows is that I found a way to trigger it when the record is new or updated but not when the specific field is changed so it triggers every time the whole record is updated even when the field stay the same.  On Process Builder, you can use isChanged and then call the flow with specific record but that kinds of defeat the point of using flows for before Update.  So is there a possibility to do the same thing directly in flow ?  Thank you for the help !
71359
Say, we have a query like this:  select a.*,b.* from  a join b on a.col1=b.col1 and len(a.col1)=10   Assuming the above query uses a Hash Join and has a residual, the probe key will be col1 and the residual will be len(a.col1)=10.  But while going through another example, I could see both the probe and the residual to be the same column. Below is an elaboration on what I am trying to say:  Query:  select * from T1 join T2 on T1.a = T2.a    Execution plan, with probe and residual highlighted:         Test data:    create table T1 (a int, b int, x char(200)) create table T2 (a int, b int, x char(200))  set nocount on declare @i int set @i = 0 while @i &lt; 1000   begin       insert T1 values (@i * 2, @i * 5, @i)     set @i = @i + 1   end  declare @i int set @i = 0 while @i &lt; 10000   begin     insert T2 values (@i * 3, @i * 7, @i)     set @i = @i + 1   end   Question:    How can a probe and a residual be the same column? Why can't SQL Server use the probe column only? Why does it have to use the same column as a residual to filter rows again?  References for test data:
93683
I'm having trouble with the design of a temporal database. I need to know how to make sure I have only one active record for any given timeframe for a store. I have read , but I'm afraid I can't wrap my head around how the trigger would work. Particularly, how I would work that trigger into my existing one that prevents updates to records, and inserts a new record instead. My real problem is that I do not know how to prevent a Store from having more than one effective date when the finished date is null. (i.e. prevent 2 active records for a store).  This is what I have, but it allows me to insert a new record for a store with a different effective date.  Table Definition:  /****** Object:  Table [PCR].[Z_STORE_TEAM]    Script Date: 05/09/2014 13:05:57 ******/ IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Z_STORE_TEAM]') AND type in (N'U')) DROP TABLE [Z_STORE_TEAM] GO  IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Z_STORE_TEAM]') AND type in (N'U')) BEGIN CREATE TABLE [Z_STORE_TEAM](     [STORENUM] [int] NOT NULL,     [TEAM] [varchar](10) NULL,     [EFFECTIVE] [date] NOT NULL,     [FINISHED] [date] NULL, PRIMARY KEY CLUSTERED  (     [STORENUM] ASC,     [EFFECTIVE] ASC )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] ) ON [PRIMARY] END GO   Sample Data:  INSERT [Z_STORE_TEAM] ([STORENUM], [TEAM], [EFFECTIVE], [FINISHED]) VALUES (1, N'1', CAST(0x01380B00 AS Date), CAST(0x81380B00 AS Date)) INSERT [Z_STORE_TEAM] ([STORENUM], [TEAM], [EFFECTIVE], [FINISHED]) VALUES (1, N'2', CAST(0x81380B00 AS Date), NULL) INSERT [Z_STORE_TEAM] ([STORENUM], [TEAM], [EFFECTIVE], [FINISHED]) VALUES (2, N'1', CAST(0x01380B00 AS Date), NULL) INSERT [Z_STORE_TEAM] ([STORENUM], [TEAM], [EFFECTIVE], [FINISHED]) VALUES (2, N'2', CAST(0x20380B00 AS Date), NULL)   Instead of Update Trigger:  CREATE TRIGGER [tr_ZStoreTeam_update]     ON  [Z_STORE_TEAM]    INSTEAD OF UPDATE AS  BEGIN     -- SET NOCOUNT ON added to prevent extra result sets from     -- interfering with SELECT statements.     SET NOCOUNT ON;      -- Insert statements for trigger here     INSERT INTO PCR.Z_STORE_TEAM(STORENUM,TEAM,EFFECTIVE)     SELECT I.STORENUM,I.TEAM,GETDATE() AS EFFECTIVE     FROM inserted I     INNER JOIN PCR.Z_STORE_TEAM ST         ON I.STORENUM = ST.STORENUM      UPDATE ST     SET FINISHED = GETDATE()     FROM PCR.Z_STORE_TEAM ST     INNER JOIN inserted I         ON ST.STORENUM = I.STORENUM         AND ST.EFFECTIVE = I.EFFECTIVE END  GO
49890
I'm currently trying to create a View from a Table that does the following:  Table A ╔═════════════╦═══════════════╗  ║ PART NUMBER ║   FILE NAME   ║ ╠═════════════╬═══════════════╣ ║  PN0001-01  ║      FOO      ║ ║  PN0002-01  ║      BAR      ║ ║  PN0003-01  ║    FOO,BAR    ║ ║  PN0004-01  ║  BAR,FOO,FOB  ║ ╚═════════════╩═══════════════╝   To create  View B ╔═════════════╦═══════════════╗  ║ PART NUMBER ║   FILE NAME   ║ ╠═════════════╬═══════════════╣ ║  PN0001-01  ║      FOO      ║ ║  PN0002-01  ║      BAR      ║ ║  PN0003-01  ║      FOO      ║ ║  PN0003-01  ║      BAR      ║ ║  PN0004-01  ║      BAR      ║ ║  PN0004-01  ║      FOO      ║ ║  PN0004-01  ║      FOB      ║ ╚═════════════╩═══════════════╝   So, essentially, I need to take records from Table A and insert them into View B, where if the record from A has a LIKE '[,]' property, then that field is split into multiple records in B, depending on the number of times the ',' is present, with the text between each ',' being given its own record in View B.