The problem: I have a table that records data rows in foo. Each time the row is updated, a new row is inserted along with a revision number. The table looks like: id rev field 1 1 test1 2 1 …
Tag: sql
Issues using sqlsrv_rows_affected, it keeps returning false even when its true
I have a query that changes a users password, and I want to check the users knows their current password before being able to change it. So it is working, I need to enter the current password before changes will be made, but my outputs are still always false, here is the code I have: Can anyone see this won&#…
SQL – Subtracting a depleting value from rows
I have a situation where I need to take a “quantity consumed” from one table, and apply it against a second table that has 1 or more rows that are “pooled lots” of quantities. I’m not sure how to describe it better, here’s what I mean from a table perspective: I need a resu…
How to update with inner join in Oracle
Could someone please verify whether inner join is valid with UPDATE statment in PL SQL? e.g. Update table t set t.value=’value’ from tableb b inner join on t.id=b.id inner join tablec c on c.id=b.id …
Creating index if index doesn’t exist
I have a problem creating an index with the advantage database server if it doesn’t exist with a sql query. My query looks like this: So I don’t use FullTextSearchIndizes,because it is a integer field. Otherwhise it would look like this: So, my only problem is how do I get the indices. I’ve …
I need to find and replace n in a mysql field
i’ve got data like this: 1 streetn2streetnmycitynmytown What i want to do is replace n with char(10) as i need a real linebreak in the db field. I’ve got: UPDATE data set `value` = REPLACE(`…
NVARCHAR(?) for Email addresses in SQL Server
For Email addresses, how much space should I give the columns in SQL Server. I found this definition on Wikipedia: http://en.wikipedia.org/wiki/Email_address The format of email addresses is local-part@domain where the local-part may be up to 64 characters long and the domain name may have a maximum of 253 ch…
How do I use a function result as my alias?
I am wondering if it is possible to use an SQL function to name the column of a query. As a simple example: should theoretically return If we can, which databases would this be available for? If not, then why? Answer AFAIK, there is no SQL way in any major DBMS product to support this. To name PIVOT columns i…
How can I perform a SQL ‘NOT IN’ query faster?
I have a table (EMAIL) of email addresses: EmailAddress ———— jack@aol.com jill@aol.com tom@aol.com bill@aol.lcom and a table (BLACKLIST) of blacklisted email addresses: EmailAddress ——–…
How to count number of records per day?
I have a table in a with the following structure: I would like to know how I can count the number of records per day, for the last 7 days in SQL and then return this as an integer. At present I have the following SQL query written: However this only returns all entries for the past 7 days. How