MySQL-dagi ustunlar soni <2 bo'lgan ustuni yangilang

Bu kabi MySQL ma'lumotlar bazasiga ega deb ayting:

physicians database
Name   |   Specialty
----------------
Bob        Nurse
Susan      Nurse
Joan       Driver

I want to update the Database so that any Specialty that has a count of less than 2 is moved to Other Specialties

Shunday qilib, ma'lumotlar bazasi quyidagi tarzda yangilanishi kerak:

physicians database
Name   |   Specialty
----------------
Bob        Nurse
Susan      Nurse
Joan       Other Specialties

Ushbu so'rov uchun haqiqiy ma'lumotlar bazasi juda katta.

Men bu so'rovni sinab ko'rdim:

UPDATE physicians 
 (SELECT Specialty FROM physicians GROUP BY Specialty
   HAVING COUNT(Specialty) = 1) AS counter
SET Specialty = 'Other Specialties'
WHERE Specity = counter

Lekin men noto'g'ri qilayotganimni aniq bilmasligim uchun sintaksik xato yuzaga keladi.

0
Yangilash bayonoti sintaksisi siz foydalanadigan subselektning tuzilishiga ruxsat bermaydi - UPDATE jadvali (subselect) shartni = ustun qiymati o'rnating. Subselektni olib tashlashingiz mumkin, ammo siz qo'rqaman, natijalarni tushunishingiz mumkin emas.
qo'shib qo'ydi muallif Anand, manba
Yangilash bayonoti sintaksisi siz foydalanadigan subselektning tuzilishiga ruxsat bermaydi - UPDATE jadvali (subselect) shartni = ustun qiymati o'rnating. Subselektni olib tashlashingiz mumkin, ammo siz qo'rqaman, natijalarni tushunishingiz mumkin emas.
qo'shib qo'ydi muallif Anand, manba
Qanday sintaksik xato ro'y bermoqda?
qo'shib qo'ydi muallif Jay Blanchard, manba

6 javoblar

Ushbu kodni UPDATE qilib ko'ring:

UPDATE physicians 
SET Specialty = 'Other Specialties'
WHERE Specialty IN (SELECT Specialty 
                   FROM (SELECT Specialty 
                         FROM physicians
                         GROUP BY Specialty
                         HAVING COUNT(*) = 1) AS t)

UPDATE bu Mutaxassislik qiymatlarining yagona bo'lishini ta'minlash uchun pastki so'rovdan foydalanadi. Keyin UPDATE amalga oshirish uchun ushbu qiymatlarni ishlatadi.

Note: The subquery is wrapped in another subquery because its SELECT statement cannot be used directly by the UPDATE in order to update the same table.

Demo bu yerda

2
qo'shib qo'ydi
Bu mukammal ishlaydi, rahmat
qo'shib qo'ydi muallif Jordash, manba

Ushbu kodni UPDATE qilib ko'ring:

UPDATE physicians 
SET Specialty = 'Other Specialties'
WHERE Specialty IN (SELECT Specialty 
                   FROM (SELECT Specialty 
                         FROM physicians
                         GROUP BY Specialty
                         HAVING COUNT(*) = 1) AS t)

UPDATE bu Mutaxassislik qiymatlarining yagona bo'lishini ta'minlash uchun pastki so'rovdan foydalanadi. Keyin UPDATE amalga oshirish uchun ushbu qiymatlarni ishlatadi.

Note: The subquery is wrapped in another subquery because its SELECT statement cannot be used directly by the UPDATE in order to update the same table.

Demo bu yerda

2
qo'shib qo'ydi
Bu mukammal ishlaydi, rahmat
qo'shib qo'ydi muallif Jordash, manba

Agar siz so'rovlar tizimini ishlatmoqchi bo'lsangiz, uni o'zgartirishingiz mumkin

UPDATE physicians inner join   
(SELECT Specialty FROM physicians GROUP BY Specialty
HAVING COUNT(Specialty) = 1) AS counter on physicians.speciality = counter.specialty
SET Specialty = 'Other Specialties'
2
qo'shib qo'ydi

Agar siz so'rovlar tizimini ishlatmoqchi bo'lsangiz, uni o'zgartirishingiz mumkin

UPDATE physicians inner join   
(SELECT Specialty FROM physicians GROUP BY Specialty
HAVING COUNT(Specialty) = 1) AS counter on physicians.speciality = counter.specialty
SET Specialty = 'Other Specialties'
2
qo'shib qo'ydi
update physicians
set Specialty = 'Other Specialties'
where Specialty in (
        select Specialty
        from physicians
        group by Specialty
        having COUNT(Specialty) < 2
        )

Update : UPDATE physicians SET Specialty = 'Other Specialties' WHERE Specialty IN (SELECT Specialty FROM (SELECT Specialty FROM physicians GROUP BY Specialty HAVING COUNT(Specialty) < 2) AS spacialityvalues) ;

1
qo'shib qo'ydi
Bu xato qaytaradi: [Err] 1093 - "Jadval" shifokorlari "UPDATE" uchun maqsad sifatida ikki marta va ma'lumotlar uchun alohida manbadir
qo'shib qo'ydi muallif Jordash, manba
update physicians
set Specialty = 'Other Specialties'
where Specialty in (
        select Specialty
        from physicians
        group by Specialty
        having COUNT(Specialty) < 2
        )

Update : UPDATE physicians SET Specialty = 'Other Specialties' WHERE Specialty IN (SELECT Specialty FROM (SELECT Specialty FROM physicians GROUP BY Specialty HAVING COUNT(Specialty) < 2) AS spacialityvalues) ;

1
qo'shib qo'ydi
Bu xato qaytaradi: [Err] 1093 - "Jadval" shifokorlari "UPDATE" uchun maqsad sifatida ikki marta va ma'lumotlar uchun alohida manbadir
qo'shib qo'ydi muallif Jordash, manba