Wednesday, December 19, 2007

updates in PostgreSQL, MS SQL, MySQL

PostgreSQL: OK, MS SQL: OK (just change the "||" to "+"), MySQL: NOT OK

/* very orthogonal, since only one table allowed to be updated, no need to "set product.name = ", "set name = " will do. in fact if you do: "set product.name = ", postgres will disallow it */

update product set name = product.name || ':' || prod.name
from prod
where prod.seq_id = product.seq_id



**********************************

PostgreSQL: OK (good for re-using existing query, must alias the source table if the source table conflicts with to-be-updated-table name), MS SQL: NOT OK, MySQL: NOT OK

update product set name = product.name || ':' || prod.name
from product x
inner join prod on prod.seq_id = x.seq_id
where x.seq_id = product.seq_id /* don't forget to filter the to-be-updated-table to queried rows */



**********************************

PostgreSQL: NOT OK, MS SQL: NOT OK, MySQL: OK

/* note: even only one possible table can be updated, requires the fully-qualified name for the fields to be set. i.e. set product.name */

update product,prod set product.name = concat(product.name, ':', prod.name)
where prod.seq_id = product.seq_id;



**********************************

PostgreSQL: NOT OK, MS SQL: OK (good for re-using existing query), MySQL: NOT OK

update product set name = product.name + ':' + prod.name
from product /* limitation: cannot alias this, must be same name as the table to-be-updated */
inner join prod on prod.seq_id = product.seq_id

***********************************


PostgreSQL: NOT OK, MS SQL: NOT OK, MySQL: OK


update product join prod on prod.seq_id = product.seq_id

set product.name = concat(product.name, ':', prod.name);


/* nice update syntax, not ambiguous as MS SQL. similar to MS Access update join syntax, though not as good/flexible as PostgreSQL, wherein you can just drop-in your previous query to the update statement */

7 Comments:

Anonymous Anonymous said...

--postgreSQL(9.0verified) should be:
UPDATE product set name = product.name + ':' + prod.name
FROM prod WHERE prod.seq_id = product.seq_id

5:47 PM

 
Anonymous Anonymous said...

Let. The makers of Meratol, unlike a selection of their rivals, please make sure of suggesting to
their site visitors which a healthy diet and
exercise would have been a very good idea to coincide with usage of their product.
Proactol is well known and will take proper as much
as 28% of the fat you consume.

Here is my site: capsiplex review

1:15 PM

 
Anonymous Anonymous said...

Anemia and other vitamin deficiencies can cause both
men and women to lose hair, as can low-calorie or low-protein diets.
Or, it can be physical, such as from an injury.
Many times, light versions of foods include simply reduced fat.


Have a look at my blog post - cause hair loss

10:52 AM

 
Anonymous Anonymous said...

When someone writes an paragraph he/she retains the plan of a user in his/her brain that how a user can understand it.
Therefore that's why this post is outstdanding. Thanks!

Here is my page :: weight loss pills that work
my web page > weight loss pills

10:14 AM

 
Anonymous Anonymous said...

We're now fully soaked on the new found glory of technology. At now I was while attending college and sharing a town-home with good friend. Whilst Lil Wayne has been handcuffed with a police car, an array of his belongings is spread about the hood including a packet of Strapped Condoms.

My site :: free porn movies

10:35 AM

 
Anonymous Anonymous said...

Needless for more information on say,any regarding the icelandic sheepdog all your family come across he has to be cross-checked with
reputable sources. Finally, after several years of
research for this project, Hi-Tech created formula that's every bit as good (if not better) compared to the original Fastin formula. Thoroughly look into the quantity of each ingredient.

Here is my web-site :: Phen375 Effectiveness
my website - Phen375 Fat Burner

9:15 AM

 
Anonymous Anonymous said...

Last but not least, it's time to take a look at Alexa Rankings for Just - Say - Hi. The first method we will look at is affiliate marketing, to be more specific, adult affiliate marketing. If giving orders is what turns you on, ask the camgirl if she would be comfortable doing it.

My blog post :: free sex chat

10:46 AM

 

Post a Comment

Links to this post:

Create a Link

<< Home