Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 UPDATE inside a SELECT statement?

Author  Topic 

jarv
Posting Yak Master

131 Posts

Posted - 2015-03-24 : 09:26:42
I would like to UPDATE a column form my SELECT statement below but not sure how to go about this?!

the column I need to update is: courses.active = N


SELECT schools.id, schools.name, schools.cactus_name, schools.cactus_name_english, schools.address1, schools.address2, schools.city, schools.county, schools.postcode, schools.country, schools.active, schools.latitude, schools.longitude, schools.contact, schools.website, schools.email, schools.telephone, schools.fax, schools.dos, schools.other_contacts, schools.school_commission, schools.bo_notes, courses.name, courses.domains, courses.active, courses.course_type_id, course_types.course_type_id, course_types.name_en
FROM schools
INNER JOIN courses ON courses.school_id = schools.id
INNER JOIN course_types ON courses.course_type_id = course_types.course_type_id
WHERE courses.domains
IN (
'[CL]'
)
AND courses.active = 'Y'
AND schools.active = '0'
GROUP BY schools.id

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2015-03-24 : 09:51:20
UPDATE Courses
SET active = 'N'
FROM schools
INNER JOIN courses ON courses.school_id = schools.id
INNER JOIN course_types ON courses.course_type_id = course_types.course_type_id
WHERE courses.domains
IN (
'[CL]'
)
AND courses.active = 'Y'
AND schools.active = '0'
Go to Top of Page

jarv
Posting Yak Master

131 Posts

Posted - 2015-03-24 : 10:15:47
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM schools INNER JOIN courses ON courses.school_id = schools.id INNER JOIN cou' at line 3


UPDATE courses
SET courses.active = 'N'
FROM schools
INNER JOIN courses ON courses.school_id = schools.id
INNER JOIN course_types ON courses.course_type_id = course_types.course_type_id
WHERE courses.domains
IN (
'[CL]'
)
AND courses.active = 'Y'
AND schools.active = '0'

Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2015-03-24 : 10:31:24
quote:
Originally posted by jarv

... check the manual that corresponds to your MySQL server version ...


This is a Microsoft SQL Server forum, so you might not find anyone here who can advise on MySQL.

Suggest you ask on a MySQL forum.
Go to Top of Page

jarv
Posting Yak Master

131 Posts

Posted - 2015-03-24 : 11:15:56
hmm really?
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-24 : 11:57:50
quote:
Originally posted by jarv

hmm really?



Really!
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2015-03-24 : 14:01:57
quote:
Originally posted by jarv

hmm really?



Surprised you've got over a hundred posts and not come across that stumbling block before ...
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-03-24 : 14:16:03
Not sure if this'll work in MySql, but try this:

UPDATE courses
SET active = 'N'
FROM schools
INNER JOIN courses ON courses.school_id = schools.id
INNER JOIN course_types ON courses.course_type_id = course_types.course_type_id
WHERE courses.domains = '[CL]'
AND courses.active = 'Y'
AND schools.active = '0'

If that doesn't work, post your question on a MySql forum like the others have suggested.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
   

- Advertisement -