![[personal profile]](https://www.dreamwidth.org/img/silk/identity/user.png){kind=small}
Spell-chequers FTW!
Jul. 5th, 2011 12:10 amMicrosoft Word does not like "denormalisation". It thinks it should be spelt "demoralisation". I see its point, but in this case will sadly have to insist on the more accurate but less descriptive spelling.
When is an integer not an integer?
Mar. 21st, 2011 04:38 pmWhen you're doing a UNION in a ColdFusion Query of Queries, that's when. I've met some interesting idiosyncracies in there before, but this is a new one on me.
( techy bit )
( techy bit )
SQL Server - how to delete duplicate rows
Mar. 1st, 2011 09:22 amNot that I need this at the moment, but for future reference, here's the link.
http://www.codeproject.com/Tips/159881/How-to-remove-duplicate-rows-in-SQL-Server-2008-wh.aspx
http://www.codeproject.com/Tips/159881/How-to-remove-duplicate-rows-in-SQL-Server-2008-wh.aspx
update mytable
set mycolumn = 1
where mycolumn = 0
;
Easy enough? No.
"Error Code: 1175. To disable safe mode, toggle the option in Preferences -> SQL Editor -> Query Editor and reconnect."
So I go there, and am told that safe mode prevents updates and deletes with no WHERE clause. A very good idea - but I've got a perfectly good Where clause, so what's the problem?
That error message has more than one line, and it's hard to even know the second is there to see. It says: "You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column"
A key column? Well, no... and there's no way I sensibly could, for this update.
I don't want to turn off safe mode completely, nor to start & stop. But a bit of Googling comes up with an answer.
SET SQL_SAFE_UPDATES=0;
Do update
SET SQL_SAFE_UPDATES=1;
set mycolumn = 1
where mycolumn = 0
;
Easy enough? No.
"Error Code: 1175. To disable safe mode, toggle the option in Preferences -> SQL Editor -> Query Editor and reconnect."
So I go there, and am told that safe mode prevents updates and deletes with no WHERE clause. A very good idea - but I've got a perfectly good Where clause, so what's the problem?
That error message has more than one line, and it's hard to even know the second is there to see. It says: "You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column"
A key column? Well, no... and there's no way I sensibly could, for this update.
I don't want to turn off safe mode completely, nor to start & stop. But a bit of Googling comes up with an answer.
SET SQL_SAFE_UPDATES=0;
Do update
SET SQL_SAFE_UPDATES=1;
A bit more MySQL head-desking
Nov. 30th, 2010 10:55 amWe have a copy of the main, live DB on another server, for test purposes. It's rather out of date, and could use a refresh. I've got some test tables and data in there that I'd rather not lose until I've checked them, so what I want to do is rename the old copy rather than dropping or over-writing it, then import a new copy from last night's live backup.
( how to, how not to )
( how to, how not to )
Yet more MySQL limitations
Sep. 30th, 2010 04:11 pm"Default values must be constants. This means, for example, that you cannot set the default for a date column to be the value of a function such as NOW() or CURRENT_DATE"
[[headdesk]] Set a column to default to the current date/time? Say, one called "createddate"? Why would I ever want to do that? It's not as if keeping an audit trail is something any serious DB would ever want to do.....
What you can do is "The exception is that you can specify CURRENT_TIMESTAMP as the default for a TIMESTAMP column."
But Timestamp is not the same as datetime.... it'll do, but it's limited.
Also, I can either default my "created at" timestamp, OR do a "on update current_timestamp" to store the updated date in another column. Not both. MySQL can only cope with one per table.
aarrgh.... let me back to proper grown-up databases again! Even Microsoft ones!
[[headdesk]] Set a column to default to the current date/time? Say, one called "createddate"? Why would I ever want to do that? It's not as if keeping an audit trail is something any serious DB would ever want to do.....
What you can do is "The exception is that you can specify CURRENT_TIMESTAMP as the default for a TIMESTAMP column."
But Timestamp is not the same as datetime.... it'll do, but it's limited.
Also, I can either default my "created at" timestamp, OR do a "on update current_timestamp" to store the updated date in another column. Not both. MySQL can only cope with one per table.
aarrgh.... let me back to proper grown-up databases again! Even Microsoft ones!
Web seminar looks interesting
Mar. 25th, 2010 02:39 pmhttp://computing.brighttalk.com/upcomingevents
"Next Generation Backup with Deduplication
This Computing web seminar in association with EMC will address the question of what data de-duplication is, how it is performed, and its impact on backup and recovery environments. The topics covered include fixed and variable length segmentation, protecting the integrity of de-duplicated data, flexible deployment options, and the complementary use of virtualisation and replication technologies."
I think I know what it is, and have been doing it, but let's learn more details, and how to do it better. Starting in twenty minutes, but I'll be able to view the recorded version anyway.
"Next Generation Backup with Deduplication
This Computing web seminar in association with EMC will address the question of what data de-duplication is, how it is performed, and its impact on backup and recovery environments. The topics covered include fixed and variable length segmentation, protecting the integrity of de-duplicated data, flexible deployment options, and the complementary use of virtualisation and replication technologies."
I think I know what it is, and have been doing it, but let's learn more details, and how to do it better. Starting in twenty minutes, but I'll be able to view the recorded version anyway.
MS Access - call this a database?
Mar. 7th, 2010 10:38 amI'm not sure I believe this. Using MS Access as a backend DB for a "teach myself" CF site, and my next trick is to move all my existing queries into stored procedures. It's been a while since I used Access, and I couldn't remember how it handled them. After a bit of Googling, I now know why: it doesn't.
Oh, an Access stored proc can exist: I just created one. But you can't do it through the huge, bloated, over-complicated user interface. Not even in SQL edit mode: it looks at "create proc" and decides it's a "create table" with a syntax error. No, you have to attack it through some other interface (ColdFusion and a cfquery tag, in my case) to get the SQL to create the stored proc to run.
They expect this to be taken seriously???
I can feel a CF app for writing and testing Access stored procedures coming on....
or maybe I get tempted into MySQL?
Oh, an Access stored proc can exist: I just created one. But you can't do it through the huge, bloated, over-complicated user interface. Not even in SQL edit mode: it looks at "create proc" and decides it's a "create table" with a syntax error. No, you have to attack it through some other interface (ColdFusion and a cfquery tag, in my case) to get the SQL to create the stored proc to run.
They expect this to be taken seriously???
I can feel a CF app for writing and testing Access stored procedures coming on....
or maybe I get tempted into MySQL?
And so I'll put most of it under a cut. I'm moving a bunch of code from PL/SQL to T_SQL: this is about the lack of %ROWTYPE in T-SQL.
( Read more... )
( Read more... )
