Re: how do i use update in a select query
Well, it's a little to late to change everything...
That's the way the site is built and it's already online and fully functional, I just need to change a tag in 1020 ads, that's why I would want to do it via query...
There is one main table for the ads, separate tables for categories, extra fields, users, etc...
Regarding my understanding, I need to start RE-understanding SQL... 12 years ago (I was programming in MSAccess) I would have done this in 1 minute, today it took me 1 hour to "build" that SELECT query...
I tried the following:
UPDATE `mzm8e_jomcl_extrafields_values`
SET value = 2
WHERE ((`mzm8e_jomcl_adverts`.`id`) = (`mzm8e_jomcl_extrafields_values`.`advertid`)) AND (`mzm8e_jomcl_adverts`.`userid`) =5560 AND (`mzm8e_jomcl_extrafields_values`.`fieldid`) = 5
but, of course, I get "Unknown column 'mzm8e_jomcl_adverts.id' in 'where clause'"... I remember something about using INNER JOIN to refer to both tables, but I'm probably way off...
Anyway... thanks for trying to help!
CREATE TABLE `mzm8e_jomcl_adverts` (
`id` int(5) NOT NULL AUTO_INCREMENT,
`title` varchar(255) NOT NULL,
`alias` varchar(255) NOT NULL,
`catid` int(10) NOT NULL,
`video` varchar(255) NOT NULL,
`images` text NOT NULL,
`price` decimal(25,2) NOT NULL,
`currency` varchar(25) NOT NULL,
`tagid` varchar(50) NOT NULL,
`country` varchar(255) NOT NULL,
`region` varchar(255) NOT NULL,
`email` varchar(100) NOT NULL,
`address` text NOT NULL,
`postalcode` varchar(25) NOT NULL,
`phonenumber` varchar(25) NOT NULL,
`description` text NOT NULL,
`createddate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`expirydate` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`updateddate` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`repostdate` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`userid` int(25) NOT NULL,
`membership` varchar(25) NOT NULL,
`views` int(25) NOT NULL,
`featured` tinyint(4) NOT NULL,
`topadexpiry` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`topaddays` int(25) NOT NULL,
`published` tinyint(4) NOT NULL,
`renew` tinyint(4) NOT NULL,
`isnew` tinyint(4) NOT NULL DEFAULT '0',
`expired` tinyint(4) NOT NULL,
`latitude` varchar(255) NOT NULL,
`langtitude` varchar(255) NOT NULL,
`meta_keywords` text NOT NULL,
`meta_description` text NOT NULL,
`activation` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=14376 DEFAULT CHARSET=utf8
CREATE TABLE `mzm8e_jomcl_extrafields_values` (
`id` int(5) NOT NULL AUTO_INCREMENT,
`advertid` int(25) NOT NULL,
`fieldid` int(25) NOT NULL,
`value` text NOT NULL,
`new_val` int(11) NOT NULL,
`old_val` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=35200 DEFAULT CHARSET=utf8