Author Topic: Useful SQL Queries (Repost)  (Read 930 times)

0 Members and 1 Guest are viewing this topic.

Offline a4evermedia

  • Global Moderator
  • Full Member
  • ***
  • Posts: 122
  • Karma: +25/-0
  • Gender: Male
    • View Profile
    • A4Ever Media
Useful SQL Queries (Repost)
« on: July 30, 2012, 04:10:46 AM »
sql string  
screw  Posted: 06-18-2008 10:34pm

--------------------------------------------------------------------------------
With what sql string can I copy the contents of the gallery description field to the gallery keywords field ONLY if the keywords field is empty?
 
meicker  Posted: 06-19-2008 02:33am

--------------------------------------------------------------------------------
update tx_galleries set keywords = description where keywords = ''

Thats it !

BR

Marc  
JMB-Jeremy  Posted: 06-19-2008 09:19am

--------------------------------------------------------------------------------
The keywords value might also be NULL, so you would actually want to use this:


UPDATE `tx_galleries` SET `keywords`=`description` WHERE (`keywords`='' OR `keywords` IS NULL)
screw  Posted: 06-19-2008 12:15pm

--------------------------------------------------------------------------------
double post  
screw  Posted: 06-22-2008 01:17pm

--------------------------------------------------------------------------------
Thanks a lot.

There are two more sql strings that I'm looking for:

1. To append the text of the description field to the keywords field. In other words add the text without erasing the existing values in the keywords field.

2. To search the keywords field for duplicate words and leave only one of them. So that there are no more duplicates in the keywords field.  
JMB-Jeremy  Posted: 06-23-2008 10:07am

--------------------------------------------------------------------------------
1.
UPDATE `tx_galleries` SET `keywords`=CONCAT(`keywords`, ' ', `description`)
2. Sorry, there is not an SQL query you can run to do this.  
screw  Posted: 08-31-2008 06:04pm

--------------------------------------------------------------------------------
Thanks.

And how do I change this
UPDATE `tx_galleries` SET `keywords`=CONCAT(`keywords`, ' ', `description`)to only append the description field to the keywords field if the keywords field has 4 or less values in it?
So 4 values or less in the keywords field, append description field.
More than 4 values in the keywords field, do nothing.  
JMB-Jeremy  Posted: 09-01-2008 10:19am

--------------------------------------------------------------------------------
Sorry, there isn't a way to determine the number of keywords that have been set using only a MySQL query. The closest you can get is to check the length of the keywords field, which will tell you how many characters it currently contains. For example, to add the description to the keywords field when the keywords field contains less than 20 characters you would use this:


UPDATE `tx_galleries` SET `keywords`=CONCAT(`keywords`, ' ', `description`) WHERE LENGTH(`keywords`) < 20

Unofficial JMB Support Forums

Useful SQL Queries (Repost)
« on: July 30, 2012, 04:10:46 AM »


Tags:
 

Related Topics

  Subject / Started by Replies Last post
0 Replies
924 Views
Last post July 30, 2012, 03:48:07 AM
by a4evermedia
0 Replies
918 Views
Last post July 30, 2012, 03:54:58 AM
by a4evermedia
0 Replies
855 Views
Last post July 30, 2012, 04:01:13 AM
by a4evermedia
1 Replies
1265 Views
Last post February 26, 2014, 07:02:27 AM
by Zug

anything