CoderFunda
  • Home
  • About us
    • Contact Us
    • Disclaimer
    • Privacy Policy
    • About us
  • Home
  • Php
  • HTML
  • CSS
  • JavaScript
    • JavaScript
    • Jquery
    • JqueryUI
    • Stock
  • SQL
  • Vue.Js
  • Python
  • Wordpress
  • C++
    • C++
    • C
  • Laravel
    • Laravel
      • Overview
      • Namespaces
      • Middleware
      • Routing
      • Configuration
      • Application Structure
      • Installation
    • Overview
  • DBMS
    • DBMS
      • PL/SQL
      • SQLite
      • MongoDB
      • Cassandra
      • MySQL
      • Oracle
      • CouchDB
      • Neo4j
      • DB2
      • Quiz
    • Overview
  • Entertainment
    • TV Series Update
    • Movie Review
    • Movie Review
  • More
    • Vue. Js
    • Php Question
    • Php Interview Question
    • Laravel Interview Question
    • SQL Interview Question
    • IAS Interview Question
    • PCS Interview Question
    • Technology
    • Other

31 July, 2024

Postgres CTE value not being used in where clause

 Programing Coderfunda     July 31, 2024     No comments   

I'm trying to run this query that updates an ordering column after inserting the rows, and everything seems to work fine except using the id FROM the CTE query in the WHERE clause doesn't work, even though it works everywhere else. This is the query
WITH variant AS (
INSERT INTO accessory_variants (
accessory_id,
label,
multiple
) VALUES (
1,
'Colors',
FALSE
) RETURNING *
),
opts AS (
INSERT INTO accessory_variant_options (
accessory_id,
accessory_variant_id,
price,
label,
description
) VALUES (
1,
(SELECT id FROM variant),
100,
'Red',
'A red one'
),
(
1,
(SELECT id FROM variant),
100,
'Blue',
'A blue one'
) RETURNING id
),
ids AS (
SELECT JSONB_AGG(id) AS option_ids FROM opts
)
UPDATE
accessory_variants
SET
ordering = ids.option_ids
FROM
variant,
ids
WHERE
accessory_variants.id = variant.id



Where it gets weird is if I change it to something like this
...
UPDATE
accessory_variants
SET
ordering = TO_JSONB(variant.id)
FROM
variant,
ids
WHERE
accessory_variants.id = {existing row id}



The ordering table is populated with the correct id, so variant.id contains the correct id, but still can't be found by the where clause
  • Share This:  
  •  Facebook
  •  Twitter
  •  Google+
  •  Stumble
  •  Digg
Email ThisBlogThis!Share to XShare to Facebook
Newer Post Older Post Home

0 comments:

Post a Comment

Thanks

Meta

Popular Posts

  • Laravel DynamoDB Eloquent Models and Query Builder
    Laravel   DynamoDB   is a DynamoDB-based Eloquent model and Query builder for Laravel. Using the provided Dynamo driver, models extend the E...
  • There are two solutions for one board, using different chips. But one of their i2c address is the same. How to resolve conflict in one dts?
    Two chips A and B conflict with 0x62 on i2c bus 10. If A@62 and B@62 are configured on dts, Linux loads the driver of A. If B@62 is in front...
  • Unable to install pyocd in latest version of Anaconda
    I'm trying to set up our usual Anaconda development environment on a new Windows 10 PC. I downloaded the latest version of Anaconda last...
  • Campfire Coders (The post-Laracon-'24 recap episode!)
    submitted by /u/JerseyMilker [link] [comments]
  • Multidimensional array searching to return key if value found
    <? Multidimensional  array  searching to  return  key  if  value found I need to pass in a name to a search  function   on  an  array .  ...

Categories

  • Ajax (26)
  • Bootstrap (30)
  • DBMS (42)
  • HTML (12)
  • HTML5 (45)
  • JavaScript (10)
  • Jquery (34)
  • Jquery UI (2)
  • JqueryUI (32)
  • Laravel (1017)
  • Laravel Tutorials (23)
  • Laravel-Question (6)
  • Magento (9)
  • Magento 2 (95)
  • MariaDB (1)
  • MySql Tutorial (2)
  • PHP-Interview-Questions (3)
  • Php Question (13)
  • Python (36)
  • RDBMS (13)
  • SQL Tutorial (79)
  • Vue.js Tutorial (69)
  • Wordpress (150)
  • Wordpress Theme (3)
  • codeigniter (108)
  • oops (4)
  • php (853)

Social Media Links

  • Follow on Twitter
  • Like on Facebook
  • Subscribe on Youtube
  • Follow on Instagram

Pages

  • Home
  • Contact Us
  • Privacy Policy
  • About us

Blog Archive

  • July (4)
  • September (100)
  • August (50)
  • July (56)
  • June (46)
  • May (59)
  • April (50)
  • March (60)
  • February (42)
  • January (53)
  • December (58)
  • November (61)
  • October (39)
  • September (36)
  • August (36)
  • July (34)
  • June (34)
  • May (36)
  • April (29)
  • March (82)
  • February (1)
  • January (8)
  • December (14)
  • November (41)
  • October (13)
  • September (5)
  • August (48)
  • July (9)
  • June (6)
  • May (119)
  • April (259)
  • March (122)
  • February (368)
  • January (33)
  • October (2)
  • July (11)
  • June (29)
  • May (25)
  • April (168)
  • March (93)
  • February (60)
  • January (28)
  • December (195)
  • November (24)
  • October (40)
  • September (55)
  • August (6)
  • July (48)
  • May (2)
  • January (2)
  • July (6)
  • June (6)
  • February (17)
  • January (69)
  • December (122)
  • November (56)
  • October (92)
  • September (76)
  • August (6)

Loading...

Laravel News

Loading...

Copyright © CoderFunda | Powered by Blogger
Design by Coderfunda | Blogger Theme by Coderfunda | Distributed By Coderfunda