I am looking for a simple way to query an update or insert based on if the row exists in the first place. I am trying to use Python's MySQLdb right now. This is how I execute my query: self.cursor.execute("""UPDATE `inventory` SET `quantity` = `quantity`+{1} WHERE `item_number` = {0} ...
Table1 t10 (id) id --- 1 2 table t11(a1,a2,a3) a1 a2 a3 ---------- 1 10 a 1 10 b 1 11 b 1 12 c 2 20 d 2 21 e select * from t10 a,t11 b where a.id = b.a1 how to display id a1 a2 a3 -------------- 1 1 10 a 1 1 10 b //(not display this row) 1 1 11 b //(not display this row) 1 1 12 c /...
Hi guys, I'd like to hear some opinions or discussion on a matter of database design. Me and my colleagues are developing a complex application in finance industry that is being installed in several countries. Our contractors wanted us to keep a single application for all the countries so we naturally face the difficulties with differ...
I want to declare a server name and use this name in an insert statement. So far all i got is an error message. declare @machine nvarchar(6); declare @bar nvarchar(3); set @machine = 'Name00'; set @bar = 'foo' insert into @machine.dbname.dbo.table (column1, column2) select (column1, column2) from table where column1 = @bar This gives...
SELECT CONVERT (VARCHAR,GLTR_PSTNG_DATETIME,101) AS GLTR_PSTNG_DATE, GLTR_DOC_CODE, GLTR_OUR_DOC_NO, CONVERT (VARCHAR,GLTR_DOC_DATETIME,101) AS GLTR_DOC_DATETIME, GLTR_TRAN_AMT, GLTR_FC_CODE, GLTR_FC_AMT FROM GLAS_GL_TRANSACTIONS WHERE...
Hi all, I got the following text: PRINT CONVERT(NVARCHAR, CURRENT_TIMESTAMP, 111) + ' ' + CONVERT(NVARCHAR, CURRENT_TIMESTAMP, 108) + ' -Test Mode : ' + (CASE WHEN @turbo_mode_ind = 1 THEN 'some text ''test'' some more text.' ELSE 'an...
I'm not entirely sure if I'm trying to do something a little too complex for this but what I essentially want to do is turn this: declare @callid int set @callid = 57 declare @update nvarchar(max) declare update_cursor cursor for select UpdateIdentity from [Helpdesk_HR].[dbo].[hdEvents] where CallID = @callid group by UpdateIdentity or...
Hi everyone! I have a tricky problem. I have a very complex view in MS SQL 2005 with the following result: | ID | Name | ParentID | -------------------------------- | 1 | Cars | 1 | | 2 | Audi | 1 | | 3 | Toyota | 1 | | 4 | Trucks | 4 | Now I want my view to recogniz...
Hi all, I need some help with a SQL query for SQL Server 2005. Here is an example of the data in the table to be queried: Id PersonId PayrollNum ContractId PayrollFrom PayrollTo --------------------------------------------------------------------------- 1 432642 85110892 1 01/05/2009 ...
I have a report that calls 5 stored procedures, 3 of which are used for the display of data. The stored procedures work fine if called from Query Analyzer. However in Preview Mode in Visual Studio 2008 and on the Web Page it would throw an error on the last procedure - GenerateInstallReport. This report takes 4 parameters (customer ...
I'm generating a large script to do a bunch of inserts and updates. When I run it I get some errors, but the error messages don't let me pinpoint the problem - the line numbers are since the last "GO", so I can't find the right line. I'd like to add calls to my script to a function in T-SQL that will just write to the results window, so...
I am looking to combine the following queries into one, where scouting.jumpGate is integer, scouting.astroLoc is a string, scouting.ownerguild is a string and scouting.galaxy is a integer that cross-links to another table (and is my GROUP): Select TOP 3 scouting.jumpGate, scouting.astroLoc, scouting.ownerGuild...
There are two SQL statements. One is select count(*) from steventable where username='steven'; select * from steventable where username='steven' LIMIT 0 , 30; Is it possible to combine two SQL statements into one? The first one is to get how many records in steventable when username is steven, the second one is to fetch the first 30 r...
I would really appreciate some help with my problem: I have 2 MySQL tables, categories and posts, laid out (simplified) like so: categories: CATID - name - parent_id posts: PID - name - category What I would like to do is get the total amount of posts for each category, including any posts in subcategories. Right now I am getting...
What is the difference between an inner join and outer join? What's the precise meaning of these two kinds of joins? ...
Hi ! I'd like to understand the SQL Server 2008 Filtered Index Feature. How does the Database Engine deal wih this concept ? And how is it going to help me get faster results than standard indexes while querying large tables ? Can anybody help me on this ? Thanks. ...
I have a sql like this: SELECT * FROM tableA WHERE column_a = sth AND colun_b > sth so, how do I create index for it? One index for two column Two indexes for each column which is better? ...
Hey guys, What is wrong with the below format specifier for to_number? SELECT TO_NUMBER('0,22', '0,99') * 100 FROM DUAL; The result is 2200 instead of 22 -- what am I doing wrong? ...
Hi, I need to check if a string is found in one or more columns. Basically, I have a program which lets you checks multiple fields (name, surname, etc...) If both name and surname are checked and the user enters just the name, for example chris it would be easy to check it in mySQL with the LIKE parameter like this: select * from tbl...
I have a table like this: +-----+-----+-------+ | id | fk | value | +-----+-----+-------+ | 0 | 1 | peter | | 1 | 1 | josh | | 3 | 2 | marc | | ... | ... | ... | I'd like now to get all entries which have more than one value. The expected result would be: +-----+-------+ | fk | count | +-----+-------+ | 1 | 2 ...