Ok is it just me who does everything slowly and badly until someone comes along and says why are you doing it like that?  My justification is that the job has to be done no matter what.

If you've ever sat there with phpMyAdmin or a MySQL Administration Tool (like Navicat or SQLYog), and still there at night editing each field so that it displays correctly... well that's usually me.

Found it a bit boring but here's a short bit of code to speed it up which I now use:

update table_name set field_name=REPLACE(field_name,'string_to_find','string_to_replace');

Source: http://www.mediacollege.com/computer/database/mysql/find-replace.html

 

Suppose you have a column in your table that you use as a counter (storing the value of the counter - eg. times an article has been displayed).

Basically what I used to do is something similar to the following:

  1. SELECT counter_field_value FROM table1 WHERE column1='this_article'
  2. Add 1 to counter_field_value
  3. UPDATE table1 SET counter_field_value=<new_counter_field_value> WHERE column1='this_article'

Combined with a PHP script this could be a few lines for something really small.

 

The quick trick to this is to do it all in one query:

UPDATE table1 SET counter_field_value=counter_field_value+1 WHERE column1='this_article'

What?
A quick note on how I got round one this one.

Why?
Often enough, our requirement is that the latest record from another table is associated with the current row, and often enough we get the latest by ordering the dataset of the subquery. In T-SQL and MySQL, this is not so much of an issue.

I get this error when having to use an ORDER BY clause in a subquery within an Oracle 11g environment.

How?
Consider the following:

SELECT
	ps.person_id,
	ps.person_name,
	(
	select 
		pa.person_reference as personid,
		pa.person_text as txt
	from 
		Person_Academia pa
	where 
		pa.type = 'USER' 
		AND pa.code = 'SPECIAL1'
		pa.person_reference = ps.person_reference
	order by
		pa.last_modified_date desc
	)
FROM
	Person_Staff ps

-- In oracle, yields "missing parenthesis" error.
So you can see my parentheses are fine.

Working Solution:
SELECT
	ps.person_id,
	ps.person_name,
	(
		select txt from
		(
			select 
				pa.person_text as txt, 
				pa.person_reference as personid,
				row_number() over (order by pa.last_modified_date desc) r
			from 
				Person_Academia pa
			where 
				pa.type = 'USER' 
				AND pa.code = 'SPECIAL1'
		)
	        where personid = ps.person_id

	) as AcademicInfo
FROM
	Person_Staff ps

Applies to:
- MS Windows 7 Enterprise


What?
So I can ping the server the Oracle 11g database sits on. I've installed the Oracle 11g client tools and can connect using sqlplus. But if I tnsping any oracle database, I get something along the lines of:

Message 3511 not found; No message file for product=NETWORK, facility=TNSMessage
3512 not found; No message file for product=NETWORK, facility=TNSMessage 3513 n
ot found; No message file for product=NETWORK, facility=TNSMessage 3509 not foun
d; No message file for product=NETWORK, facility=TNS

Why?
We NEED several instances of Oracle installed as we have different products/services and these run off various instances of Oracle 9i, 10g, and 11g. This article was written for those that need to support these kind of systems and not as an invite for suggestions on how my business should be run.


How?
So there are a variety of solutions that will fix this. However we have certain restrictions in my work environment thanks partly to group policies:

Subcategories